YTD
现有合同表一份, 要求计算任意指定范围内的每个月的YTD (全部同时列出). 输出字段: 年月, YTD金额。
合同表数据:
insert into "HANABI"."CONTRACTS" VALUES(1,'2012-04-01', '2013-06-30',100);
insert into "HANABI"."CONTRACTS" VALUES(2,'2013-07-01', '2015-03-31',100);
insert into "HANABI"."CONTRACTS" VALUES(3,'2014-02-01', '2015-07-31',100);
insert into "HANABI"."CONTRACTS" VALUES(4,'2014-05-01', '2016-10-31',100);
insert into "HANABI"."CONTRACTS" VALUES(5,'2014-07-01', '2015-05-31',100);
insert into "HANABI"."CONTRACTS" VALUES(6,'2015-01-01', '2016-02-29',100);
insert into "HANABI"."CONTRACTS" VALUES(7,'2015-08-01', '2016-09-30',100);
insert into "HANABI"."CONTRACTS" VALUES(8,'2014-02-01', '2014-12-31',100);
insert into "HANABI"."CONTRACTS" VALUES(9,'2015-02-01', '2015-03-30',100);
一.建模
1.1建表创建日期表、合同表。
合同表:
年月表:
通过EXCEL的CSV格式向年月表导入数据:
1.2创建视图
创建年月属性试图:
创建YEARM计算列调整年月格式:
创建合同分析视图:
创建join时的计算列CONT:
创建计算列STARTI设置合同开始时间YYYYMM格式:
创建计算列ENDTI设置合同结束时间YYYYMM格式:
创建计算列STAR_YEAR_INT提取合同开始年:
创建计算列END_YEAR_INT提取合同结束年:
创建计算列STR_MON_INT提取合同开始月:
创建计算列STR_MON_INT提取合同结束月:
创建计算视图关联合同分析视图和年月属性视图
创建计算列YM调整年月格式为YYYYMM
通过CONT字段将合同分析视图和年月属性视图关联
创建GL计算列通过输入参数对年月进行过滤:
创建计算列YTD
创建时间过滤
1.3结果展示
输入年月范围201406-201506.
二.创建脚本计算试图
创建YM,YTD输出列:
SQL语句展示:
/********* Begin Procedure Script ************/
BEGIN
var_out =
SELECT
YM,
SUM(
CASE
WHEN YM > ENDTI AND
TO_INT(LEFT(STARTI,4)) = TO_INT(LEFT(ENDTI,4))
THEN (TO_INT(RIGHT(ENDTI,2)) - TO_INT(RIGHT(STARTI,2))+ 1) * AMOUNT
WHEN YM > ENDTI AND
TO_INT(LEFT(STARTI,4)) < TO_INT(LEFT(ENDTI,4))
THEN TO_INT(RIGHT(ENDTI,2)) * AMOUNT
WHEN YM <= ENDTI AND
YEAR_INT = TO_INT(LEFT(STARTI,4))
THEN (MONTH_INT - TO_INT(RIGHT(STARTI,2))+ 1)*AMOUNT
ELSE MONTH_INT * AMOUNT
END
) AS YTD
FROM
(SELECT YEAR_INT,
MONTH_INT,
TO_CHAR(CONCAT (LEFT(CONCAT(YEAR_INT,CONCAT('0',MONTH_INT)),4),RIGHT(CONCAT(YEAR_INT,CONCAT('0',MONTH_INT)),2)))
AS YM
FROM "HA_NC"."DATE_YDT") ,
(SELECT START_DATE,END_DATE,AMOUNT,
CONCAT(LEFT(START_DATE,4),RIGHT(LEFT(START_DATE,7),2))
STARTI ,
CONCAT(LEFT(END_DATE,4),RIGHT(LEFT(END_DATE,7),2))
ENDTI
FROM "HA_NC"."CONTRACTS")
WHERE YM >= STARTI AND
YEAR_INT <= TO_INT(LEFT(ENDTI,4))
GROUP BY YM;
END /*********End Procedure Script ************/
结果展示:
三。存储过程
创建存储过程:
创建Stored Procedure文件:
代码如下:
PROCEDURE "HA_NC"."NC::PRACTICE" (IN STARTT VARCHAR(6),IN ENDTT VARCHAR(6) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT
SCHEMA <default_schema_name>
--READS
SQL DATA
AS
BEGIN
DELETE FROM "HA_NC"."BBB";
INSERT INTO "HA_NC"."BBB"
SELECT
YM,
SUM(
CASE
WHEN YM > ENDTI AND TO_INT(LEFT(STARTI,4)) = TO_INT(LEFT(ENDTI,4))
THEN (TO_INT(RIGHT(ENDTI,2)) - TO_INT(RIGHT(STARTI,2)) + 1) * AMOUNT
WHEN YM > ENDTI AND TO_INT(LEFT(STARTI,4)) < TO_INT(LEFT(ENDTI,4))
THEN TO_INT(RIGHT(ENDTI,2)) * AMOUNT
WHEN YM <= ENDTI AND YEAR_INT = TO_INT(LEFT(STARTI,4))
THEN (MONTH_INT - TO_INT(RIGHT(STARTI,2)) + 1)*AMOUNT
ELSE MONTH_INT * AMOUNT
END
) AS YTD
FROM
(SELECT YEAR_INT,
MONTH_INT,
TO_CHAR(CONCAT (LEFT(CONCAT(YEAR_INT,CONCAT('0',MONTH_INT)),4),RIGHT(CONCAT(YEAR_INT,CONCAT('0',MONTH_INT)),2))) AS YM
FROM "HA_NC"."DATE_YDT") ,
(SELECT START_DATE,END_DATE,AMOUNT,
CONCAT(LEFT(START_DATE,4),RIGHT(LEFT(START_DATE,7),2))
STARTI ,
CONCAT(LEFT(END_DATE,4),RIGHT(LEFT(END_DATE,7),2))
ENDTI
FROM "HA_NC"."CONTRACTS")
WHERE YM >= STARTI AND YEAR_INT <= TO_INT(LEFT(ENDTI,4)) AND YM >=STARTT AND YM <=ENDTT
GROUP BY YM;
END;
创建存储数据的表:
调用存储过程
通过CALL来调用,如图:
结果展示
查询表BBB数据显示: