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
| select result.libelle, ISNULL(sum(result.nbReservations3), 0) as delai3, ISNULL(sum(result.nbReservations7),0) as delai7, ISNULL(SUM (result.nbReservations14),0) as delai14,result.Code
from sejour.NatureSejour nat
leftjoin
(
select n.libelle as libelle, ISNULL(COUNT(i.ID_Inscription),0) as nbReservations3, '' as nbReservations7,'' as nbReservations14, n.Code as code
from i
inner join p on p.ID_Periode = i.ID_Periode
inner join a on art.ID_Article = p.ID_Article
inner join s sej on sej.ID_Sejour = a.ID_Sejour
inner join NS nat on nat.ID_NatureSejour = s.ID_NatureSejour
INNER JOIN u ON u.ID_Utilisateur = i.ID_Utilisateur
where i.ID_TypeInscription = 2
and DATEDIFF(DD ,GETDATE(),i.DateEcheance) between 0 and 3
and i.DateAnnulation is null
AND u.ID_Region = 2
group by libelle,code
union
select n.libelle as libelle,'' as nbReservations3, ISNULL(COUNT(i.ID_Inscription),0) as nbReservations7, '' as nbReservations14, n.Code as code
from i
inner join p on p.ID_Periode = i.ID_Periode
inner join a on art.ID_Article = p.ID_Article
inner join s sej on sej.ID_Sejour = a.ID_Sejour
inner join NS nat on nat.ID_NatureSejour = s.ID_NatureSejour
INNER JOIN u ON u.ID_Utilisateur = i.ID_Utilisateur
where i.ID_TypeInscription = 2
and DATEDIFF(DD ,GETDATE(),i.DateEcheance) between 0 and 7
and i.DateAnnulation is NULL
AND u.ID_Region = 2
group by libelle,code
union
select n.libelle as libelle,'' as nbReservations3, '' as nbReservations7, ISNULL(COUNT(i.ID_Inscription),0) as nbReservations14, n.Code as code
from i
inner join p on p.ID_Periode = i.ID_Periode
inner join a on art.ID_Article = p.ID_Article
inner join s sej on sej.ID_Sejour = a.ID_Sejour
inner join NS nat on nat.ID_NatureSejour = s.ID_NatureSejour
INNER JOIN u ON u.ID_Utilisateur = i.ID_Utilisateur
where i.ID_TypeInscription = 2
and DATEDIFF(DD ,GETDATE(),i.DateEcheance) between 0 and 14
and i.DateAnnulation is NULL
AND u.ID_Region = 2
group by libelle,code
)
as result on nat.libelle = result.libelle
group by result.libelle,result.code |
Partager