1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| SELECT
vwOcrTelegrams.Part,
Switch((vwOcrTelegrams.STATUS=0) Or (vwOcrTelegrams.STATUS=8),'Lu',(vwOcrTelegrams.STATUS=3) Or (vwOcrTelegrams.STATUS=5),'Non lu',True,'Autre') AS STATUS,
Count(vwOcrTelegrams.ItemID) AS Nb_colis
FROM
(vwOcrTelegrams INNER JOIN
vwCodes ON vwOcrTelegrams.ItemID = vwCodes.ItemID) INNER JOIN
vwItemData ON vwOcrTelegrams.ItemID = vwItemData.ItemID
WHERE
(vwOcrTelegrams.EventTime between #19/02/2014 00:00# AND #20/02/2014 00:00#)
AND (vwItemData.InductionMode = 'scanner')
AND (vwOcrTelegrams.VideoCoderId > 9997)
AND (LEFT(vwCodes.Code, 2) IN ('8D', '8T', 'CK', '8N', '8U', '8Y', '8Z', 'CE', 'CC', '7D', '8P', '8B', '8F', '8A', '8C', '8H', '8I', 'CJ', 'CH', '7M'))
GROUP BY
vwOcrTelegrams.Part, vwOcrTelegrams.Status
ORDER BY
vwOcrTelegrams.Part DESC,
vwOcrTelegrams.Status DESC |