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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
|
SELECT orga, matricule, num_contrat, deb_abs, fin_abs, code_abs
FROM (
SELECT orga
, matricule
, num_contrat
, deb_abs
, Min(fin_abs) "fin_abs"
, 'présent' "code_abs"
FROM (SELECT ABS1.orga
, ABS1.matricule
, ABS1.num_contrat
, ABS1.fin_abs + 1 "deb_abs"
, ABS2.deb_abs - 1 "fin_abs"
FROM CONTRAT C
JOIN ABSENCE ABS1
ON ( C.matricule = ABS1.matricule
AND C.num_contrat = ABS1.num_contrat
AND C.orga=ABS1.orga)
JOIN ABSENCE ABS2
ON ( C.matricule = ABS2.matricule
AND C.num_contrat = ABS2.num_contrat
AND C.orga=ABS2.orga)
WHERE ABS1.fin_abs < ABS2.deb_abs
AND ABS1.orga = ABS2.orga
AND ABS1.orga = '0001') T
-- WHERE matricule = 'R0550934'
GROUP BY orga
, matricule
, num_contrat
, deb_abs
UNION ALL
SELECT orga
, matricule
, num_contrat
, deb_abs
, fin_abs
, 'présent !'
FROM (SELECT C.orga
, C.matricule
, C.num_contrat
, C.deb_contrat "deb_abs"
, Min(ABS1.deb_abs) - 1 "fin_abs"
FROM CONTRAT C
JOIN ABSENCE ABS1
ON (C.matricule = ABS1.matricule
AND C.num_contrat = ABS1.num_contrat
AND C.orga=ABS1.orga)
JOIN ABSENCE ABS2
ON (C.matricule = ABS2.matricule
AND C.num_contrat = ABS2.num_contrat
AND C.orga=ABS2.orga)
WHERE ABS1.deb_abs > C.deb_contrat
AND NOT EXISTS (SELECT ''
FROM ABSENCE ABS3
WHERE C.matricule = ABS3.matricule
AND C.num_contrat = ABS3.num_contrat
AND C.deb_contrat = ABS3.deb_abs
AND C.orga=ABS3.orga)
AND C.orga='0001'
GROUP BY C.orga
, C.matricule
, C.num_contrat
, C.deb_contrat) T
-- WHERE matricule = 'R0550934'
UNION ALL
SELECT C.orga
, C.matricule
, C.num_contrat
, Max(ABS1.fin_abs) + 1 "deb_abs"
, C.fin_contrat "fin_abs"
, 'présent'
FROM CONTRAT C
JOIN ABSENCE ABS1
ON (C.matricule = ABS1.matricule
AND C.num_contrat = ABS1.num_contrat
AND C.orga=ABS1.orga)
JOIN ABSENCE ABS2
ON (C.matricule = ABS2.matricule
AND C.num_contrat = ABS2.num_contrat
AND C.orga = ABS2.orga)
WHERE ABS1.fin_abs < C.fin_contrat
-- AND C.matricule = 'R0550934'
AND C.orga = '0001'
GROUP BY C.orga
, C.matricule
, C.num_contrat
, C.fin_contrat
UNION ALL
SELECT C.orga
, C.matricule
, C.num_contrat
, C.deb_contrat
, C.fin_contrat
, 'présent tout le temps !'
FROM CONTRAT C
WHERE NOT EXISTS (SELECT ''
FROM ABSENCE ABS
WHERE C.matricule = ABS.matricule
AND C.num_contrat = ABS.num_contrat
AND C.orga=ABS.orga)
-- AND C.matricule = 'R0550934'
AND C.orga='0001'
UNION ALL
SELECT ABS.orga
, ABS.matricule
, ABS.num_contrat
, ABS.deb_abs
, ABS.fin_abs
, ABS.code_abs
FROM ABSENCE ABS
WHERE ABS.orga='0001'
-- AND ABS.matricule = 'R0550934'
) PT
WHERE PT.deb_abs <= PT.fin_abs
ORDER BY 2, 3, 4 |
Partager