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
|
DECLARE @EVENT_TYPE_SORTING INT= 2; -- Tri
DECLARE @EVENT_TYPE_REJECT INT= 34; -- Rejet
DECLARE @StartDateTime DATETIME= '20180613 04:30:00';
DECLARE @EndDateTime DATETIME= '20180614 13:30:00';
SELECT Parcel.[Id] AS id,
Tache.Num AS TacheTri,
Chute.Num AS num,
dsc.SortingType AS type,
Cab_Suivi,
Cab_Pch,
Cab_Geolabel,
Parcel.[PostalCodeAcqMode] AS Acquisition_PostalCode
FROM [Chute]
LEFT OUTER JOIN Vacation
JOIN Parcel ON(Vacation.Debut BETWEEN @StartDateTime AND @EndDateTime
OR Vacation.Fin BETWEEN @StartDateTime AND @EndDateTime)
AND Vacation.Id = Parcel.VacationId
JOIN ParcelEvent ON Parcel.Id = ParcelEvent.ParcelId
AND ParcelEvent.EventTypeNum IN(@EVENT_TYPE_SORTING, @EVENT_TYPE_REJECT)
AND ParcelEvent.[DateTime] BETWEEN @StartDateTime AND @EndDateTime
LEFT JOIN (
SELECT
BarCode.ParcelId
, MAX(CASE WHEN LEN(BarCode.Barcode) = 13 THEN BarCode END ) AS Cab_Suivi
, MAX(CASE WHEN LEN(BarCode.Barcode) = 24 THEN BarCode END ) AS Cab_Pch
, MAX(CASE WHEN LEN(BarCode.Barcode) = 28 THEN BarCode END ) AS Cab_Geolabel
FROM BarCode
WHERE LEN(BarCode.Barcode) IN (13, 24, 28)
GROUP BY BarCode.ParcelId
) AS BC
ON BC.ParcelId = Parcel.[Id]
ON Chute.Num = Parcel.PhysicalChuteNum
LEFT JOIN dsc ON dsc.chute = chute.num
AND dsc.Idtache = Vacation.idtache
JOIN tache ON tache.id = dsc.IdTache; |
Partager