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
|
PARAMETERS debut DateTime, fin DateTime;
SELECT tbl.Displayname AS injection, Sum(tbl.c1) AS keyboard, Sum(tbl.c2) AS HHScanner, Sum(tbl.c3) AS Scanner, Sum(tbl.c4) AS Stray, Sum(tbl.c5) AS Fragile, Sum(tbl.c6) AS Cylindrical, Sum(tbl.c7) AS OverSize, Sum(c1)+Sum(c2)+Sum(c3)+Sum(c4)+Sum(c5)+Sum(c6)+Sum(c7) AS TOTAL, tbl.DEBUT AS DEBUT, tbl.FIN AS FIN, ((Sum([c1])+Sum([c2])+Sum([c3])+Sum([c4])+Sum([c5])+Sum([c6])+Sum([c7]))/(DateDiff("n",[DEBUT],[FIN]))*60) AS Colis_Heure
FROM (
SELECT p.Displayname, 0 AS c1, 0 AS c2, 0 AS c3, 0 AS c4, 0 AS c5, Sum(CounterValue) AS c6, 0 as c7, DEBUT, FIN
FROM dbo_vwPartCountsHistory as ch INNER JOIN dbo_vwParts AS p ON ch.PartId = p.ID
WHERE (( (CounterTimeStamp)>=debut) And ( (CounterTimeStamp)<=fin) AND (CounterTypeId=82 ) )
GROUP BY p.DisplayName
UNION
SELECT p.Displayname, 0 AS c1, 0 AS c2, 0 AS c3, 0 AS c4, 0 as c5, 0 AS c6, Sum(CounterValue) as c7, DEBUT, FIN
FROM dbo_vwPartCountsHistory as ch INNER JOIN dbo_vwParts AS p ON ch.PartId = p.ID
WHERE (((CounterTimeStamp)>=debut) And ( (CounterTimeStamp)<=fin) AND (CounterTypeId=83))
GROUP BY p.DisplayName
UNION
SELECT p.DisplayName, COUNT(*) AS c1, 0 AS c2, 0 AS c3, 0 AS c4, 0 AS c5, 0 as c6, 0 as c7, DEBUT, FIN
FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
WHERE ((dbo_vwItemEventHistory.ItemEventTypeID=5) AND (dbo_vwItemEventHistory.EventTime>debut) And (dbo_vwItemEventHistory.EventTime<=fin) AND (id.InductionMode='Keyboard') AND (id.dischargePartId <> 3645) )
GROUP BY p.DisplayName
UNION
SELECT p.DisplayName, 0 AS c1, COUNT(*) AS c2, 0 AS c3, 0 AS c4, 0 AS c5, 0 as c6, 0 as c7, DEBUT, FIN
FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
WHERE ((dbo_vwItemEventHistory.ItemEventTypeID=5) AND (dbo_vwItemEventHistory.EventTime>debut) And (dbo_vwItemEventHistory.EventTime<=fin) AND (id.InductionMode='HHScanner') AND (id.dischargePartId <> 3645) )
GROUP BY p.DisplayName
UNION
SELECT p.DisplayName, 0 AS c1, 0 AS c2, COUNT(*) AS c3, 0 AS c4, 0 AS c5, 0 as c6, 0 as c7, DEBUT, FIN
FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
WHERE ((dbo_vwItemEventHistory.ItemEventTypeID=5) AND (dbo_vwItemEventHistory.EventTime>debut) And (dbo_vwItemEventHistory.EventTime<=fin) AND (id.InductionMode='Scanner') AND (id.dischargePartId <> 3645) )
GROUP BY p.DisplayName
UNION
SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 AS c3,COUNT(*) AS c4, 0 AS c5, 0 as c6, 0 as c7, DEBUT, FIN
FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
WHERE ((dbo_vwItemEventHistory.ItemEventTypeID=5) AND (dbo_vwItemEventHistory.EventTime>debut) And (dbo_vwItemEventHistory.EventTime<=fin) AND (id.InductionMode='Stray') AND (id.dischargePartId <> 3645) )
GROUP BY p.DisplayName
UNION
SELECT p.DisplayName, 0 AS c1, 0 AS c2, 0 AS c3, 0 AS c4, COUNT(*) AS c5, 0 as c6, 0 as c7, DEBUT, FIN
FROM (dbo_vwItemEventHistory INNER JOIN dbo_vwItemData AS id ON dbo_vwItemEventHistory.ItemID = id.ItemID) INNER JOIN dbo_vwParts AS p ON id.InductionPartID = p.ID
WHERE ((dbo_vwItemEventHistory.ItemEventTypeID=5) AND (dbo_vwItemEventHistory.EventTime>debut) And (dbo_vwItemEventHistory.EventTime<=fin) AND (id.InductionMode='Fragile') AND (id.dischargePartId <> 3645) )
GROUP BY p.DisplayName) AS tbl
GROUP BY tbl.Displayname, tbl.DEBUT, tbl.FIN
ORDER BY tbl.Displayname; |
Partager