IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='應收賬齡分析_PROC' AND TYPE='P')
DROP PROC 應收賬齡分析_PROC
GO
create  PROC 應收賬齡分析_PROC
AS
create table #收款匯總
(發票號碼 VARCHAR(20),
發票日期 DATETIME,
借方金額 NUMERIC(18,6),
貸方金額 NUMERIC(18,6),
借貸余額 AS 借方金額-貸方金額)

create table #賬齡

(發票號碼 INT,
[270天] NUMERIC(18,6)DEFAULT(0.0000),
[180天] NUMERIC(18,6)DEFAULT(0.0000),
[120天] NUMERIC(18,6)DEFAULT(0.0000),
[90天] NUMERIC(18,6)DEFAULT(0.0000),
[60天] NUMERIC(18,6)DEFAULT(0.0000),
[45天] NUMERIC(18,6)DEFAULT(0.0000),
[30天] NUMERIC(18,6)DEFAULT(0.0000),
[15天] NUMERIC(18,6)DEFAULT(0.0000),
[0天] NUMERIC(18,6) DEFAULT(0.0000),)


INSERT  INTO  #收款匯總 (發票號碼,發票日期,借方金額,貸方金額)
SELECT 發票號碼,發票日期,SUM(借方金額_原幣),SUM(貸方金額_原幣) FROM 應收賬款明細賬
GROUP BY  發票號碼, 發票日期 


DECLARE @日期 DATETIME 
SELECT  @日期=發票日期 FROM #收款匯總
IF  DATEdiff(DY,@日期,GETDATE())>270
INSERT  INTO  #賬齡(發票號碼,[270天])SELECT 發票號碼,借貸余額 FROM #收款匯總 
ELSE
IF DATEdiff(DY,@日期,GETDATE())>180 
INSERT  INTO  #賬齡(發票號碼,[180天])SELECT 發票號碼,借貸余額 FROM #收款匯總
ELSE
IF  DATEdiff(DY,@日期,GETDATE())>120
INSERT  INTO  #賬齡(發票號碼,[120天])SELECT 發票號碼,借貸余額 FROM #收款匯總
ELSE
IF  DATEdiff(DY,@日期,GETDATE())>90
INSERT  INTO  #賬齡(發票號碼,[90天])SELECT 發票號碼,借貸余額 FROM #收款匯總
ELSE
IF DATEdiff(DY,@日期,GETDATE())>60 
INSERT  INTO  #賬齡(發票號碼,[60天])SELECT 發票號碼,借貸余額 FROM #收款匯總
ELSE
IF  DATEdiff(DY,@日期,GETDATE())>45
INSERT  INTO  #賬齡(發票號碼,[45天])SELECT 發票號碼,借貸余額 FROM #收款匯總
ELSE
IF DATEdiff(DY,@日期,GETDATE())>30 
INSERT  INTO  #賬齡(發票號碼,[30天])SELECT 發票號碼,借貸余額 FROM #收款匯總
ELSE
IF DATEdiff(DY,@日期,GETDATE())>15
INSERT  INTO  #賬齡(發票號碼,[15天])SELECT 發票號碼,借貸余額 FROM #收款匯總 
ELSE
IF  DATEdiff(DY,@日期,GETDATE())=0
INSERT  INTO  #賬齡(發票號碼,[0天])SELECT 發票號碼,借貸余額 FROM #收款匯總 

SELECT *FROM  #賬齡

--EXEC