1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| CREATE TABLE PERIODE
(
ID bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
DATD date NOT NULL,
JOURS integer DEFAULT 1,
CONSTRAINT INTEG_81 PRIMARY KEY (ID)
);
COMMIT;
INSERT INTO PERIODE (DATD, JOURS) VALUES ('01.01.2021', '2');
INSERT INTO PERIODE (DATD, JOURS) VALUES ('05.01.2021', '0');
INSERT INTO PERIODE (DATD, JOURS) VALUES ('08.01.2021', '2');
COMMIT;
SET TERM ^ ;
CREATE PROCEDURE JOURSDISPO (
FROMDATE date,
TODATE date )
RETURNS (
DISPODATE date )
AS
DECLARE VARIABLE N INTEGER;
BEGIN
FOR WITH RECURSIVE dates AS (
SELECT :FROMDATE date1
FROM rdb$database
UNION ALL
SELECT date1 + 1
FROM dates
WHERE date1 < :TODATE
)
SELECT Date1 FROM Dates into :DISPODATE
DO BEGIN
SELECT COUNT(1) FROM PERIODE P WHERE :DISPODATE BETWEEN P.DATD AND dateadd(P.JOURS DAY TO P.DATD) INTO :N;
IF ((N=0) AND (EXTRACT(WEEKDAY FROM :DISPODATE)<5)) -- vendredi/samedi chomés
THEN SUSPEND;
END
END
^
SET TERM ; ^
COMMIT;
SELECT p.DISPODATE,EXTRACT(WEEKDAY FROM p.DISPODATE)
FROM JOURSDISPO ('01.01.2021', '20.01.2021') p; |
Partager