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
|
WITH t AS
(
SELECT
to_date('29/09/2009', 'dd/mm/yyyy') + (level - 1) * 1/24 * 4 AS borninf,
to_date('29/09/2009', 'dd/mm/yyyy') + (level) * 1/24 * 4 - (1/(24*60*60)) AS bornsup
FROM dual
CONNECT BY level <= (trunc(sysdate) - to_date('29/09/2009'))*24/4
),
presence as
(
select
'29/09/2009' as date_entree, '05:12:05' as heure_entree,
'30/09/2009' as date_sortie, '08:18:05' as heure_sortie
from dual
union all
select
'30/09/2009' as date_entree, '05:12:05' as heure_entree,
'30/09/2009' as date_sortie, '08:39:05' as heure_sortie
from dual
union all
select
'30/09/2009' as date_entree, '08:12:05' as heure_entree,
'30/09/2009' as date_sortie, '08:19:05' as heure_sortie
from dual
union all
select
'30/09/2009' as date_entree, '09:12:05' as heure_entree,
'30/09/2009' as date_sortie, '10:19:05' as heure_sortie
from dual
union all
select
'30/09/2009' as date_entree, '07:12:05' as heure_entree,
'30/09/2009' as date_sortie, '13:19:05' as heure_sortie
from dual
union all
select
'01/10/2009' as date_entree, '09:12:05' as heure_entree,
'01/10/2009' as date_sortie, '20:19:05' as heure_sortie
from dual
union all
select
'30/09/2009' as date_entree, '06:12:05' as heure_entree,
'30/09/2009' as date_sortie, '07:19:05' as heure_sortie
from dual
)
SELECT
t.borninf,
t.bornsup,
coalesce(entrees.nb_entrees,0) as nb_entrees,
coalesce(sorties.nb_sorties,0) as nb_sorties,
sum(coalesce(entrees.nb_entrees,0)-coalesce(sorties.nb_sorties,0)) over (partition by null order by t.borninf) as occupation
FROM t
LEFT JOIN
(
SELECT
t.borninf,
t.bornsup,
count(*) AS nb_entrees
FROM t
JOIN presence entree
ON to_date(entree.date_entree || entree.heure_entree,'dd/mm/yyyyhh24:mi:ss') BETWEEN t.borninf AND t.bornsup
GROUP BY
t.borninf,
t.bornsup
) entrees ON t.borninf = entrees.borninf and t.bornsup = entrees.bornsup
LEFT JOIN
(
SELECT
t.borninf,
t.bornsup,
count(*) AS nb_sorties
FROM t
JOIN presence sortie
ON to_date(sortie.date_sortie || sortie.heure_sortie,'dd/mm/yyyyhh24:mi:ss') BETWEEN t.borninf AND t.bornsup
GROUP BY
t.borninf,
t.bornsup
) sorties ON t.borninf = sorties.borninf AND t.bornsup = sorties.bornsup
ORDER BY t.borninf |
Partager