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
| WITH
VeloVert AS
(
SELECT YEAR(VELO_DAT_CRE) As ANNEE,
COUNT(ID_VELO_VERT ) As NOMBRE_VELO,
LIBELLE_PARKING As PARKING
FROM dbo.TAB_VELO_VERT
INNER JOIN dbo.TAB_PROPRIO
ON TAB_PROPRIO.ID_PROPRIO = TAB_VELO_VERT.ID_PROPRIO
INNER JOIN [dbo].[VUE : CEN : Liste des parking ouverts]
ON [VUE : CEN : Liste des parking ouverts].[ID_PARKING] = TAB_PROPRIO.ID_PARKING
GROUP BY YEAR(VELO_DAT_CRE), LIBELLE_PARKING
),
VeloJaune AS
(
SELECT YEAR(VELO_DAT_CRE) As ANNEE,
COUNT(ID_VELO_JAUNE ) As NOMBRE_VELO,
LIBELLE_PARKING As PARKING
FROM dbo.TAB_VELO_JAUNE
INNER JOIN dbo.TAB_PROPRIO
ON TAB_PROPRIO.ID_PROPRIO = TAB_VELO_JAUNE.ID_PROPRIO
INNER JOIN [dbo].[VUE : CEN : Liste des parking ouverts]
ON [VUE : CEN : Liste des parking ouverts].[ID_PARKING] = TAB_PROPRIO.ID_PARKING
GROUP BY YEAR(VELO_DAT_CRE), LIBELLE_PARKING
),
VeloRouge AS
(
SELECT YEAR(VELO_DAT_CRE) As ANNEE,
COUNT(ID_VELO_ROUGE ) As NOMBRE_VELO,
LIBELLE_PARKING As PARKING
FROM dbo.TAB_VELO_ROUGE
INNER JOIN dbo.TAB_PROPRIO
ON TAB_PROPRIO.ID_PROPRIO = TAB_VELO_ROUGE.ID_PROPRIO
INNER JOIN [dbo].[VUE : CEN : Liste des parking ouverts]
ON [VUE : CEN : Liste des parking ouverts].[ID_PARKING] = TAB_PROPRIO.ID_PARKING
GROUP BY YEAR(VELO_DAT_CRE), LIBELLE_PARKING
),
Parks AS
(SELECT PARKING
FROM VeloVert
UNION
SELECT PARKING
FROM VeloJaune
UNION
SELECT PARKING
FROM VeloRouge
),
An AS
(SELECT ANNEE
FROM VeloVert
UNION
SELECT ANNEE
FROM VeloJaune
UNION
SELECT ANNEE
FROM VeloRouge
),
ParkAn AS
(
SELECT A.ANNEE, P.PARKING
FROM Parks AS P
CROSS JOIN An AS A
)
SELECT PA.ANNEE, PA.PARKING, VV.NOMBRE_VELO + VJ.NOMBRE_VELO + VR.NOMBRE_VELO AS TOTAL_VELO
FROM ParkAn AS PA
LEFT OUTER JOIN VeloVert AS VV
ON PA.ANNEE = VV.ANNEE AND PA.PARKING = VV.PARKING
LEFT OUTER JOIN VeloJaune AS VJ
ON PA.ANNEE = VJ.ANNEE AND PA.PARKING = VJ.PARKING
LEFT OUTER JOIN VeloRouge AS VR
ON PA.ANNEE = VR.ANNEE AND PA.PARKING = VR.PARKING; |
Partager