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
|
update FRH set (fait_id,frh.absence_id,
frh.temps_id,
frh.temps_id_fin,
frh.salarie_id,
nb_jour_ouvre )=
(select * from
(select DISTINCT frh.fait_id,
frh.absence_id,
frh.temps_id,
frh.temps_id_fin,
frh.salarie_id,
(CASE
WHEN sum(drh_temps.ferie) = to_date(frh.temps_id_fin)-to_date(frh.temps_id)+1 AND To_CHAR(frh.temps_id,'D')='6' THEN 0
WHEN sum(drh_temps.ferie) = to_date(frh.temps_id_fin)-to_date(frh.temps_id)+1 AND To_CHAR(frh.temps_id,'D')='7' THEN 0
WHEN sum(drh_temps.ferie) is null THEN (to_date(frh.temps_id_fin)-to_date(frh.temps_id)) + 1
WHEN sum(drh_temps.ferie)>=1 THEN to_date(frh.temps_id_fin)-to_date(frh.temps_id)-sum(drh_temps.ferie)+1
ELSE 0
END) as NB_JOUR_OUVRE
from frh,drh_temps
where drh_temps.temps_id between frh.temps_id and frh.temps_id_fin
And absence_id is not null
and fait_id=(select distinct frh.fait_id from frh where absence_id is not null
and nb_jour_ouvre is null)
group by frh.fait_id,frh.absence_id,
frh.temps_id,
frh.temps_id_fin,
frh.salarie_id) FRH_MAJ
where FRH.fait_id = FRH_MAJ.fait_id)
WHERE fait_id=(select distinct frh.fait_id from frh where absence_id is not null and nb_jour_ouvre is null ) |
Partager