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
| create procedure SP_TriNonMeca
@StartDateTime DateTime,
@EndDateTime DateTime
as
--DECLARE @StartDateTime DateTime = '20190513 04:30:00'
--DECLARE @EndDateTime DateTime = '20190514 04:30:00'
;WITH HN as (
select DISTINCT
[archive].dbo.PrintTableDetail.Chute,
[archive].dbo.PrintTableDetail.Row,
[customer].dbo.T_NonMeca.destination_NM,
[customer].dbo.T_NonMeca.SortingType,
[archive].dbo.parcel.Id as NbrColisNM
FROM [archive].dbo.Parcel
inner JOIN [archive].dbo.ParcelEvent
ON [archive].dbo.Parcel.Id=[archive].dbo.ParcelEvent.ParcelId AND [archive].dbo.Parcel.SystemNum=1 and [archive].dbo.parcelevent.EventTypeNum=2
AND [archive].dbo.ParcelEvent.[DateTime] BETWEEN @StartDateTime AND @EndDateTime
inner JOIN [archive].dbo.Vacation
ON [archive].dbo.Parcel.VacationId=[archive].dbo.Vacation.Id
inner JOIN [archive].dbo.Tache
ON [archive].dbo.Vacation.IdTache=[archive].dbo.Tache.Id
inner JOIN [archive].dbo.PrintTable
ON [archive].dbo.Tache.Id=[archive].dbo.PrintTable.IdTache
inner JOIN [archive].dbo.PrintTableDetail
ON [archive].dbo.PrintTableDetail.PrintTableId=[archive].dbo.PrintTable.Id
AND [archive].dbo.Parcel.PostalCode BETWEEN [archive].dbo.PrintTableDetail.BeginPostalCode AND [archive].dbo.PrintTableDetail.EndPostalCode
inner join [customer].dbo.T_NonMeca
on [customer].dbo.T_NonMeca.NumChute=[archive].dbo.PrintTableDetail.Chute
group BY
[archive].dbo.PrintTableDetail.Chute,
[archive].dbo.PrintTableDetail.Row,
[customer].dbo.T_NonMeca.destination_NM,
[customer].dbo.T_NonMeca.SortingType,
[archive].dbo.parcel.id
)
SELECT
HN.Chute,
HN.Row,
HN.destination_NM,
HN.SortingType,
count(HN.NbrColisNM)
FROM HN
GROUP BY
HN.Chute,
HN.Row,
HN.destination_NM,
HN.SortingType
ORDER BY
HN.Chute |
Partager