id_inf = date(annee,mois,21)
if mois < 12 then
  id_sup = date(annee, mois + 1, 20)
else
  id_sup = date(annee+1, 1, 20)
end if

select 
idorder,
dbo.GetEquipe(cast(a.nmatricule as varchar)) as Equipe,
a.nmatricule,
cast(a.nmatricule as varchar)+ ' / ' + dbo.Agent.lnom + ' ' + dbo.Agent.lpostnom as Agent,
lnewmotif,
a.djm, a.nmotif, 
CASE nmotif 
  WHEN 1 THEN 1 
  WHEN 2 THEN 1 
  WHEN 13 THEN 1 
  WHEN 12 THEN round(a.dailytime * 24, 0) 
  ELSE NULL 
END AS valeur,
b.day,
dayofmonth,
dayofweek
from pointage_hspnight_v AS a INNER JOIN dbo.Agent ON a.nmatricule = dbo.Agent.nmatricule RIGHT OUTER JOIN
dbo.dayofmonth AS b ON dayofmonth = b.day
where (nentity = :arg_equipe) and  (a.djm BETWEEN :arg_inf  AND :arg_sup )

union 
select 
idorder,
dbo.GetEquipe(cast(a.nmatricule as varchar)) as Equipe,
a.nmatricule,
cast(a.nmatricule as varchar)+ ' / ' + dbo.Agent.lnom + ' ' + dbo.Agent.lpostnom as Agent,
lnewmotif,
a.djm, a.nmotif, 
CASE nmotif 
  WHEN 1 THEN 1 
  WHEN 2 THEN 1 
  WHEN 13 THEN 1 
  WHEN 12 THEN round(a.dailytime * 24, 0) 
  ELSE NULL 
END AS valeur,
b.day,
dayofmonth,
dayofweek
from pointage_hspweekend_v AS a INNER JOIN dbo.Agent ON a.nmatricule = dbo.Agent.nmatricule RIGHT OUTER JOIN
dbo.dayofmonth AS b ON dayofmonth = b.day
where (nentity = :arg_equipe) and  (a.djm BETWEEN :arg_inf  AND :arg_sup )

union 
select 
idorder,
dbo.GetEquipe(cast(a.nmatricule as varchar)) as Equipe,
a.nmatricule,
cast(a.nmatricule as varchar)+ ' / ' + dbo.Agent.lnom + ' ' + dbo.Agent.lpostnom as Agent,
lnewmotif,
a.djm, a.nmotif, 
CASE nmotif 
  WHEN 1 THEN 1 
  WHEN 2 THEN 1 
  WHEN 13 THEN 1 
  WHEN 12 THEN round(a.dailytime * 24, 0) 
  ELSE NULL 
END AS valeur,
b.day,
dayofmonth,
dayofweek
from pointage_hsp_v AS a INNER JOIN dbo.Agent ON a.nmatricule = dbo.Agent.nmatricule RIGHT OUTER JOIN
dbo.dayofmonth AS b ON dayofmonth = b.day
where (nentity = :arg_equipe) and   (a.djm BETWEEN :arg_inf  AND :arg_sup )

union 
select 
idorder,
dbo.GetEquipe(cast(a.nmatricule as varchar)) as Equipe,
a.nmatricule,
cast(a.nmatricule as varchar)+ ' / ' + dbo.Agent.lnom + ' ' + dbo.Agent.lpostnom as Agent,
lnewmotif,
a.djm, a.nmotifin nmotif, 
CASE nmotifin 
  WHEN 1 THEN 1 
  WHEN 2 THEN 1 
  WHEN 13 THEN 1 
  WHEN 12 THEN round(a.dailytime * 24, 0) 
  ELSE NULL 
END AS valeur,
b.day,
dayofmonth,
dayofweek
from pointage_noworkdaily_v AS a INNER JOIN dbo.Agent ON a.nmatricule = dbo.Agent.nmatricule RIGHT OUTER JOIN
dbo.dayofmonth AS b ON dayofmonth = b.day
where (nentity = :arg_equipe) and  (a.djm BETWEEN :arg_inf  AND :arg_sup )

union 
select 
idorder,
dbo.GetEquipe(cast(a.nmatricule as varchar)) as Equipe,
a.nmatricule,
cast(a.nmatricule as varchar)+ ' / ' + dbo.Agent.lnom + ' ' + dbo.Agent.lpostnom as Agent,
'Travail' lnewmotif,
a.djm, a.nmotifin as nmotif, 
CASE a.nmotifin 
  WHEN 1 THEN 1 
  WHEN 2 THEN 1 
  WHEN 13 THEN 1 
  WHEN 12 THEN round(sum(totaltime) over ( partition by a.nmatricule, djm ) * 24, 0) 
  ELSE NULL 
END AS valeur,
b.day,
DATEPART(dd, djm) as dayofmonth,
case dbo.F_ISO_WEEKDAY(djm)
  when 1 then 'D'
  when 2 then 'L'
  when 3 then 'M'
  when 4 then 'M'
  when 5 then 'J'
  when 6 then 'V'
  when 7 then 'S'
end  as dayofweek 
from dbo.pointage_deltatime_sum_v AS a INNER JOIN dbo.Agent ON a.nmatricule = dbo.Agent.nmatricule RIGHT OUTER JOIN
dbo.dayofmonth AS b ON DATEPART(dd, djm) = b.day
where nmotifin in ( 1, 2, 14, 16, 19,20 ) 
     and (nentity = :arg_equipe) and   (a.djm BETWEEN :arg_inf  AND :arg_sup )
group by  a.nmatricule, djm ,b.day, a.nmotifin, totaltime, idorder, dbo.Agent.lnom,dbo.Agent.lpostnom

dw_report.retrieve( il_equipe, date(id_inf), date(id_sup))