I have some table data like this:
account createddate closed_date account_type Initial_Amount Record_date Status
1234 01/02/2023 01/01/2099 Normal 100 01/02/2023 ACTIVE
7892 02/02/2023 01/01/2099 Premimum 200 01/02/2023 ACTIVE
4567 03/02/2023 01/01/2099 Normal 500 01/02/2023 ACTIVE
8120 08/02/2023 01/01/2099 Normal 500 02/02/2023 ACTIVE
8890 04/02/2023 01/01/2099 Premimum 500 05/02/2023 ACTIVE
1234 04/02/2023 19/02/2023 Normal 0 19/02/2023 CLOSED
8890 04/02/2023 01/03/2023 Premimum 0 01/03/2023 CLOSED
3542 02/03/2023 01/01/2099 Premimum 300 02/03/2023 ACTIVE
3542 03/03/2023 01/01/2099 Normal 200 03/03/2023 ACTIVE
as SQL:
CREATE TABLE retail_account (
account integer
, createddate varchar
, closed_date varchar
, account_type varchar
, initial_amount integer
, record_date varchar
, status varchar
);
INSERT INTO retail_account VALUES
(1234, '01/02/2023', '01/01/2099', 'Normal', 100, '01/02/2023', 'ACTIVE')
, (7892, '02/02/2023', '01/01/2099', 'Premimum', 200, '01/02/2023', 'ACTIVE')
, (4567, '03/02/2023', '01/01/2099', 'Normal', 500, '01/02/2023', 'ACTIVE')
, (8120, '08/02/2023', '01/01/2099', 'Normal', 500, '02/02/2023', 'ACTIVE')
, (8890, '04/02/2023', '01/01/2099', 'Premimum', 500, '05/02/2023', 'ACTIVE')
, (1234, '04/02/2023', '19/02/2023', 'Normal', 0, '19/02/2023', 'CLOSED')
, (8890, '04/02/2023', '01/03/2023', 'Premimum', 0, '01/03/2023', 'CLOSED')
, (3542, '02/03/2023', '01/01/2099', 'Premimum', 300, '02/03/2023', 'ACTIVE')
, (3542, '03/03/2023', '01/01/2099', 'Normal', 200, '03/03/2023', 'ACTIVE')
;
I need to return a few aggregations based on the user's selected date range (Date is DD/MM/YYYY format), For example, if the user selects data range as start date 01/02/2023 and end date as 10/02/2023 I need to return like below
Month Account_type Count(Accouny_type) sum(Initial_Amount)
02 Normal 3 1100
02 Premimum 2 700
if the user selects the data range as start date 01/02/2023 and end date as 20/02/2023 I need to return like below
Month Account_type Count(Accouny_type) sum(Initial_Amount)
02 Normal 2 1000
02 Premimum 2 700
I need to exclude account 1234 as it got closed and its status is CLOSED
if user selects data range as start date 01/02/2023 and end date as 03/03/2023 I need to return like below
Month Account_type Count(Accouny_type) sum(Initial_Amount)
02 Normal 2 1000
02 Premimum 2 700
03 Normal 1 200
03 Premimum 1 300
I tried the below query but it still includes account 1234 and 8890 values as their previous status is active
SELECT
DATEADD(month, 'DD/MM/YYYY') AS MM
, Account_type
, COUNT(Account_type) AS Count_Account_type
, SUM(Initial_Amount) AS Sum_Initial_Amount
FROM
retail_account
WHERE
Record_date >= '01/02/2023'
AND
Record_date <= '03/03/2023'
AND
Status = 'ACTIVE'
GROUP BY
DATEADD(month, 'DD/MM/YYYY')
, Account_type
DECLARE @CurrentYear INT;
DECLARE @NextYear INT;
DECLARE @PrevFinancialYearStart DATETIME;
DECLARE @CurrentFinancialYearStart DATETIME;
SET @CurrentYear = YEAR(GETDATE());
SET @NextYear = @CurrentYear + 1;
SET @PrevFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, (@CurrentYear - 1)) + '-04-01', 120);
SET @CurrentFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, @CurrentYear) + '-04-01', 120);
DECLARE @CurrentYear INT;
DECLARE @NextYear INT;
DECLARE @PrevFinancialYearStart DATETIME;
DECLARE @CurrentFinancialYearStart DATETIME;
SET @CurrentYear = YEAR(GETDATE());
SET @NextYear = @CurrentYear + 1;
SET @PrevFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, (@CurrentYear - 1)) + '-04-01', 120);
SET @CurrentFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, @CurrentYear) + '-04-01', 120);
SELECT *
FROM T1
WHERE NOT EXISTS (
SELECT 1
FROM T1 AS t2
WHERE T1.Account = t2.Account
AND (
(t2.Status = 'cls' AND t2.ClsDate >= @CurrentFinancialYearStart)
OR
(t2.Status != 'cls' AND t1.OpenDate >= @PrevFinancialYearStart AND t1.OpenDate <= DATEADD(DAY, -1, @CurrentFinancialYearStart))
)
);
DECLARE @CurrentYear INT;
DECLARE @NextYear INT;
DECLARE @PrevFinancialYearStart DATETIME;
DECLARE @CurrentFinancialYearStart DATETIME;
SET @CurrentYear = YEAR(GETDATE());
SET @NextYear = @CurrentYear + 1;
SET @PrevFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, (@CurrentYear - 1)) + '-04-01', 120);
SET @CurrentFinancialYearStart = CONVERT(DATETIME, CONVERT(VARCHAR, @CurrentYear) + '-04-01', 120);
SELECT *
FROM T1
WHERE NOT EXISTS (
SELECT 1
FROM T1 AS t2
WHERE T1.Account = t2.Account
AND (
(t1.OpenDate >= @PrevFinancialYearStart AND t1.OpenDate <= DATEADD(DAY, -1, @CurrentFinancialYearStart))
OR
(
t1.OpenDate >= @CurrentFinancialYearStart
AND t1.OpenDate <= DATEADD(DAY, -1, DATEADD(YEAR, 1, @CurrentFinancialYearStart))
AND t2.Status = 'cls'
AND (t2.ClsDate IS NULL OR t2.ClsDate >= @PrevFinancialYearStart)
)
)
);
Have you tried something like this?
WITH FilteredData AS (
SELECT
Account_number,
TO_CHAR(TO_DATE(Record_date, 'DD/MM/YYYY'), 'MM') AS Month,
Account_type,
COUNT(Account_type) AS Count_Account_type,
SUM(Initial_Amount) AS Sum_Initial_Amount
FROM
your_table_name
WHERE
TO_DATE(Record_date, 'DD/MM/YYYY') BETWEEN TO_DATE('01/02/2023', 'DD/MM/YYYY') AND TO_DATE('03/03/2023', 'DD/MM/YYYY')
)
SELECT
Month,
Account_type,
Count_Account_type,
Sum_Initial_Amount
FROM
FilteredData
WHERE
Account_number NOT IN (
SELECT DISTINCT Account_number
FROM your_table_name
WHERE Status = 'CLOSED'
AND TO_DATE(closed_date, 'DD/MM/YYYY') < TO_DATE('01/02/2023', 'DD/MM/YYYY')
)
ORDER BY
Month,
Account_type;