How to have different calender dates as business month in SQL

Question

account  createddate  closed_date   account_type  Debit_Amount  txn_date

1234      01/02/2023   01/01/2099   Normal         100           01/02/2023
7892      02/02/2023   01/01/2099   Premimum       200           01/02/2023 
4567      03/02/2023   01/01/2099   Normal         500           01/02/2023 
8790      05/02/2023   01/01/2099   Normal         500           05/02/2023
8890      05/02/2023   01/01/2099   Super_prem     500           05/03/2023
8330      06/02/2023   01/01/2099   Normal         500           05/02/2023
8990      08/02/2023   01/01/2099   Normal         500           04/02/2023
8490      04/02/2023   01/01/2099   Premimum       500           05/03/2023
8550      05/02/2023   01/01/2099   Normal         500           05/03/2023
8660      05/02/2023   01/01/2099   Super_prem     500           05/03/2023
8340      06/02/2023   01/01/2099   Normal         500           05/02/2023
8120      08/02/2023   01/01/2099   Normal         500           02/02/2023
8890      04/02/2023   01/01/2099   Premimum       500           05/03/2023

I have some data like this, now I need to report the total debit balance based on account_type But each account type has a different business month cycle to consider.

For example:

For Normal account type I need to consider date from every month 2nd to next month 2nd as business month first business month: for example 2nd January to 2nd Feb is my January business month.

Similarly for Premimum 5 to 5th and Super_premimum 10th to 10th.

I need to report business month wise for last 6 months total debit balance for all these 3 account type.

I was trying to usse DATEADD for each month and account_type and aggregate the debit balance separately and join them later

select convert(date, dateadd(day, 2 - day(getdate()), getdate())) as month_first,
       dateadd(day, 2, eomonth(getdate(), -1)) as alternative_month_first,
       eomonth(getdate())+2 as month_last

and query for each month in the above for each account type separately but I feel this gets complex and not optimal, is there a better way to do it ?

Answer 1

You could try to achieve this using the WITH clause to get the business start and end dates for each account type. Then use those to apply the transformation on the table to fetch the data.

DROP TABLE IF EXISTS tableName;

CREATE TABLE tableName 
(
    account INT,
    createddate DATE,
    closed_date DATE,
    account_type    VARCHAR(512),
    Debit_Amount    INT,
    txn_date    DATE
);

INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('1234', '01/02/2023', '01/01/2099', 'Normal', '100', '01/13/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('7892', '02/02/2023', '01/01/2099', 'Premimum', '200', '01/02/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('4567', '03/02/2023', '01/01/2099', 'Normal', '500', '01/02/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8790', '05/02/2023', '01/01/2099', 'Normal', '500', '05/02/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8890', '05/02/2023', '01/01/2099', 'Super_prem', '500', '05/03/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8330', '06/02/2023', '01/01/2099', 'Normal', '500', '05/02/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8990', '08/02/2023', '01/01/2099', 'Normal', '500', '04/02/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8490', '04/02/2023', '01/01/2099', 'Premimum', '500', '05/15/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8550', '05/02/2023', '01/01/2099', 'Normal', '500', '05/03/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8660', '05/02/2023', '01/01/2099', 'Super_prem', '500', '05/18/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8340', '06/02/2023', '01/01/2099', 'Normal', '500', '05/02/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8120', '08/02/2023', '01/01/2099', 'Normal', '500', '02/02/2023');
INSERT INTO tableName (account, createddate, closed_date, account_type, Debit_Amount, txn_date) VALUES ('8890', '04/02/2023', '01/01/2099', 'Premimum', '500', '05/03/2023');
  
WITH BusinessMonthRanges AS (
    SELECT
        account AS business_account,
        account_type as business_account_typ,
        DATEADD(DAY, CASE 
                        WHEN account_type = 'Normal' THEN 2 - DAY(txn_date)
                        WHEN account_type = 'Premimum' THEN 5 - DAY(txn_date)
                        WHEN account_type = 'Super_prem' THEN 10 - DAY(txn_date)
                        ELSE 0
                     END, txn_date) AS business_month_start
    FROM tableName
)
SELECT Account,
  Account_Type,
  business_month_start, 
  DATEADD(MONTH, 1, business_month_start) AS business_month_end ,
  SUM(Debit_Amount) AS total_debit_balance
FROM BusinessMonthRanges
INNER JOIN tableName ON account = business_account
AND account_type = business_account_typ
AND txn_date >= business_month_start
AND txn_date < DATEADD(MONTH, 1, business_month_start)
GROUP BY account, account_type, business_month_start, DATEADD(MONTH, 1, business_month_start)
ORDER BY account_type, business_month_start;

Account	Account_Type	business_month_start	business_month_end	total_debit_balance
1234	Normal	2023-01-02	2023-02-02	100
4567	Normal	2023-01-02	2023-02-02	500
8120	Normal	2023-02-02	2023-03-02	500
8990	Normal	2023-04-02	2023-05-02	500
8330	Normal	2023-05-02	2023-06-02	500
8340	Normal	2023-05-02	2023-06-02	500
8550	Normal	2023-05-02	2023-06-02	500
8790	Normal	2023-05-02	2023-06-02	500
8490	Premimum	2023-05-05	2023-06-05	500
8660	Super_prem	2023-05-10	2023-06-10	500

fiddle