Write a SQL query to calculate the balance in a card [closed]

Can you please help me how to calculate the balance for each quarter

The data table

Script to create the table:

Create Table CardTransactions ( CardId varchar(10), Trans_num int, Quarter varchar(25), TransactionAmount float, Balance_Trans float )

insert into CardTransactions values

(‘Card1’, 1, ‘FQ-2013-4’, 10 , 5 ),
(‘Card1’, 2, ‘FQ-2013-4’, 3.65, 1.35 ),
(‘Card1’, 3, ‘FQ-2014-1’, -10 , 11.35 ),
(‘Card1’, 4, ‘FQ-2014-1’, -10 , 21.35 ), (‘Card1’, 5, ‘FQ-2014-1’, 4.2 , 17.15 ), (‘Card1’, 6, ‘FQ-2014-3’, 2 , 15.15 ), (‘Card1’, 7, ‘FQ-2014-3’, 1.15, 14 ),
(‘Card1’, 8, ‘FQ-2014-4’, -20 , 34 ),
(‘Card1’, 9, ‘FQ-2014-4’, 5.15, 28.85 ), (‘Card1’, 10, ‘FQ-2015-3’, 4 , 24.85 ), (‘Card1’, 11, ‘FQ-2015-3’, 2.5 , 22.35 ), (‘Card1’, 12, ‘FQ-2015-3’, 2.35, 20 )

select * from CardTransactions;

Expected result: Need the result like this

I’ve tried to run this query like this but not working

select 
   distinct ct1.CardId, 
   ct1.Quarter,
   ct2.Balance_Trans-ct1.TransactionAmount balance, 
   ROW_NUMBER() over(partition by ct1.Quarter order by ct1.Quarter) rn
from CardTransactions ct1
join  CardTransactions ct2
   on ct1.Trans_num >= ct2.Trans_num;

Answer

You seem to want the last balance_trans for each card and quarter.

One method is:

select ct.*
from (select ct.*,
             row_number() over (partition by cardid, quarter order by trans_num desc) as seqnum
      from CardTransactions ct
     ) ct
where seqnum = 1;

You can also express this as:

select ct.*
from CardTransactions ct
where ct.trans_num = (select max(ct2.trans_num)
                      from CardTransactions ct2
                      where ct2.cardid = ct.cardid and 
                            ct2.quarter = ct.quarter
                     );