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 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198
|
SELECT
R.TypeInfo,
R.NumWeek,
R.DepuisLe,
R.JusquAu,
COALESCE( R.Infos , 0 ) AS Infos ,
COALESCE( R.Cumul_Infos, 0 ) AS Cumul_Infos,
COALESCE( R.Prior_Infos, 0 ) AS Prior_Infos,
-- Libelle: Libellé du circuit ou du jour
-----------------------------------------------------------------------------------------
CASE R.TypeInfo WHEN 1 THEN ( SELECT Libelle FROM LibCircuits WHERE ID = R.IDCircuit )
WHEN 2 THEN CASE R.DepuisLe - NumWeek2Dates( R.NumWeek, 0 )
WHEN 0 THEN 'Lundi'
WHEN 1 THEN 'Mardi'
WHEN 2 THEN 'Mercredi'
WHEN 3 THEN 'Jeudi'
WHEN 4 THEN 'Vendredi'
WHEN 5 THEN 'Samedi'
WHEN 6 THEN 'Dimanche'
END
WHEN 3 THEN 'TOTAUX'
END AS Libelle,
-- Evolution_Week: Calcul de l'évolution par rapport à la semaine précédente en %
-----------------------------------------------------------------------------------------
CASE WHEN R.TypeInfo IN ( 1, 3 )
THEN CASE WHEN ( R.Prior_Infos = 0 ) OR ( R.Prior_Infos IS NULL )
THEN 0
ELSE ROUND( ( R.Infos - R.Prior_Infos ) * 100 / R.Prior_Infos, 2 )
END
END AS Evolution_Week,
-- DN de l'article
-----------------------------------------------------------------------------------------
CASE WHEN R.TypeInfo IN ( 1, 3 )
THEN CASE WHEN ( R.MagCount = 0 ) OR ( R.MagCount IS NULL )
THEN 0
ELSE ROUND( R.DN / R.MagCount * 100, 2 )
END
END AS DN
FROM
(
SELECT
1 AS TypeInfo ,
R.NumWeek ,
R.IDCircuit AS IDCircuit ,
NumWeek2Dates( R.NumWeek, 0 ) AS DepuisLe ,
NumWeek2Dates( R.NumWeek, 1 ) AS JusquAu ,
R.Infos ,
COALESCE( R.Cumul_Infos, 0 ) AS Cumul_Infos ,
COALESCE( R.Prior_Infos, 0 ) AS Prior_Infos ,
R.DN ,
R.MagCount
FROM
(
SELECT
P.NumWeek ,
C.ID AS IDCircuit ,
COALESCE( R.Infos , 0 ) AS Infos ,
COALESCE( R.DN , 0 ) AS DN ,
COALESCE( R.MagCount, 0 ) AS MagCount ,
CASE WHEN R.Prior_Infos IS NULL
THEN ( SELECT SUM( X.QTRedress ) FROM STAT_CAB_WEEK X
WHERE X.NumWeek = NumWeek2PriorWeek( P.NumWeek )
AND X.IDArt = 83149
AND X.IDCircuit = C.ID
)
ELSE R.Prior_Infos
END AS Prior_Infos,
CASE WHEN R.Cumul_Infos IS NULL
THEN ( SELECT SUM( X.Cumul_QTREdress ) FROM STAT_CAB_WEEK X
WHERE X.NumWeek = ( SELECT Max( NumWeek ) FROM STAT_CAB_WEEK
WHERE NumWeek < P.NumWeek
AND IDArt = X.IDArt
)
AND X.IDArt = 83149
AND X.IDCircuit = C.ID
)
ELSE R.Cumul_Infos
END AS Cumul_Infos
FROM LibCircuits C
LEFT JOIN Periodes_Week P ON 1 = 1 -- <-- ce n'est pas une erreur!
LEFT JOIN
(
SELECT
S.NumWeek ,
S.IDCircuit ,
SUM( S.QTRedress ) AS Infos ,
SUM( S.Cumul_QTRedress ) AS Cumul_Infos,
SUM( S.Prior_QTRedress ) AS Prior_Infos,
SUM( S.DN ) AS DN ,
SUM( S.MagCount ) AS MagCount
FROM Stat_CAB_Week S
WHERE S.IDArt = 83149
GROUP BY S.NumWeek, S.IDCircuit
) R ON R.NumWeek = P.NumWeek AND R.IDCircuit = C.ID
WHERE C.ID BETWEEN 1 AND 4
) R
UNION ALL
SELECT
2 AS TypeInfo ,
P.NumWeek AS NumWeek ,
NULL AS IDCircuit ,
P.DTE AS DepuisLe ,
P.DTE AS JusquAu ,
S.PC AS Infos ,
NULL AS Cumul_Infos,
NULL AS Prior_Infos,
NULL AS DN ,
NULL AS MagCount
FROM Periodes_Day P
LEFT JOIN Stat_CAB_Day S ON S.NumWeek = P.NumWeek AND S.DTE = P.DTE AND S.IDArt = 83149
UNION ALL
SELECT
3 AS TypeInfo ,
R.NumWeek ,
NULL AS IDCircuit ,
NumWeek2Dates( R.NumWeek, 0 ) AS DepuisLe ,
NumWeek2Dates( R.NumWeek, 1 ) AS JusquAu ,
R.Infos ,
COALESCE( R.Cumul_Infos, 0 ) AS Cumul_Infos ,
COALESCE( R.Prior_Infos, 0 ) AS Prior_Infos ,
R.DN ,
R.MagCount
FROM
(
SELECT
P.NumWeek ,
COALESCE( R.Infos , 0 ) AS Infos ,
COALESCE( R.DN , 0 ) AS DN ,
COALESCE( R.MagCount, 0 ) AS MagCount ,
CASE WHEN R.Prior_Infos IS NULL
THEN ( SELECT SUM( X.QTRedress ) FROM STAT_CAB_WEEK X
WHERE X.NumWeek = NumWeek2PriorWeek( P.NumWeek ) AND X.IDArt = 83149
)
ELSE R.Prior_Infos
END AS Prior_Infos,
CASE WHEN R.Cumul_Infos IS NULL
THEN ( SELECT SUM( X.Cumul_QTREdress ) FROM STAT_CAB_WEEK X
WHERE X.NumWeek = ( SELECT Max( NumWeek ) FROM STAT_CAB_WEEK
WHERE NumWeek < P.NumWeek
AND IDArt = X.IDArt
)
AND X.IDArt = 83149
)
ELSE R.Cumul_Infos
END AS Cumul_Infos
FROM Periodes_Week P
LEFT JOIN
(
SELECT
S.NumWeek ,
SUM( S.QTRedress ) AS Infos ,
SUM( S.Cumul_QTRedress ) AS Cumul_Infos,
SUM( S.Prior_QTRedress ) AS Prior_Infos,
SUM( S.DN ) AS DN ,
SUM( S.MagCount ) AS MagCount
FROM Stat_CAB_Week S
WHERE S.IDCircuit BETWEEN 1 AND 4
AND S.IDArt = 83149
GROUP BY S.NumWeek
) R ON R.NumWeek = P.NumWeek
) R
) R
WHERE R.NumWeek BETWEEN 401 AND 405
ORDER BY R.NumWeek, R.TypeInfo, R.IDCircuit, R.DepuisLe |
Partager