?? 應(yīng)付賬齡分析_proc.sql
字號(hào):
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
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號(hào)
Ctrl + =
減小字號(hào)
Ctrl + -