63 lines
1.9 KiB
Transact-SQL
63 lines
1.9 KiB
Transact-SQL
ALTER PROCEDURE YSQKGKQDNF_GZTH
|
||
@FSDate NVARCHAR(100),
|
||
@FEDate NVARCHAR(100)
|
||
AS
|
||
|
||
-- 声明变量
|
||
DECLARE @StartDate DATE;
|
||
DECLARE @EndDate DATE;
|
||
DECLARE @LastDate Date;
|
||
|
||
-- 将输入的字符串日期转换为日期类型
|
||
SET @StartDate = CONVERT(DATE, @FSDate, 120); -- 假设输入的日期格式为 YYYY-MM-DD
|
||
SET @EndDate = CONVERT(DATE, @FEDate, 120); -- 假设输入的日期格式为 YYYY-MM-DD
|
||
|
||
-- 计算上一年的 12 月 31 日
|
||
SET @LastDate = DATEADD(YEAR, DATEDIFF(YEAR, 0, @StartDate) - 1, 0); -- 获取 @StartDate 的年份的 1 月 1 日
|
||
SET @LastDate = DATEADD(DAY, -1, DATEADD(YEAR, 1, @StartDate)); -- 获取上一年的 12 月 31 日
|
||
|
||
TRUNCATE TABLE YSQKGK_QDNF_GZTH
|
||
INSERT INTO YSQKGK_QDNF_GZTH([签订年份],[期初到期应收款额])
|
||
SELECT
|
||
[签订年份],
|
||
[到期金额]
|
||
FROM ZRP_YingShouMingXi_yuyubo
|
||
WHERE [订单日期] <= @LastDate
|
||
AND [到期金额] <> 0
|
||
|
||
--插入日期间的到期金额数据
|
||
INSERT INTO YSQKGK_QDNF_GZTH([签订年份],[本月到期应收款额])
|
||
SELECT
|
||
[签订年份],
|
||
[到期金额]
|
||
FROM ZRP_YingShouMingXi_yuyubo
|
||
WHERE [订单日期] >= @StartDate
|
||
AND [订单日期] <= @EndDate
|
||
AND [到期金额] <> 0
|
||
|
||
--对数据进行分组合计，并插入原表中
|
||
SELECT
|
||
[签订年份],
|
||
SUM([期初到期应收款额]) as 期初到期应收款额 ,
|
||
SUM([本月到期应收款额]) as 本月到期应收款额
|
||
INTO #TEMP1
|
||
FROM YSQKGK_QDNF_GZTH
|
||
GROUP BY [签订年份]
|
||
|
||
--清空表，并将临时表的数据插入到表中
|
||
TRUNCATE TABLE YSQKGK_QDNF_GZTH
|
||
INSERT INTO YSQKGK_QDNF_GZTH([签订年份],[期初到期应收款额],[本月到期应收款额])
|
||
SELECT * FROM #TEMP1
|
||
|
||
--计算占比
|
||
--记录本年到期应收款额的总和，并更新占比字段
|
||
DECLARE @SUMDQJE DECIMAL(23, 10);
|
||
SELECT @SUMDQJE = SUM([本月到期应收款额]) FROM YSQKGK_QDNF_GZTH
|
||
|
||
UPDATE YSQKGK_QDNF_GZTH
|
||
SET [占比] = FORMAT(ROUND([本月到期应收款额] / @SUMDQJE * 100, 2), '0.##') + '%';
|
||
|
||
--计算变化值
|
||
UPDATE YSQKGK_QDNF_GZTH
|
||
SET [变化] = [本月到期应收款额]-[期初到期应收款额];
|