Skip to content

Instantly share code, notes, and snippets.

@robinli

robinli/T-SQL for Aging Analysis Report - 01 Source Data

Created January 17, 2022 08:02
Show Gist options
  • Save robinli/06cfe8e9c34e92bde29ab76b2b829b7f to your computer and use it in GitHub Desktop.
Save robinli/06cfe8e9c34e92bde29ab76b2b829b7f to your computer and use it in GitHub Desktop.
Download ZIP
T-SQL for Aging Analysis Report
Raw
T-SQL for Aging Analysis Report - 01 Source Data
DECLARE @SourceData AS TABLE(DOCNO varchar(16), TRANS_DATE date, AMOUNT int)
insert into @SourceData(DOCNO, TRANS_DATE, AMOUNT)
select DOCNO='2021011501', TRANS_DATE=convert(date,'2021-01-15'),AMOUNT=300 union
select DOCNO='2021052601', TRANS_DATE=convert(date,'2021-05-26'),AMOUNT=400 union
select DOCNO='2021060701', TRANS_DATE=convert(date,'2021-06-07'),AMOUNT=300 union
select DOCNO='2021060801', TRANS_DATE=convert(date,'2021-06-08'),AMOUNT=400 union
select DOCNO='2021060901', TRANS_DATE=convert(date,'2021-06-09'),AMOUNT=700 union
select DOCNO='2021071001', TRANS_DATE=convert(date,'2021-07-10'),AMOUNT=800 union
select DOCNO='2021071101', TRANS_DATE=convert(date,'2021-07-11'),AMOUNT=900 union
select DOCNO='2021081201', TRANS_DATE=convert(date,'2021-08-12'),AMOUNT=1000 union
select DOCNO='2021081301', TRANS_DATE=convert(date,'2021-08-13'),AMOUNT=1100 union
select DOCNO='2021081401', TRANS_DATE=convert(date,'2021-08-14'),AMOUNT=1200 union
select DOCNO='2021081501', TRANS_DATE=convert(date,'2021-08-15'),AMOUNT=1300 union
select DOCNO='2021091601', TRANS_DATE=convert(date,'2021-09-16'),AMOUNT=1400 union
select DOCNO='2021101701', TRANS_DATE=convert(date,'2021-10-17'),AMOUNT=1500 union
select DOCNO='2021101801', TRANS_DATE=convert(date,'2021-10-18'),AMOUNT=1600 union
select DOCNO='2021111901', TRANS_DATE=convert(date,'2021-11-19'),AMOUNT=1700 union
select DOCNO='2021112001', TRANS_DATE=convert(date,'2021-11-20'),AMOUNT=1800 union
select DOCNO='2021112101', TRANS_DATE=convert(date,'2021-11-21'),AMOUNT=1900 union
select DOCNO='2021122201', TRANS_DATE=convert(date,'2021-12-22'),AMOUNT=2000 union
select DOCNO='2021122001', TRANS_DATE=convert(date,'2021-12-20'),AMOUNT=2100 ;
select * from @SourceData;
Raw
T-SQL for Aging Analysis Report - 02 Age Group
DECLARE @AgeGroup AS TABLE(DATE_FROM int, DATE_TO int, AGE_GROUP varchar(16))
insert into @AgeGroup(DATE_FROM, DATE_TO, AGE_GROUP)
select DATE_FROM=0, DATE_TO=30, AGE_GROUP='0 ~ 30' union
select DATE_FROM=31, DATE_TO=60, AGE_GROUP='31 ~ 60' union
select DATE_FROM=61, DATE_TO=90, AGE_GROUP='61 ~ 90' union
select DATE_FROM=91, DATE_TO=180, AGE_GROUP='91 ~ 180' union
select DATE_FROM=181, DATE_TO=360, AGE_GROUP='181 ~ 360'
select * from @AgeGroup
Raw
T-SQL for Aging Analysis Report - 03 Calculate Age days
DECLARE @Calculate Date = convert(date, '2022-01-01')
/*依照組距加總數值*/
select [0 ~ 30] = sum(isnull([0],0))
, [31 ~ 60] = sum(isnull([31],0))
, [61 ~ 90] = sum(isnull([61],0))
, [91 ~ 180] = sum(isnull([91],0))
, [181 ~ 360] = sum(isnull([181],0))
from(
select *
from(
select P1.*
/*Age / 天數 : 計算交易資料 [日期] 與指定日期相差幾天*/
, Age = DATEDIFF(day, P1.TRANS_DATE, @Calculate)
from @SourceData P1
) P2
outer apply(
/*天數組距 : 由 [Age / 天數] 對應組距名稱*/
select Ag.DATE_FROM, Ag.AGE_GROUP
from @AgeGroup Ag
where P2.Age>=Ag.DATE_FROM and P2.Age<=Ag.DATE_TO
) A
) Src
pivot(
sum(AMOUNT)
for Src.DATE_FROM in([0],[31],[61],[91],[181])
) Pvt
Raw
T-SQL for Aging Analysis Report - 10 Complete example
DECLARE @SourceData AS TABLE(DOCNO varchar(16), TRANS_DATE date, AMOUNT int)
insert into @SourceData(DOCNO, TRANS_DATE, AMOUNT)
select DOCNO='2021011501', TRANS_DATE=convert(date,'2021-01-15'),AMOUNT=300 union
select DOCNO='2021052601', TRANS_DATE=convert(date,'2021-05-26'),AMOUNT=400 union
select DOCNO='2021060701', TRANS_DATE=convert(date,'2021-06-07'),AMOUNT=300 union
select DOCNO='2021060801', TRANS_DATE=convert(date,'2021-06-08'),AMOUNT=400 union
select DOCNO='2021060901', TRANS_DATE=convert(date,'2021-06-09'),AMOUNT=700 union
select DOCNO='2021071001', TRANS_DATE=convert(date,'2021-07-10'),AMOUNT=800 union
select DOCNO='2021071101', TRANS_DATE=convert(date,'2021-07-11'),AMOUNT=900 union
select DOCNO='2021081201', TRANS_DATE=convert(date,'2021-08-12'),AMOUNT=1000 union
select DOCNO='2021081301', TRANS_DATE=convert(date,'2021-08-13'),AMOUNT=1100 union
select DOCNO='2021081401', TRANS_DATE=convert(date,'2021-08-14'),AMOUNT=1200 union
select DOCNO='2021081501', TRANS_DATE=convert(date,'2021-08-15'),AMOUNT=1300 union
select DOCNO='2021091601', TRANS_DATE=convert(date,'2021-09-16'),AMOUNT=1400 union
select DOCNO='2021101701', TRANS_DATE=convert(date,'2021-10-17'),AMOUNT=1500 union
select DOCNO='2021101801', TRANS_DATE=convert(date,'2021-10-18'),AMOUNT=1600 union
select DOCNO='2021111901', TRANS_DATE=convert(date,'2021-11-19'),AMOUNT=1700 union
select DOCNO='2021112001', TRANS_DATE=convert(date,'2021-11-20'),AMOUNT=1800 union
select DOCNO='2021112101', TRANS_DATE=convert(date,'2021-11-21'),AMOUNT=1900 union
select DOCNO='2021122201', TRANS_DATE=convert(date,'2021-12-22'),AMOUNT=2000 union
select DOCNO='2021122001', TRANS_DATE=convert(date,'2021-12-20'),AMOUNT=2100 ;
select * from @SourceData;
DECLARE @AgeGroup AS TABLE(DATE_FROM int, DATE_TO int, AGE_GROUP varchar(16))
insert into @AgeGroup(DATE_FROM, DATE_TO, AGE_GROUP)
select DATE_FROM=0, DATE_TO=30, AGE_GROUP='0 ~ 30' union
select DATE_FROM=31, DATE_TO=60, AGE_GROUP='31 ~ 60' union
select DATE_FROM=61, DATE_TO=90, AGE_GROUP='61 ~ 90' union
select DATE_FROM=91, DATE_TO=180, AGE_GROUP='91 ~ 180' union
select DATE_FROM=181, DATE_TO=360, AGE_GROUP='181 ~ 360'
select * from @AgeGroup
DECLARE @Calculate Date = convert(date, '2022-01-01')
/*依照組距加總數值*/
select [0 ~ 30] = sum(isnull([0],0))
, [31 ~ 60] = sum(isnull([31],0))
, [61 ~ 90] = sum(isnull([61],0))
, [91 ~ 180] = sum(isnull([91],0))
, [181 ~ 360] = sum(isnull([181],0))
from(
select *
from(
select P1.*
/*Age / 天數 : 計算交易資料 [日期] 與指定日期相差幾天*/
, Age = DATEDIFF(day, P1.TRANS_DATE, @Calculate)
from @SourceData P1
) P2
outer apply(
/*天數組距 : 由 [Age / 天數] 對應組距名稱*/
select Ag.DATE_FROM, Ag.AGE_GROUP
from @AgeGroup Ag
where P2.Age>=Ag.DATE_FROM and P2.Age<=Ag.DATE_TO
) A
) Src
pivot(
sum(AMOUNT)
for Src.DATE_FROM in([0],[31],[61],[91],[181])
) Pvt
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment