71 lines
1.8 KiB
Transact-SQL
71 lines
1.8 KiB
Transact-SQL
|
||
DECLARE @FSDate NVARCHAR(100);
|
||
DECLARE @FEDate NVARCHAR(100);
|
||
|
||
--清空表的数据
|
||
TRUNCATE TABLE YSQKGK_QDNF
|
||
|
||
--定义变量
|
||
DECLARE @FEDateDate DATE;
|
||
|
||
--判断@FEDate日期是否为空
|
||
IF @FEDate IS NULL OR @FEDate = ''
|
||
BEGIN
|
||
SET @FEDateDate = GETDATE();
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
-- 转换为日期类型
|
||
SET @FEDateDate = CONVERT(DATE, @FEDate);
|
||
END
|
||
|
||
-- 获取上一年的 12 月 31 日
|
||
SET @FEDateDate = DATEFROMPARTS(YEAR(@FEDateDate) - 1, 12, 31);
|
||
|
||
SET @FSDate = '2025-01-31'
|
||
SET @FEDate = '2025-04-01'
|
||
|
||
--根据日期参数@FEDateDate，过滤出条件年初到期金额
|
||
SELECT ISNULL([签订年份],1) AS 签订年份,SUM([到期金额]) AS 到期金额
|
||
INTO #QCTEMP1
|
||
FROM ZRP_YingShouMingXi_yuyubo
|
||
WHERE [到期时间] <= @FEDateDate
|
||
GROUP BY [签订年份]
|
||
ORDER BY [签订年份] DESC
|
||
|
||
--插入期初数据
|
||
INSERT INTO YSQKGK_QDNF([签订年份],[年初到期应收款额])
|
||
SELECT [签订年份],[到期金额] FROM #QCTEMP1 WHERE [签订年份] <> 1
|
||
|
||
--根据过滤条件的日期过滤到期金额
|
||
SELECT ISNULL([签订年份],1) AS 签订年份 ,SUM([到期金额]) AS 到期金额
|
||
INTO #BNTEMP1
|
||
FROM ZRP_YingShouMingXi_yuyubo
|
||
WHERE (@FSDate = '' OR [到期时间] >= @FSDate)
|
||
AND (@FEDate = '' OR [到期时间] <= @FEDate)
|
||
GROUP BY [签订年份]
|
||
ORDER BY [签订年份] DESC
|
||
|
||
--插入本年到期金额数据
|
||
INSERT INTO YSQKGK_QDNF([签订年份],[本年到期应收款额])
|
||
SELECT [签订年份],[到期金额] FROM #BNTEMP1 WHERE [签订年份] <> 1
|
||
|
||
SELECT *
|
||
INTO #TEMP1
|
||
FROM
|
||
(SELECT [签订年份],SUM(年初到期应收款额) AS 年初到期应收额,
|
||
SUM([本年到期应收款额]) AS 本年到期应收款额
|
||
FROM YSQKGK_QDNF GROUP BY [签订年份]) AS YS
|
||
|
||
--清空表的数据
|
||
TRUNCATE TABLE YSQKGK_QDNF
|
||
|
||
|
||
INSERT INTO YSQKGK_QDNF([签订年份],[年初到期应收款额],[本年到期应收款额])
|
||
SELECT [签订年份],[年初到期应收额],[本年到期应收款额] FROM #TEMP1
|
||
|
||
|
||
|
||
DROP TABLE #QCTEMP1,#BNTEMP1,#TEMP1
|
||
|