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
|
with TAB as (
SELECT HU.ID_INDIV, HU.MATRICULE , HU.NOM_USAGE , HU.PRENOM ,
--RTT (h)
case when GT1.NOMBRE IS null then 0 else GT1.NOMBRE end as rtt, '(h)' as Unité,
case when sum (GT2.NOMBRE) IS null then 0 else sum(GT2.NOMBRE) end as rttplus,
case when sum (GT3.DUREE_EVT)/3600 IS null then 0 else sum (GT3.DUREE_EVT)/3600 end as rttmoins
FROM [HORSYS].[dbo].[GT_HUMAIN] HU
left outer join [HORSYS].[dbo].[GT_VARIAB] GT1 on HU.ID_INDIV = GT1.FK_INDIV and GT1.FK_RUBRIK = 372
and GT1.DATEMVT = (select MAX (GT1.[DATEMVT]) from [HORSYS].[dbo].[GT_VARIAB] GT1 where GT1.FK_RUBRIK = 372 and GT1.[FK_INDIV] = HU.ID_INDIV)
left outer join [HORSYS].[dbo].[GT_VARIAB] GT2 on HU.ID_INDIV = GT2.FK_INDIV and GT2.FK_RUBRIK = 311
and GT2.DATEMVT > (select MAX (GT1.[DATEMVT]) from [HORSYS].[dbo].[GT_VARIAB] GT1 where GT1.FK_RUBRIK = 372 and GT1.[FK_INDIV] = HU.ID_INDIV)
left outer join [HORSYS].[dbo].[GT_EVT] GT3 on HU.ID_INDIV = GT3.FK_INDIV and GT3.FK_CODEVT = 294 and GT3.ETATMVT = 'T'
and GT3.DATEMVT > (select MAX (GT1.[DATEMVT]) from [HORSYS].[dbo].[GT_VARIAB] GT1 where GT1.FK_RUBRIK = 372 and GT3.[FK_INDIV] = HU.ID_INDIV)
WHERE FINGTA >= '20220401' and MCTR_CAT <> 'CAD' and SOCIETE = 'MECA'
group by HU.ID_INDIV, MATRICULE, NOM_USAGE, PRENOM, GT1.NOMBRE, GT2.NOMBRE
)
select ID_INDIV, MATRICULE, ROUND(( rtt + rttplus - rttmoins ),2) as 'Solde RTT', Unité, Rtt , Rttplus , Rttmoins
from TAB
where MATRICULE = '31100'
ORDER BY Unité, NOM_USAGE, PRENOM |
Partager