Month on month changes in a variable aggregated by columns

Transact-SQL
1

HI,

First post.

I am looking into changes in a column, accrued interest on a month-by-month basis and can find the total change by using the following:

SELECT (reportDate),
sum(accruedInterest) - LAG(sum(accruedInterest)) Over (order by Year(reportDate), Month(reportDate),Day(reportDate))
FROM
dbo.tblPortCharacteristics
WHERE
reportDate in('2022-11-30','2022-10-31')
group by (reportDate)

But I would like to see it on a position level (securitydescription) and a portfolio level (portfolio)

I adjust my SQL to

SELECT (reportDate),(securityDescription),(Portfolio),
sum(accruedInterest) - LAG(sum(accruedInterest)) Over (order by Year(reportDate), Month(reportDate),Day(reportDate))
FROM
dbo.tblPortCharacteristics
WHERE
reportDate in('2022-11-30')
group by (reportDate),(SecurityDescription),(Portfolio)

But the sum of the new column does not equal the change gathered from my first query. How could I adjust this? I would like to see the change in accrued interest for that month with the last month and list this by securityDescription and Portfolio

2

I would suggest doing it slightly differently, and in my opinion, in a more straightforward manner as shown below. Here I am going to assume that the accrued interest is the monthly interest accrual, and not the year-to-date accrual.

SELECT
    reportDate,
    SecurityDescription,
    Portfolio,
    SUM(accruedInterest) OVER (PARTITION BY YEAR(reportDate), Portfolio,SecurityDescription ORDER BY reportDate)
ORDER BY
    Portfolio,
    SecurityDescription,
    reportDate;

In the PARTITION BY clause:

  • YEAR(reportDate) makes sure that you add up only interest for the current year.
  • Portforlio makes sure that you add up accrued interest for each portfolio in a separate bucket.
  • SecurityDescription will ensure that you accrue interest for each securitydescription in a separate bucket.
1 Like
3

Thanks James for that, this is a good way to show the rolled up accrued interest, how I can show the monthly changes? I.e. the difference between monthly interest in this month vs last month for each security and portfolio

4

LEAD LAG functions

5

SELECT
reportDate,
SecurityDescription,
Portfolio,
SUM(accruedInterest) OVER (PARTITION BY YEAR(reportDate), Portfolio,SecurityDescription ORDER BY reportDate)
-LAG(sum(accruedInterest)) Over(PARTITION BY YEAR(reportDate), Portfolio,SecurityDescription ORDER BY reportDate)
from dbo.tblPortCharacteristics
ORDER BY
Portfolio,
SecurityDescription,
reportDate;

I have this but doesn't work

7

without having sample data very hard to tell

please provide sample data

8
reportDate Security Portfolio Accrued Interest
1/6/2023 Security 1 Portfolio 1 132031
1/6/2023 Security 2 Portfolio 2 28301
1/6/2023 Security 3 Portfolio 3 103178
1/6/2023 Security 4 Portfolio 4 183013
1/6/2023 Security 5 Portfolio 5 1930103
1/6/2023 Security 6 Portfolio 6 381993
1/6/2023 Security 7 Portfolio 7 19031
1/6/2023 Security 8 Portfolio 1 19310171
1/6/2023 Security 9 Portfolio 2 45863
1/6/2023 Security 10 Portfolio 3 283021
1/6/2023 Security 11 Portfolio 4 38290420
1/6/2023 Security 12 Portfolio 5 8492042
1/6/2023 Security 13 Portfolio 6 459868
1/6/2023 Security 14 Portfolio 7 356138
1/6/2023 Security 15 Portfolio 1 431015
1/6/2023 Security 16 Portfolio 2 510850
1/6/2023 Security 17 Portfolio 3 2257940
1/6/2023 Security 18 Portfolio 4 709830
1/6/2023 Security 19 Portfolio 5 346868
1/6/2023 Security 20 Portfolio 6 19638008
1/6/2023 Security 21 Portfolio 7 373700
1/6/2023 Security 22 Portfolio 1 610858
1/6/2023 Security 23 Portfolio 2 38618257
1/6/2023 Security 24 Portfolio 3 8819879
1/6/2023 Security 25 Portfolio 4 787705
1/6/2023 Security 26 Portfolio 5 683975
1/6/2023 Security 27 Portfolio 6 758852
1/6/2023 Security 28 Portfolio 7 838687
1/6/2023 Security 29 Portfolio 1 2585777
1/6/2023 Security 30 Portfolio 2 1037667
2/17/2023 Security 1 Portfolio 3 674705
2/17/2023 Security 2 Portfolio 4 19965845
2/17/2023 Security 3 Portfolio 5 701537
2/17/2023 Security 4 Portfolio 6 938695
2/17/2023 Security 5 Portfolio 7 38946094
2/17/2023 Security 6 Portfolio 1 9147716
2/17/2023 Security 7 Portfolio 2 1115542
2/17/2023 Security 8 Portfolio 3 1011812
2/17/2023 Security 9 Portfolio 4 1086689
2/17/2023 Security 10 Portfolio 5 1166524
2/17/2023 Security 11 Portfolio 6 2913614
2/17/2023 Security 12 Portfolio 7 1365504
2/17/2023 Security 13 Portfolio 1 1002542
2/17/2023 Security 14 Portfolio 2 20293682
2/17/2023 Security 15 Portfolio 3 1029374
2/17/2023 Security 16 Portfolio 4 1266532
2/17/2023 Security 17 Portfolio 5 39273931
2/17/2023 Security 18 Portfolio 6 9475553
2/17/2023 Security 19 Portfolio 7 1443379
2/17/2023 Security 20 Portfolio 1 1339649
2/17/2023 Security 21 Portfolio 2 1414526
2/17/2023 Security 22 Portfolio 3 1494361
2/17/2023 Security 23 Portfolio 4 3241451
2/17/2023 Security 24 Portfolio 5 1693341
2/17/2023 Security 25 Portfolio 6 1330379
2/17/2023 Security 26 Portfolio 7 20621519
2/17/2023 Security 27 Portfolio 1 1357211
2/17/2023 Security 28 Portfolio 2 1594369
2/17/2023 Security 29 Portfolio 3 39601768
2/17/2023 Security 30 Portfolio 4 9803390
9
drop create sample data script

drop table if exists #tblPortCharacteristics
create table #tblPortCharacteristics ( reportDate date , Security varchar(20) , Portfolio varchar(20) , AccruedInterest int )
insert into #tblPortCharacteristics select '1/6/2023', 'Security 1' ,'Portfolio 1',132031
insert into #tblPortCharacteristics select '1/6/2023', 'Security 2' ,'Portfolio 2',28301
insert into #tblPortCharacteristics select '1/6/2023', 'Security 3' ,'Portfolio 3',103178
insert into #tblPortCharacteristics select '1/6/2023', 'Security 4' ,'Portfolio 4',183013
insert into #tblPortCharacteristics select '1/6/2023', 'Security 5' ,'Portfolio 5',1930103
insert into #tblPortCharacteristics select '1/6/2023', 'Security 6' ,'Portfolio 6',381993
insert into #tblPortCharacteristics select '1/6/2023', 'Security 7' ,'Portfolio 7',19031
insert into #tblPortCharacteristics select '1/6/2023', 'Security 8' ,'Portfolio 1',19310171
insert into #tblPortCharacteristics select '1/6/2023', 'Security 9' ,'Portfolio 2',45863
insert into #tblPortCharacteristics select '1/6/2023', 'Security 10','Portfolio 3',283021
insert into #tblPortCharacteristics select '1/6/2023', 'Security 11','Portfolio 4',38290420
insert into #tblPortCharacteristics select '1/6/2023', 'Security 12','Portfolio 5',8492042
insert into #tblPortCharacteristics select '1/6/2023', 'Security 13','Portfolio 6',459868
insert into #tblPortCharacteristics select '1/6/2023', 'Security 14','Portfolio 7',356138
insert into #tblPortCharacteristics select '1/6/2023', 'Security 15','Portfolio 1',431015
insert into #tblPortCharacteristics select '1/6/2023', 'Security 16','Portfolio 2',510850
insert into #tblPortCharacteristics select '1/6/2023', 'Security 17','Portfolio 3',2257940
insert into #tblPortCharacteristics select '1/6/2023', 'Security 18','Portfolio 4',709830
insert into #tblPortCharacteristics select '1/6/2023', 'Security 19','Portfolio 5',346868
insert into #tblPortCharacteristics select '1/6/2023', 'Security 20','Portfolio 6',19638008
insert into #tblPortCharacteristics select '1/6/2023', 'Security 21','Portfolio 7',373700
insert into #tblPortCharacteristics select '1/6/2023', 'Security 22','Portfolio 1',610858
insert into #tblPortCharacteristics select '1/6/2023', 'Security 23','Portfolio 2',38618257
insert into #tblPortCharacteristics select '1/6/2023', 'Security 24','Portfolio 3',8819879
insert into #tblPortCharacteristics select '1/6/2023', 'Security 25','Portfolio 4',787705
insert into #tblPortCharacteristics select '1/6/2023', 'Security 26','Portfolio 5',683975
insert into #tblPortCharacteristics select '1/6/2023', 'Security 27','Portfolio 6',758852
insert into #tblPortCharacteristics select '1/6/2023', 'Security 28','Portfolio 7',838687
insert into #tblPortCharacteristics select '1/6/2023', 'Security 29','Portfolio 1',2585777
insert into #tblPortCharacteristics select '1/6/2023', 'Security 30','Portfolio 2',1037667
insert into #tblPortCharacteristics select '2/17/2023', 'Security 1','Portfolio 3',674705
insert into #tblPortCharacteristics select '2/17/2023', 'Security 2','Portfolio 4',19965845
insert into #tblPortCharacteristics select '2/17/2023', 'Security 3','Portfolio 5',701537
insert into #tblPortCharacteristics select '2/17/2023', 'Security 4','Portfolio 6',938695
insert into #tblPortCharacteristics select '2/17/2023', 'Security 5','Portfolio 7',38946094
insert into #tblPortCharacteristics select '2/17/2023', 'Security 6','Portfolio 1',9147716
insert into #tblPortCharacteristics select '2/17/2023', 'Security 7','Portfolio 2',1115542
insert into #tblPortCharacteristics select '2/17/2023', 'Security 8','Portfolio 3',1011812
insert into #tblPortCharacteristics select '2/17/2023', 'Security 9','Portfolio 4',1086689
insert into #tblPortCharacteristics select '2/17/2023', 'Security 10','Portfolio 5',1166524
insert into #tblPortCharacteristics select '2/17/2023', 'Security 11','Portfolio 6',2913614
insert into #tblPortCharacteristics select '2/17/2023', 'Security 12','Portfolio 7',1365504
insert into #tblPortCharacteristics select '2/17/2023', 'Security 13','Portfolio 1',1002542
insert into #tblPortCharacteristics select '2/17/2023', 'Security 14','Portfolio 2',20293682
insert into #tblPortCharacteristics select '2/17/2023', 'Security 15','Portfolio 3',1029374
insert into #tblPortCharacteristics select '2/17/2023', 'Security 16','Portfolio 4',1266532
insert into #tblPortCharacteristics select '2/17/2023', 'Security 17','Portfolio 5',39273931
insert into #tblPortCharacteristics select '2/17/2023', 'Security 18','Portfolio 6',9475553
insert into #tblPortCharacteristics select '2/17/2023', 'Security 19','Portfolio 7',1443379
insert into #tblPortCharacteristics select '2/17/2023', 'Security 20','Portfolio 1',1339649
insert into #tblPortCharacteristics select '2/17/2023', 'Security 21','Portfolio 2',1414526
insert into #tblPortCharacteristics select '2/17/2023', 'Security 22','Portfolio 3',1494361
insert into #tblPortCharacteristics select '2/17/2023', 'Security 23','Portfolio 4',3241451
insert into #tblPortCharacteristics select '2/17/2023', 'Security 24','Portfolio 5',1693341
insert into #tblPortCharacteristics select '2/17/2023', 'Security 25','Portfolio 6',1330379
insert into #tblPortCharacteristics select '2/17/2023', 'Security 26','Portfolio 7',20621519
insert into #tblPortCharacteristics select '2/17/2023', 'Security 27','Portfolio 1',1357211
insert into #tblPortCharacteristics select '2/17/2023', 'Security 28','Portfolio 2',1594369
insert into #tblPortCharacteristics select '2/17/2023', 'Security 29','Portfolio 3',39601768
insert into #tblPortCharacteristics select '2/17/2023', 'Security 30','Portfolio 4',9803390

Now Please explain how you want to see the data ?
image

10

See below. Depending on what you want to show when you enter a new position or exit a position, use Change1 or Change2. Change1 will show NULL in such cases, Change2 will assume the absent position generated zero accrual.

SELECT
    reportDate,
    SecurityDescription,
    Portfolio,
    YTDAccrual = SUM(accruedInterest) OVER (PARTITION BY YEAR(reportDate), Portfolio,SecurityDescription ORDER BY reportDate),
    Change1 = accruedInterest - LAG(accruedInterest) OVER (PARTITION BY Portfolio, SecurityDescription ORDER BY reportDate),
    Change2 = COALESCE(accruedInterest,0) - LAG(accruedInterest,1,0) OVER (PARTITION BY Portfolio, SecurityDescription ORDER BY reportDate)
ORDER BY
    Portfolio,
    SecurityDescription,
    reportDate;
1 Like