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

create table #賬齡
(發(fā)票號(hào)碼 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  #付款匯總 (發(fā)票號(hào)碼,發(fā)票日期,借方金額,貸方金額)
SELECT 發(fā)票號(hào)碼,發(fā)票日期,SUM(借方金額),SUM(貸方金額) FROM 應(yīng)付賬款明細(xì)賬
GROUP BY  發(fā)票號(hào)碼, 發(fā)票日期 

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

SELECT *FROM  #賬齡

--  EXEC 應(yīng)付賬齡分析_PROC