工作日计算设计思路:
1.创建节假日表;包含字段:主键id(自增),日期,天数(如3月8号,0.5),假期类型(两种类型:节假日(包括法定节假日及周六日),调休工作日);
2.根据年份批量导入周六日,根据数据库类型可自行百度:sql获取周六日:
https://www.cnblogs.com/wayne-ivan/archive/2008/04/10/1146308.html
3.手动维护法定节假日及调休日,注意:法定节假日包含周六日时不要重复录入;
4.sql书写,减去节假日+调休日;
如:统计需减去的节假日:select sum(节假日表天数字段) from 节假日表 where 节假日表日期字段>=创建时间 and 节假日表日期字段<=结束时间 and 假期类型=节假日;
统计需要增加的调休日:select sum(节假日表天数字段) from 节假日表 where 节假日表日期字段>=创建时间 and 节假日表日期字段<=结束时间 and 假期类型=调休工作日;
CREATE OR REPLACE FUNCTION "itsm"."p_holiday"()
RETURNS "pg_catalog"."void" AS $BODY$
DECLARE holidayTime DATE;
DECLARE weekday int4;
BEGIN
holidayTime:= '2020-01-01';
WHILE holidayTime<='2020-12-31'::DATE LOOP
weekday =EXTRACT(DOW FROM holidayTime);
IF weekday=6 THEN
INSERT INTO t_holiday("HOLIDAY_DATE","DAY_NUM","HOLIDAY_TYPE") VALUES (holidayTime,1,1);
END IF;
IF weekday=0 THEN
INSERT INTO t_holiday("HOLIDAY_DATE","DAY_NUM","HOLIDAY_TYPE") VALUES (holidayTime,1,1);
END IF;
holidayTime:=holidayTime+1;
END LOOP;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
CREATE OR REPLACE FUNCTION "itsm"."p_holiday_afterWorkDay5"("holiday_start" date)
RETURNS "pg_catalog"."date" AS $BODY$
DECLARE i int4;
DECLARE holiday_end DATE;
DECLARE j int4;
BEGIN
i:=1;
WHILE i<=5 LOOP
SELECT count("ID") INTO j FROM t_holiday WHERE "HOLIDAY_DATE"=(holiday_start+1);
IF j>=1 THEN
SELECT count("ID") INTO j FROM t_holiday WHERE "HOLIDAY_DATE"=(holiday_start+1) AND "HOLIDAY_TYPE"=0;
IF j=1 THEN
holiday_start:=holiday_start+1;
i:=i+1;
ELSE
holiday_start:=holiday_start+1;
END IF;
ELSE
holiday_start:=holiday_start+1;
i:=i+1;
END IF;
END LOOP;
RETURN holiday_start+1;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
CREATE OR REPLACE FUNCTION "itsm"."p_caculate_workdays"(IN "work_start_day" date, IN "work_end_day" date, OUT "j" int4)
RETURNS "pg_catalog"."int4" AS $BODY$
BEGIN
j:=(work_end_day-work_start_day)
-COALESCE((SELECT SUM ("DAY_NUM") FROM t_holiday WHERE "HOLIDAY_DATE">work_start_day AND "HOLIDAY_DATE"<=work_end_day AND "HOLIDAY_TYPE"=1),0)
+COALESCE((SELECT SUM ("DAY_NUM") FROM t_holiday WHERE "HOLIDAY_DATE">work_start_day AND "HOLIDAY_DATE"<=work_end_day AND "HOLIDAY_TYPE"=0),0) ;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100