53 lines
1.9 KiB
Plaintext
53 lines
1.9 KiB
Plaintext
/***Select
|
|
SorterName,Lane,Startstamp,Endtstamp,
|
|
Total_count, DestFull_count,
|
|
|
|
Total_count/Total_count as total_perc,
|
|
DestFull_count/Total_count as destfull_perc,
|
|
|
|
Total_count*3600/TIMESTAMPDIFF(second, :starttime, :endtime) as total_rate,
|
|
DestFull_count*3600/TIMESTAMPDIFF(second, :starttime, :endtime) as destfull_rate
|
|
FROM
|
|
(Select
|
|
'S01' AS SorterName,
|
|
alltable.DEST_REQ as Lane,
|
|
COUNT(*) AS Total_count,
|
|
SUM(alltable.DivertStatus = 5) AS DestFull_count,
|
|
MIN(alltable.t_stamp) AS Startstamp,
|
|
Max(alltable.t_stamp) AS Endtstamp
|
|
|
|
FROM alltable
|
|
Where (alltable.DEST_REQ <> 'S0199' AND alltable.t_stamp BETWEEN :starttime AND :endtime)
|
|
Group BY alltable.DEST_REQ
|
|
Union
|
|
Select
|
|
'S01' AS SorterName,
|
|
alltable.ACTUAL_DEST as Lane,
|
|
COUNT(*) AS Total_count,
|
|
0 AS DestFull_count,
|
|
MIN(alltable.t_stamp) AS Startstamp,
|
|
Max(alltable.t_stamp) AS Endtstamp
|
|
FROM alltable
|
|
Where (alltable.ACTUAL_DEST = 'S0199' AND alltable.t_stamp BETWEEN :starttime AND :endtime)) basa ***/
|
|
|
|
/***Select
|
|
SorterName,InductName,Total_count,singlecarrier_count,doublecarrier_count,Startstamp,Endtstamp,
|
|
Total_count/Total_count as Total_perc,
|
|
singlecarrier_count/Total_count as SingleCarrier_perc,
|
|
doublecarrier_count/Total_count as DoubleCarrier_perc,
|
|
Total_count*3600/TIMESTAMPDIFF(second, Startstamp, Endtstamp) as Total_rate,
|
|
singlecarrier_count*3600/TIMESTAMPDIFF(second, Startstamp, Endtstamp) as SingleCarrier_rate,
|
|
doublecarrier_count*3600/TIMESTAMPDIFF(second, Startstamp, Endtstamp) as DoubleCarrier_rate
|
|
FROM
|
|
(SELECT
|
|
'S01' as SorterName,
|
|
'Induct 1' as InductName,
|
|
COUNT(*) AS Total_count,
|
|
SUM(jam_area.ParcelHistory_TupelLength = 1) AS singlecarrier_count,
|
|
SUM(jam_area.ParcelHistory_TupelLength = 2) AS doublecarrier_count,
|
|
MIN(jam_area.t_stamp) AS Startstamp,
|
|
Max(jam_area.t_stamp) AS Endtstamp
|
|
|
|
FROM alltable
|
|
Where (alltable.t_stamp BETWEEN :starttime AND :endtime)
|
|
Group BY 'Induct 1' ) basajam***/ |