需求說明

常見帳齡分析的報表例如：客戶帳齡分析、產品庫存呆滯天數分析...等。

舉例一組簡化的客戶發票資料，包括單號、日期、金額，

欲將發票資料依天數組距加總統計，得到客戶帳齡分析，

如下圖。

原始資料

交易資料中必要欄位有：

一個日期欄位 : 用來計算與指定日期相差幾天

一個數值欄位 : 用來各組距彙總時的數值

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;

定義天數組距

0 ~ 30 : 表示 0 天至 30 天

31 ~ 60 : 表示 31 天至 60 天

以此類推

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

計算步驟

1. 延伸原始資料

(1) Age : 計算交易資料日期 [TRANS_DATE] 與指定日期 (@Calculate) 相差幾天

(2) 天數組距 : 由 [Age ] 對應組距名稱

DECLARE @Calculate Date = convert(date, '2022-01-01') 

select * 
from( 
 select P1.* 
  /*Age: 計算交易資料日期 [TRANS_DATE] 與指定日期 (@Calculate) 相差幾天 */ 
  , 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

2.依照組距加總數值

利用 PIVOT TABLE 產生各組距的加總。

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

搜尋此網誌

RobinNotes

使用 T-SQL 計算帳齡分析 Aging Analysis Report

需求說明

原始資料

計算步驟

1. 延伸原始資料

相關連結