oracle计算两个日期间的工作日（根据表配置）

首先新增表用于配置非工作日，工作日，具体配置在程序中处理

DROP TABLE  "T_WEEKDAY";
CREATE TABLE "T_WEEKDAY" (
  "FYEAR" VARCHAR2(100 BYTE),
  "FDATE" VARCHAR2(100 BYTE) NOT NULL,
  "FFLAG" VARCHAR2(100 BYTE),
  "FWEEK" VARCHAR2(100 BYTE),
  "FTIME" VARCHAR2(100 BYTE),
  "FEMPID" VARCHAR2(255 BYTE),
  "FEMPNAME" VARCHAR2(255 BYTE),
  "CREATETIME" DATE
)
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "T_WEEKDAY"."FYEAR" IS '年份';
COMMENT ON COLUMN "T_WEEKDAY"."FDATE" IS '日期';
COMMENT ON COLUMN "T_WEEKDAY"."FFLAG" IS '0-非工作日 1-工作日 ';
COMMENT ON COLUMN "T_WEEKDAY"."FWEEK" IS '星期';
COMMENT ON COLUMN "T_WEEKDAY"."FTIME" IS '工作时间,号隔开';
COMMENT ON COLUMN "T_WEEKDAY"."FEMPID" IS '维护人编号';
COMMENT ON COLUMN "T_WEEKDAY"."FEMPNAME" IS '维护人姓名';
COMMENT ON COLUMN "T_WEEKDAY"."CREATETIME" IS '维护时间';


ALTER TABLE  "T_WEEKDAY" ADD CONSTRAINT "SYS_C0026111" PRIMARY KEY ("FDATE");
 

ALTER TABLE  "T_WEEKDAY" ADD CONSTRAINT "SYS_C0026110" CHECK ("FDATE" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

插入几条数据

INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-03-16', '1', '星期三', '08:30-12:00,14:00-18:00', NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-03-18', '1', '星期五', '08:30-12:00,14:00-18:00', NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-03-22', '1', '星期二', '08:30-12:00,14:00-18:00', NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-03-25', '1', '星期五', '08:30-12:00,14:00-18:00', NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-03-29', '1', '星期二', '08:30-12:00,14:00-18:00', NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-03-31', '1', '星期四', '08:30-12:00,14:00-18:00', NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-04-05', '1', '星期二', '08:30-12:00,14:00-18:00', NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-04-07', '1', '星期四', '08:30-12:00,14:00-18:00', NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-01-22', '0', '星期六', NULL, NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));  --配置的节假日
INSERT INTO "T_WEEKDAY" VALUES ('2022', '2022-01-23', '0', '星期日', NULL, NULL, NULL, TO_DATE('2022-03-20 11:50:03', 'SYYYY-MM-DD HH24:MI:SS'));  --配置的节假日

创建函数

--计算两个日期间隔的天数,去除配置的节假日，这边计算的是工作日大于十天的
create or replace function SUMDAY(fodate in date) RETURN int IS
  V_COUNT int;
begin
  SELECT case
           when count(d.fdate) > 10 then
            1
           else
            0
         end
    into V_COUNT
    FROM t_weekday d
   where d.fflag = '1' --1为工作日，0为节假日
     and d.fdate > to_char(fodate, 'yyyy-mm-dd')
     and d.fdate <= to_char(sysdate, 'yyyy-mm-dd');
  return V_COUNT;
end;


--调用
SELECT  a.fmrdid,
sysdate, --当天
a.fodate,--出院日期
SumDay(a.fodate) days --1为大于10个工作日，0为小于10个工作日
 FROM    t_noentryquery a;

下面为计算根据FZYYSNO, FDSTYPE两个字段进行分组求两个日期间的工作日总和大于10天的，t_noentryquery 表可自行创建

SELECT a.FZYYSNO, a.FDSTYPE, count(a.days) days
  FROM (SELECT a.FZYYSNO,
               a.FDSTYPE,
               (TRUNC(sysdate, 'IW') - TRUNC(fodate, 'IW')) * 5 / 7 +
               LEAST(TRUNC(sysdate) - TRUNC(sysdate, 'IW') + 1, 5) -
               LEAST(TRUNC(fodate) - TRUNC(fodate, 'IW'), 5) AS days
          FROM t_noentryquery a) a
 where a.days > 10
 group by a.FZYYSNO, a.FDSTYPE;