CODEWITHSUNDEEP
sqlsql-servert-sql
user3369545
user3369545

Reputation: 421

Getting the balance in a particular month with the payments in the following month by Account Month

I have a account table that has the accountno, performance month and balance. I also have a payments table where I have the account number ,payment date and the payment amount. Now I want to create a result table having the performance month ,total distinct accounts from account table for that month, total balance for that performance month from account table and the total payments for the same set of accounts and the next month (performance month +1) (extracting next month from payment date) from the payments table.

Account table:

Accountno PerformanceMonth Balance
1234 Jan-23 1456
2134 Jan-23 234
3412 Jan-23 904
1234 Feb-23 781
2134 Feb-23 3415
6751 Feb-23 567
6751 Mar-23 5600
4512 Mar-23 770
2134 Mar-23 546

Payments table:

Account No Payment date Paymentamount
2134 1/3/2023 56
2134 1/3/2023 134
2134 2/3/2023 125
2134 2/3/2023 90
5165 2/3/2023 1200
2134 3/15/2023 2000
2134 3/23/2023 500
2134 4/5/2023 350
3412 1/3/2023 200
3412 2/20/2023 800
3412 2/27/2023 40
1234 2/10/2023 400
1234 2/15/2023 300
1234 2/21/2023 150
1234 3/5/2023 300
1234 3/7/2023 100
1234 3/21/2023 20
6751 3/11/2023 300
6751 3/12/2023 120
6751 4/5/2023 1500
6751 4/12/2023 380
6751 4/22/2023 1200

Result table:

PerformanceMonth Total accounts Total balance Total payment next month
Jan-23 3 2594 1905
Feb-23 3 4763 3340
Mar-23 3 6916 3430 
create table #account as
(accountno int,
performancemonth date,
balance float)

insert into #account values
('1234','1/1/2023','1456'),
('2134','1/1/2023','234'),
('3412','1/1/2023','904'),
('1234','2/1/2023','781'),
('2134','2/1/2023','3415'),
('6751','2/1/2023','567'),
('6751','3/1/2023','5600'),
('4512','3/1/2023','770'),
('2134','3/1/2023','546')

create table #payments as
(accountno int,
paymentdate date,
paymentamount float)

insert into #payments values
('2134','1/3/2023','56'),
('2134','1/3/2023','134'),
('2134','2/3/2023','125'),
('2134','2/3/2023','90'),
('5165','2/3/2023','1200'),
('2134','3/15/2023','2000'),
('2134','3/23/2023','500'),
('2134','4/5/2023','350'),
('3412','1/3/2023','200'),
('3412','2/20/2023','800'),
('3412','2/27/2023','40'),
('1234','2/10/2023','400'),
('1234','2/15/2023','300'),
('1234','2/21/2023','150'),
('1234','3/5/2023','300'),
('1234','3/7/2023','100'),
('1234','3/21/2023','20'),
('6751','3/11/2023','300'),
('6751','3/12/2023','120'),
('6751','4/5/2023','1500'),
('6751','4/12/2023','380'),
('6751','4/22/2023','1200')

create table #result as
(performancemonth date,
totalaccounts int,
totalbalance float,
totalpaymentnextmonth float)

insert into #result values
('1/1/2023','3','2594','1905),
('2/1/2023','3','4763','3340),
('3/1/2023','3','6916','3430)

Query tried:

SELECT  performance_month,
    COUNT(DISTINCT a.account_number) AS total_accounts,
    SUM(a.balance) AS total_balance, 
    COALESCE(SUM(CASE WHEN MONTH(p.payment_date) = MONTH(DATE_ADD(a.performance_month, INTERVAL 1 MONTH)) THEN p.amount ELSE 0 END), 0) AS total_next_month_payments 
FROM account a
LEFT JOIN payment p 
    ON a.account_number = p.account_number 
GROUP BY performance_month;

Thanks

Upvotes: 0

Views: 101

Answers (1)

abolfazl sadeghi
abolfazl sadeghi

Reputation: 2368

You can use outer apply(join with payments) for calcaute Totalpaymentnextmonth

and use Convert(char(3), a.performancemonth, 0) to Get shortName month

The calculations you made for the month "Jan-2023" are wrong (your calculations =1905 but correct is 3105)

SELECT  
   Convert(char(3), a.performancemonth, 0)+'-'+ 
   cast( DATEPART(YEAR, a.performancemonth) as  varchar(100)) ,
    
    COUNT(DISTINCT a.accountno) AS total_accounts,
    SUM(DISTINCT a.balance) AS total_balance 
    ,sum(distinct sumpaymentamount) as Totalpaymentnextmonth

FROM #account a
LEFT JOIN #payments p 
    ON a.accountno = p.accountno 
outer apply (

                select 
                sum(aa.paymentamount) as sumpaymentamount  
                from #payments aa
                where 
                        DATEPART( YEAR,aa.paymentdate)= 
                        DATEPART( YEAR, dateadd(MONTH,1,a.performancemonth))
                    
                    and DATEPART( MONTH,aa.paymentdate)= 
                        DATEPART( MONTH, dateadd(MONTH,1,a.performancemonth))

)f
GROUP BY 
          Convert(char(3), a.performancemonth, 0)+'-'+ 
          cast( DATEPART(YEAR, a.performancemonth) as  varchar(100))

Demo

Upvotes: 1

Related Questions