Hello,
I have built a Power Pivot Excel report to calculate WTD, MTD, QTD, YTD calculations. The problem is that Excel is far too big (80 MB) so I need to do the calculations in SQL and just have Excel making the report look pretty.
The key points are:
The problem domain is banking accounts
The grain of the data is daily snapshots i.e. end of day closing balances for each account
The datasets contains data for Today, Last Week (defined as Sunday), Last Month, Last Quarter and Last Year
I need to calculate Values Movements (e.g. Balance Today - Balance Last Year)
I need to calculate Volume Movements (e.g. Number of account Today - Number of accounts Last Year) by Segment
Below is a sample data set and the two reports that I need to produce. I have seen various ways of doing this, including Windows Functions. Is there a recommended and simple way of doing this in T-SQL?
---------------------------------------------------------------------------------------
-- Sample data
---------------------------------------------------------------------------------------
drop table if exists #Data
create table #Data
(
SnapshotDate date not null
, AccountNumber int not null
, Segment varchar(20) not null
, Balance money not null
)
-- Account 123 : existed in every period
insert into #Data values('18Aug2021', 123, 'Accountancy', 45)
insert into #Data values('15Aug2021', 123, 'Accountancy', 55) -- Last Sunday
insert into #Data values('31Jul2021', 123, 'Accountancy', 1506) -- Last Month
insert into #Data values('30Jun2021', 123, 'Accountancy', 4560) -- Last Quarter
insert into #Data values('31Dec2020', 123, 'Accountancy', 20) -- Last year
-- Account 456 : did not exist end of last year
insert into #Data values('18Aug2021', 456, 'Accountancy', 345)
insert into #Data values('15Aug2021', 456, 'Accountancy', 10) -- Last Sunday
insert into #Data values('31Jul2021', 456, 'Accountancy', 550) -- Last Month
insert into #Data values('30Jun2021', 456, 'Accountancy', 450) -- Last Quarter
-- Account 789 : missing latest snapshot
insert into #Data values('15Aug2021', 789, 'Pension', 1) -- Last Sunday
insert into #Data values('31Jul2021', 789, 'Pension', 220) -- Last Month
insert into #Data values('30Jun2021', 789, 'Pension', 434) -- Last Quarter
insert into #Data values('31Dec2020', 789, 'Pension', 800) -- Last year
select *
from #Data
order by SnapshotDate asc, AccountNumber
---------------------------------------------------------------------------------------
-- All Accounts Report
---------------------------------------------------------------------------------------
drop table if exists #AllAccountsReport
create table #AllAccountsReport
(
AccountNumber int not null
, Segment varchar(20) not null
, BalanceToday money not null
-- Value Movements
, WTD_Value money not null
, MTD_Value money not null
, QTD_Value money not null
, YTD_Value money not null
)
select *
from #AllAccountsReport
---------------------------------------------------------------------------------------
-- Segment Report
---------------------------------------------------------------------------------------
drop table if exists #SegmentReport
create table #SegmentReport
(
Segment varchar(20) not null
, BalanceToday money not null
-- Value Movements
, WTD_Value money not null
, MTD_Value money not null
, QTD_Value money not null
, YTD_Value money not null
-- Volume Movements
, WTD_Volume money not null
, MTD_Volume money not null
, QTD_Volume money not null
, YTD_Volume money not null
)
select *
from #SegmentReport


