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 44 45 46
|
SET TERM ^ ;
CREATE PROCEDURE SEMAINE_TYPE
( CONTRATDU DATE,
CONTRATAU DATE)
RETURNS
( REFSALARIE INTEGER,
REFCONTRAT INTEGER,
DATEJOUR DATE,
HTHEO DECIMAL(18,2))
AS
BEGIN
FOR
WITH C AS
(SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,1+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT) AS DATEJOUR ,NBHEURES_LUNDI AS HTHEO FROM CONTRATDETRAVAIL
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_LUNDI>0
UNION
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,2+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_MARDI FROM CONTRATDETRAVAIL
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_MARDI>0
UNION
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,3+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_MERCREDI FROM CONTRATDETRAVAIL
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_MERCREDI>0
UNION
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,4+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_JEUDI FROM CONTRATDETRAVAIL
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_JEUDI>0
UNION
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,5+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_VENDREDI FROM CONTRATDETRAVAIL
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_VENDREDI>0
UNION
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,6+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_SAMEDI FROM CONTRATDETRAVAIL
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_SAMEDI>0
UNION
SELECT REFSALARIE,REFCONTRAT,DATEADD(DAY,7+(EXTRACT(WEEKDAY FROM DATEDEBUT)*-1),DATEDEBUT),NBHEURES_DIMANCHE FROM CONTRATDETRAVAIL
WHERE DATEDEBUT>=:CONTRATDU AND DATEFIN<=:CONTRATAU AND NBHEURES_DIMANCHE>0
)
-- c'est cette partie qu'il faut travailler
SELECT * FROM C INTO :REFSALARIE,:REFCONTRAT,:DATEJOUR,:HTHEO
DO SUSPEND;
END^
SET TERM ; ^ |
Partager