Welcome
Click Update Data to upload your CSV export.
Click Update Data to upload your CSV export.
Upload each CSV. Files are saved with timestamps for historical lookback.
Add sending site codes that are non-sortable. These will be separated from sortable arrivals in all views.
Create subscriptions to publish execution data to Slack channels on each hourly refresh.
Loading subscriptions...
WITH actuals_detail AS (
-- Inbound Total (100004, 100032, 100360, 100359)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'transin' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('PHL1', 'SAH1', 'SAX1', 'SAX2', 'SAX3', 'SAX5', 'SAX6', 'SAX7', 'SAZ1', 'SAZ2', 'SAZ3', 'SBW1', 'SCA2', 'SCA3', 'SCA4', 'SCA5', 'SCA7', 'SCA9', 'SCO1', 'SCO2', 'SDC1', 'SFL1', 'SFL2', 'SFL3', 'SFL4', 'SFL6', 'SFL7', 'SFL8', 'SGA1', 'SGA2', 'SIA2', 'SID1', 'SIL1', 'SIL2', 'SIL3', 'SIL4', 'SIN9', 'SJA1', 'SKY2', 'SMA1', 'SMA2', 'SMD1', 'SMI1', 'SMN1', 'SMO1', 'SMO2', 'SNC2', 'SNC3', 'SNC6', 'SNE1', 'SNJ1', 'SNJ2', 'SNJ3', 'SNL1', 'SNV1', 'SNV4', 'SNY1', 'SNY2', 'SNY5', 'SOH1', 'SOH2', 'SOH3', 'SOK1', 'SOR3', 'SPA1', 'SPA4', 'SPA5', 'SRH2', 'SRO1', 'SSC4', 'SSP2', 'SSY1', 'STN1', 'STW1', 'STX2', 'STX3', 'STX4', 'STX5', 'STX6', 'STX7', 'STX8', 'STX9', 'SUT1', 'SUT2', 'SVA2', 'SVA4', 'SWA1', 'SWA2', 'SWA4', 'SWF4', 'SWI1', 'SYS3')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id IN (100004, 100032, 100360, 100359)
GROUP BY 1, 2, 3, 4
UNION ALL
-- Non-Sort Actuals (100032 only)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'nonsort' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('PHL1', 'SAH1', 'SAX1', 'SAX2', 'SAX3', 'SAX5', 'SAX6', 'SAX7', 'SAZ1', 'SAZ2', 'SAZ3', 'SBW1', 'SCA2', 'SCA3', 'SCA4', 'SCA5', 'SCA7', 'SCA9', 'SCO1', 'SCO2', 'SDC1', 'SFL1', 'SFL2', 'SFL3', 'SFL4', 'SFL6', 'SFL7', 'SFL8', 'SGA1', 'SGA2', 'SIA2', 'SID1', 'SIL1', 'SIL2', 'SIL3', 'SIL4', 'SIN9', 'SJA1', 'SKY2', 'SMA1', 'SMA2', 'SMD1', 'SMI1', 'SMN1', 'SMO1', 'SMO2', 'SNC2', 'SNC3', 'SNC6', 'SNE1', 'SNJ1', 'SNJ2', 'SNJ3', 'SNL1', 'SNV1', 'SNV4', 'SNY1', 'SNY2', 'SNY5', 'SOH1', 'SOH2', 'SOH3', 'SOK1', 'SOR3', 'SPA1', 'SPA4', 'SPA5', 'SRH2', 'SRO1', 'SSC4', 'SSP2', 'SSY1', 'STN1', 'STW1', 'STX2', 'STX3', 'STX4', 'STX5', 'STX6', 'STX7', 'STX8', 'STX9', 'SUT1', 'SUT2', 'SVA2', 'SVA4', 'SWA1', 'SWA2', 'SWA4', 'SWF4', 'SWI1', 'SYS3')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id = 100032
GROUP BY 1, 2, 3, 4
UNION ALL
-- Ship/Outbound (100131 only)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'outbound sd' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('PHL1', 'SAH1', 'SAX1', 'SAX2', 'SAX3', 'SAX5', 'SAX6', 'SAX7', 'SAZ1', 'SAZ2', 'SAZ3', 'SBW1', 'SCA2', 'SCA3', 'SCA4', 'SCA5', 'SCA7', 'SCA9', 'SCO1', 'SCO2', 'SDC1', 'SFL1', 'SFL2', 'SFL3', 'SFL4', 'SFL6', 'SFL7', 'SFL8', 'SGA1', 'SGA2', 'SIA2', 'SID1', 'SIL1', 'SIL2', 'SIL3', 'SIL4', 'SIN9', 'SJA1', 'SKY2', 'SMA1', 'SMA2', 'SMD1', 'SMI1', 'SMN1', 'SMO1', 'SMO2', 'SNC2', 'SNC3', 'SNC6', 'SNE1', 'SNJ1', 'SNJ2', 'SNJ3', 'SNL1', 'SNV1', 'SNV4', 'SNY1', 'SNY2', 'SNY5', 'SOH1', 'SOH2', 'SOH3', 'SOK1', 'SOR3', 'SPA1', 'SPA4', 'SPA5', 'SRH2', 'SRO1', 'SSC4', 'SSP2', 'SSY1', 'STN1', 'STW1', 'STX2', 'STX3', 'STX4', 'STX5', 'STX6', 'STX7', 'STX8', 'STX9', 'SUT1', 'SUT2', 'SVA2', 'SVA4', 'SWA1', 'SWA2', 'SWA4', 'SWF4', 'SWI1', 'SYS3')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id = 100131
GROUP BY 1, 2, 3, 4
UNION ALL
-- ICQA Total (100017, 100026, 100112)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'icqa count' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('PHL1', 'SAH1', 'SAX1', 'SAX2', 'SAX3', 'SAX5', 'SAX6', 'SAX7', 'SAZ1', 'SAZ2', 'SAZ3', 'SBW1', 'SCA2', 'SCA3', 'SCA4', 'SCA5', 'SCA7', 'SCA9', 'SCO1', 'SCO2', 'SDC1', 'SFL1', 'SFL2', 'SFL3', 'SFL4', 'SFL6', 'SFL7', 'SFL8', 'SGA1', 'SGA2', 'SIA2', 'SID1', 'SIL1', 'SIL2', 'SIL3', 'SIL4', 'SIN9', 'SJA1', 'SKY2', 'SMA1', 'SMA2', 'SMD1', 'SMI1', 'SMN1', 'SMO1', 'SMO2', 'SNC2', 'SNC3', 'SNC6', 'SNE1', 'SNJ1', 'SNJ2', 'SNJ3', 'SNL1', 'SNV1', 'SNV4', 'SNY1', 'SNY2', 'SNY5', 'SOH1', 'SOH2', 'SOH3', 'SOK1', 'SOR3', 'SPA1', 'SPA4', 'SPA5', 'SRH2', 'SRO1', 'SSC4', 'SSP2', 'SSY1', 'STN1', 'STW1', 'STX2', 'STX3', 'STX4', 'STX5', 'STX6', 'STX7', 'STX8', 'STX9', 'SUT1', 'SUT2', 'SVA2', 'SVA4', 'SWA1', 'SWA2', 'SWA4', 'SWF4', 'SWI1', 'SYS3')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id IN (100017, 100026, 100112)
GROUP BY 1, 2, 3, 4
UNION ALL
-- Sort Total (from regular sites - process_id 100021)
SELECT
i.warehouse_id as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'sortation' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('PHL1', 'SAH1', 'SAX1', 'SAX2', 'SAX3', 'SAX5', 'SAX6', 'SAX7', 'SAZ1', 'SAZ2', 'SAZ3', 'SBW1', 'SCA2', 'SCA3', 'SCA4', 'SCA5', 'SCA7', 'SCA9', 'SCO1', 'SCO2', 'SDC1', 'SFL1', 'SFL2', 'SFL3', 'SFL4', 'SFL6', 'SFL7', 'SFL8', 'SGA1', 'SGA2', 'SIA2', 'SID1', 'SIL1', 'SIL2', 'SIL3', 'SIL4', 'SIN9', 'SJA1', 'SKY2', 'SMA1', 'SMA2', 'SMD1', 'SMI1', 'SMN1', 'SMO1', 'SMO2', 'SNC2', 'SNC3', 'SNC6', 'SNE1', 'SNJ1', 'SNJ2', 'SNJ3', 'SNL1', 'SNV1', 'SNV4', 'SNY1', 'SNY2', 'SNY5', 'SOH1', 'SOH2', 'SOH3', 'SOK1', 'SOR3', 'SPA1', 'SPA4', 'SPA5', 'SRH2', 'SRO1', 'SSC4', 'SSP2', 'SSY1', 'STN1', 'STW1', 'STX2', 'STX3', 'STX4', 'STX5', 'STX6', 'STX7', 'STX8', 'STX9', 'SUT1', 'SUT2', 'SVA2', 'SVA4', 'SWA1', 'SWA2', 'SWA4', 'SWF4', 'SWI1', 'SYS3')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id = 100021
GROUP BY 1, 2, 3, 4
UNION ALL
-- Sort Total (from V-sites mapped to regular sites)
SELECT
CASE
WHEN i.warehouse_id = 'VHL1' THEN 'PHL1'
WHEN i.warehouse_id = 'VAH1' THEN 'SAH1'
WHEN i.warehouse_id = 'VAX1' THEN 'SAX1'
WHEN i.warehouse_id = 'VAX2' THEN 'SAX2'
WHEN i.warehouse_id = 'VAX3' THEN 'SAX3'
WHEN i.warehouse_id = 'VAX5' THEN 'SAX5'
WHEN i.warehouse_id = 'VAX6' THEN 'SAX6'
WHEN i.warehouse_id = 'VAX7' THEN 'SAX7'
WHEN i.warehouse_id = 'VAZ1' THEN 'SAZ1'
WHEN i.warehouse_id = 'VAZ2' THEN 'SAZ2'
WHEN i.warehouse_id = 'VAZ3' THEN 'SAZ3'
WHEN i.warehouse_id = 'VBW1' THEN 'SBW1'
WHEN i.warehouse_id = 'VCA2' THEN 'SCA2'
WHEN i.warehouse_id = 'VCA3' THEN 'SCA3'
WHEN i.warehouse_id = 'VCA4' THEN 'SCA4'
WHEN i.warehouse_id = 'VCA5' THEN 'SCA5'
WHEN i.warehouse_id = 'VCA7' THEN 'SCA7'
WHEN i.warehouse_id = 'VCA9' THEN 'SCA9'
WHEN i.warehouse_id = 'VCO1' THEN 'SCO1'
WHEN i.warehouse_id = 'VCO2' THEN 'SCO2'
WHEN i.warehouse_id = 'VDC1' THEN 'SDC1'
WHEN i.warehouse_id = 'VFL1' THEN 'SFL1'
WHEN i.warehouse_id = 'VFL2' THEN 'SFL2'
WHEN i.warehouse_id = 'VFL3' THEN 'SFL3'
WHEN i.warehouse_id = 'VFL4' THEN 'SFL4'
WHEN i.warehouse_id = 'VFL6' THEN 'SFL6'
WHEN i.warehouse_id = 'VFL7' THEN 'SFL7'
WHEN i.warehouse_id = 'VFL8' THEN 'SFL8'
WHEN i.warehouse_id = 'VGA1' THEN 'SGA1'
WHEN i.warehouse_id = 'VGA2' THEN 'SGA2'
WHEN i.warehouse_id = 'VIA2' THEN 'SIA2'
WHEN i.warehouse_id = 'VID1' THEN 'SID1'
WHEN i.warehouse_id = 'VIL1' THEN 'SIL1'
WHEN i.warehouse_id = 'VIL2' THEN 'SIL2'
WHEN i.warehouse_id = 'VIL3' THEN 'SIL3'
WHEN i.warehouse_id = 'VIL4' THEN 'SIL4'
WHEN i.warehouse_id = 'VIN9' THEN 'SIN9'
WHEN i.warehouse_id = 'VJA1' THEN 'SJA1'
WHEN i.warehouse_id = 'VKY2' THEN 'SKY2'
WHEN i.warehouse_id = 'VMA1' THEN 'SMA1'
WHEN i.warehouse_id = 'VMA2' THEN 'SMA2'
WHEN i.warehouse_id = 'VMD1' THEN 'SMD1'
WHEN i.warehouse_id = 'VMI1' THEN 'SMI1'
WHEN i.warehouse_id = 'VMN1' THEN 'SMN1'
WHEN i.warehouse_id = 'VMO1' THEN 'SMO1'
WHEN i.warehouse_id = 'VMO2' THEN 'SMO2'
WHEN i.warehouse_id = 'VNC2' THEN 'SNC2'
WHEN i.warehouse_id = 'VNC3' THEN 'SNC3'
WHEN i.warehouse_id = 'VNC6' THEN 'SNC6'
WHEN i.warehouse_id = 'VNE1' THEN 'SNE1'
WHEN i.warehouse_id = 'VNJ1' THEN 'SNJ1'
WHEN i.warehouse_id = 'VNJ2' THEN 'SNJ2'
WHEN i.warehouse_id = 'VNJ3' THEN 'SNJ3'
WHEN i.warehouse_id = 'VNL1' THEN 'SNL1'
WHEN i.warehouse_id = 'VNV1' THEN 'SNV1'
WHEN i.warehouse_id = 'VNV4' THEN 'SNV4'
WHEN i.warehouse_id = 'VNY1' THEN 'SNY1'
WHEN i.warehouse_id = 'VNY2' THEN 'SNY2'
WHEN i.warehouse_id = 'VNY5' THEN 'SNY5'
WHEN i.warehouse_id = 'VOH1' THEN 'SOH1'
WHEN i.warehouse_id = 'VOH2' THEN 'SOH2'
WHEN i.warehouse_id = 'VOH3' THEN 'SOH3'
WHEN i.warehouse_id = 'VOK1' THEN 'SOK1'
WHEN i.warehouse_id = 'VOR3' THEN 'SOR3'
WHEN i.warehouse_id = 'VPA1' THEN 'SPA1'
WHEN i.warehouse_id = 'VPA4' THEN 'SPA4'
WHEN i.warehouse_id = 'VPA5' THEN 'SPA5'
WHEN i.warehouse_id = 'VRH2' THEN 'SRH2'
WHEN i.warehouse_id = 'VRO1' THEN 'SRO1'
WHEN i.warehouse_id = 'VSC4' THEN 'SSC4'
WHEN i.warehouse_id = 'VSP2' THEN 'SSP2'
WHEN i.warehouse_id = 'VSY1' THEN 'SSY1'
WHEN i.warehouse_id = 'VTN1' THEN 'STN1'
WHEN i.warehouse_id = 'VTW1' THEN 'STW1'
WHEN i.warehouse_id = 'VTX2' THEN 'STX2'
WHEN i.warehouse_id = 'VTX3' THEN 'STX3'
WHEN i.warehouse_id = 'VTX4' THEN 'STX4'
WHEN i.warehouse_id = 'VTX5' THEN 'STX5'
WHEN i.warehouse_id = 'VTX6' THEN 'STX6'
WHEN i.warehouse_id = 'VTX7' THEN 'STX7'
WHEN i.warehouse_id = 'VTX8' THEN 'STX8'
WHEN i.warehouse_id = 'VTX9' THEN 'STX9'
WHEN i.warehouse_id = 'VUT1' THEN 'SUT1'
WHEN i.warehouse_id = 'VUT2' THEN 'SUT2'
WHEN i.warehouse_id = 'VVA2' THEN 'SVA2'
WHEN i.warehouse_id = 'VVA4' THEN 'SVA4'
WHEN i.warehouse_id = 'VWA1' THEN 'SWA1'
WHEN i.warehouse_id = 'VWA2' THEN 'SWA2'
WHEN i.warehouse_id = 'VWA4' THEN 'SWA4'
WHEN i.warehouse_id = 'VWF4' THEN 'SWF4'
WHEN i.warehouse_id = 'VWI1' THEN 'SWI1'
WHEN i.warehouse_id = 'VYS3' THEN 'SYS3'
ELSE i.warehouse_id
END as site,
CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)::date as date,
CASE
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM CONVERT_TIMEZONE('UTC', 'America/New_York', i.start_timestamp)) >= 6 THEN 'Lunch'
ELSE 'Brunch'
END as window,
'sortation' as department,
SUM(i.unit_count) as total_units,
SUM(i.each_count) as total_eaches,
SUM(i.time_millis) / 3600.0 as total_hours
FROM "andes"."scar_ssd_staging"."scar_latis_ppa_intra_day" i
WHERE i.warehouse_id IN ('VHL1', 'VAH1', 'VAX1', 'VAX2', 'VAX3', 'VAX5', 'VAX6', 'VAX7', 'VAZ1', 'VAZ2', 'VAZ3', 'VBW1', 'VCA2', 'VCA3', 'VCA4', 'VCA5', 'VCA7', 'VCA9', 'VCO1', 'VCO2', 'VDC1', 'VFL1', 'VFL2', 'VFL3', 'VFL4', 'VFL6', 'VFL7', 'VFL8', 'VGA1', 'VGA2', 'VIA2', 'VID1', 'VIL1', 'VIL2', 'VIL3', 'VIL4', 'VIN9', 'VJA1', 'VKY2', 'VMA1', 'VMA2', 'VMD1', 'VMI1', 'VMN1', 'VMO1', 'VMO2', 'VNC2', 'VNC3', 'VNC6', 'VNE1', 'VNJ1', 'VNJ2', 'VNJ3', 'VNL1', 'VNV1', 'VNV4', 'VNY1', 'VNY2', 'VNY5', 'VOH1', 'VOH2', 'VOH3', 'VOK1', 'VOR3', 'VPA1', 'VPA4', 'VPA5', 'VRH2', 'VRO1', 'VSC4', 'VSP2', 'VSY1', 'VTN1', 'VTW1', 'VTX2', 'VTX3', 'VTX4', 'VTX5', 'VTX6', 'VTX7', 'VTX8', 'VTX9', 'VUT1', 'VUT2', 'VVA2', 'VVA4', 'VWA1', 'VWA2', 'VWA4', 'VWF4', 'VWI1', 'VYS3')
AND i.snapshot_date >= CURRENT_DATE - 7
AND i.process_id = 100021
GROUP BY 1, 2, 3, 4
),
actuals AS (
SELECT
site, date, window,
SUM(CASE WHEN department = 'transin' THEN total_units END) as ib_units,
SUM(CASE WHEN department = 'transin' THEN total_eaches END) as ib_eaches,
SUM(CASE WHEN department = 'transin' THEN total_hours END) as ib_hours,
SUM(CASE WHEN department = 'nonsort' THEN total_units END) as nonsort_units,
SUM(CASE WHEN department = 'nonsort' THEN total_eaches END) as nonsort_eaches,
SUM(CASE WHEN department = 'nonsort' THEN total_hours END) as nonsort_hours,
SUM(CASE WHEN department = 'outbound sd' THEN total_units END) as ob_units,
SUM(CASE WHEN department = 'outbound sd' THEN total_eaches END) as ob_eaches,
SUM(CASE WHEN department = 'outbound sd' THEN total_hours END) as ob_hours,
SUM(CASE WHEN department = 'sortation' THEN total_units END) as sort_units,
SUM(CASE WHEN department = 'sortation' THEN total_eaches END) as sort_eaches,
SUM(CASE WHEN department = 'sortation' THEN total_hours END) as sort_hours,
SUM(CASE WHEN department = 'icqa count' THEN total_units END) as icqa_units,
SUM(CASE WHEN department = 'icqa count' THEN total_eaches END) as icqa_eaches,
SUM(CASE WHEN department = 'icqa count' THEN total_hours END) as icqa_hours
FROM actuals_detail
GROUP BY 1, 2, 3
),
planning AS (
SELECT
CASE
WHEN lp_id = -15000031 THEN 'PHL1'
WHEN lp_id = -295234637601 THEN 'SAH1'
WHEN lp_id = -15000504 THEN 'SAX1'
WHEN lp_id = -15000159 THEN 'SAX2'
WHEN lp_id = -15000184 THEN 'SAX3'
WHEN lp_id = -15000279 THEN 'SAX5'
WHEN lp_id = -295234639103 THEN 'SAX6'
WHEN lp_id = -295234621070 THEN 'SAX7'
WHEN lp_id = -15000000 THEN 'SAZ1'
WHEN lp_id = -15000507 THEN 'SAZ2'
WHEN lp_id = -295234621393 THEN 'SAZ3'
WHEN lp_id = -28004028 THEN 'SBW1'
WHEN lp_id = -15000295 THEN 'SCA2'
WHEN lp_id = -15000185 THEN 'SCA3'
WHEN lp_id = -15000111 THEN 'SCA4'
WHEN lp_id = -15000160 THEN 'SCA5'
WHEN lp_id = -15000506 THEN 'SCA7'
WHEN lp_id = -295234658385 THEN 'SCA9'
WHEN lp_id = -15000116 THEN 'SCO1'
WHEN lp_id = -295234645369 THEN 'SCO2'
WHEN lp_id = -15000004 THEN 'SDC1'
WHEN lp_id = -15000001 THEN 'SFL1'
WHEN lp_id = -15000161 THEN 'SFL2'
WHEN lp_id = -15000162 THEN 'SFL3'
WHEN lp_id = -15000163 THEN 'SFL4'
WHEN lp_id = -15000280 THEN 'SFL6'
WHEN lp_id = -15000281 THEN 'SFL7'
WHEN lp_id = -15000282 THEN 'SFL8'
WHEN lp_id = -15000147 THEN 'SGA1'
WHEN lp_id = -15000164 THEN 'SGA2'
WHEN lp_id = -295234633814 THEN 'SIA2'
WHEN lp_id = -295234621425 THEN 'SID1'
WHEN lp_id = -15000007 THEN 'SIL1'
WHEN lp_id = -15000117 THEN 'SIL2'
WHEN lp_id = -15000118 THEN 'SIL3'
WHEN lp_id = -15000500 THEN 'SIL4'
WHEN lp_id = -15000165 THEN 'SIN9'
WHEN lp_id = -295234645812 THEN 'SJA1'
WHEN lp_id = -295234637505 THEN 'SKY2'
WHEN lp_id = -15000008 THEN 'SMA1'
WHEN lp_id = -15000186 THEN 'SMA2'
WHEN lp_id = -15000107 THEN 'SMD1'
WHEN lp_id = -15000119 THEN 'SMI1'
WHEN lp_id = -15000166 THEN 'SMN1'
WHEN lp_id = -15000505 THEN 'SMO1'
WHEN lp_id = -15000187 THEN 'SMO2'
WHEN lp_id = -15000167 THEN 'SNC2'
WHEN lp_id = -15000188 THEN 'SNC3'
WHEN lp_id = -295234636159 THEN 'SNC6'
WHEN lp_id = -295234634612 THEN 'SNE1'
WHEN lp_id = -15000257 THEN 'SNJ1'
WHEN lp_id = -28002897 THEN 'SNJ2'
WHEN lp_id = -15000189 THEN 'SNJ3'
WHEN lp_id = -295234631494 THEN 'SNL1'
WHEN lp_id = -15000168 THEN 'SNV1'
WHEN lp_id = -295234658499 THEN 'SNV4'
WHEN lp_id = -15000190 THEN 'SNY1'
WHEN lp_id = -15000284 THEN 'SNY2'
WHEN lp_id = -15000191 THEN 'SNY5'
WHEN lp_id = -15000148 THEN 'SOH1'
WHEN lp_id = -15000149 THEN 'SOH2'
WHEN lp_id = -15000192 THEN 'SOH3'
WHEN lp_id = -295234621457 THEN 'SOK1'
WHEN lp_id = -15000169 THEN 'SOR3'
WHEN lp_id = -295234621102 THEN 'SPA1'
WHEN lp_id = -295234643342 THEN 'SPA4'
WHEN lp_id = -295234656514 THEN 'SPA5'
WHEN lp_id = -295234658289 THEN 'SRH2'
WHEN lp_id = -295234646832 THEN 'SRO1'
WHEN lp_id = -295234641555 THEN 'SSC4'
WHEN lp_id = -295234644204 THEN 'SSP2'
WHEN lp_id = -295234643797 THEN 'SSY1'
WHEN lp_id = -15000005 THEN 'STN1'
WHEN lp_id = -295234629188 THEN 'STW1'
WHEN lp_id = -15000006 THEN 'STX2'
WHEN lp_id = -15000113 THEN 'STX3'
WHEN lp_id = -15000170 THEN 'STX4'
WHEN lp_id = -15000171 THEN 'STX5'
WHEN lp_id = -15000172 THEN 'STX6'
WHEN lp_id = -15000138 THEN 'STX7'
WHEN lp_id = -15000285 THEN 'STX8'
WHEN lp_id = -15000286 THEN 'STX9'
WHEN lp_id = -15000173 THEN 'SUT1'
WHEN lp_id = -295234637473 THEN 'SUT2'
WHEN lp_id = -295234621489 THEN 'SVA2'
WHEN lp_id = -295234659124 THEN 'SVA4'
WHEN lp_id = -15000009 THEN 'SWA1'
WHEN lp_id = -15000061 THEN 'SWA2'
WHEN lp_id = -295234656596 THEN 'SWA4'
WHEN lp_id = -295234646864 THEN 'SWF4'
WHEN lp_id = -15000174 THEN 'SWI1'
WHEN lp_id = -295234637537 THEN 'SYS3'
ELSE 'Unknown'
END as site,
DATE(operations_date) as date,
CASE
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 18 THEN 'Breakfast'
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 14 THEN 'Bedtime'
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 11 THEN 'Dinner'
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 6 THEN 'Lunch'
WHEN EXTRACT(HOUR FROM start_datetime_local) >= 0 THEN 'Brunch'
END as window,
SUM(CASE WHEN process_path = 'transin' THEN volume_original_value END) as ib_vol_orig,
SUM(CASE WHEN process_path = 'transin' THEN volume_override_value END) as ib_vol_override,
SUM(CASE WHEN process_path = 'transin' THEN labor_hours_original_value END) as ib_hrs_orig,
SUM(CASE WHEN process_path = 'transin' THEN labor_hours_override_value END) as ib_hrs_override,
SUM(CASE WHEN process_path = 'outbound sd' THEN volume_original_value END) as ob_vol_orig,
SUM(CASE WHEN process_path = 'outbound sd' THEN volume_override_value END) as ob_vol_override,
SUM(CASE WHEN process_path = 'outbound sd' THEN labor_hours_original_value END) as ob_hrs_orig,
SUM(CASE WHEN process_path = 'outbound sd' THEN labor_hours_override_value END) as ob_hrs_override,
SUM(CASE WHEN process_path = 'sortation' THEN volume_original_value END) as sort_vol_orig,
SUM(CASE WHEN process_path = 'sortation' THEN volume_override_value END) as sort_vol_override,
SUM(CASE WHEN process_path = 'sortation' THEN labor_hours_original_value END) as sort_hrs_orig,
SUM(CASE WHEN process_path = 'sortation' THEN labor_hours_override_value END) as sort_hrs_override,
SUM(CASE WHEN process_path = 'icqa count' THEN volume_original_value END) as icqa_vol_orig,
SUM(CASE WHEN process_path = 'icqa count' THEN volume_override_value END) as icqa_vol_override,
SUM(CASE WHEN process_path = 'icqa count' THEN labor_hours_original_value END) as icqa_hrs_orig,
SUM(CASE WHEN process_path = 'icqa count' THEN labor_hours_override_value END) as icqa_hrs_override
FROM "andes"."GSF-Central"."labor_demand_outbound_aggregate"
WHERE operations_date >= CURRENT_DATE - 7
AND operations_date <= CURRENT_DATE + 7
AND lp_id IN (-15000031, -295234637601, -15000504, -15000159, -15000184, -15000279, -295234639103, -295234621070, -15000000, -15000507, -295234621393, -28004028, -15000295, -15000185, -15000111, -15000160, -15000506, -295234658385, -15000116, -295234645369, -15000004, -15000001, -15000161, -15000162, -15000163, -15000280, -15000281, -15000282, -15000147, -15000164, -295234633814, -295234621425, -15000007, -15000117, -15000118, -15000500, -15000165, -295234645812, -295234637505, -15000008, -15000186, -15000107, -15000119, -15000166, -15000505, -15000187, -15000167, -15000188, -295234636159, -295234634612, -15000257, -28002897, -15000189, -295234631494, -15000168, -295234658499, -15000190, -15000284, -15000191, -15000148, -15000149, -15000192, -295234621457, -15000169, -295234621102, -295234643342, -295234656514, -295234658289, -295234646832, -295234641555, -295234644204, -295234643797, -15000005, -295234629188, -15000006, -15000113, -15000170, -15000171, -15000172, -15000138, -15000285, -15000286, -15000173, -295234637473, -295234621489, -295234659124, -15000009, -15000061, -295234656596, -295234646864, -15000174, -295234637537)
GROUP BY 1, 2, 3
)
SELECT
COALESCE(a.site, p.site) as site,
COALESCE(a.date, p.date) as date,
COALESCE(a.window, p.window) as window,
a.ib_units as ib_actual_units,
a.ib_eaches as ib_actual_eaches,
a.ib_hours as ib_actual_hours,
p.ib_vol_orig as ib_plan_volume_original,
p.ib_vol_override as ib_plan_volume_override,
p.ib_hrs_orig as ib_plan_hours_original,
p.ib_hrs_override as ib_plan_hours_override,
a.ob_units as ob_actual_units,
a.ob_eaches as ob_actual_eaches,
a.ob_hours as ob_actual_hours,
p.ob_vol_orig as ob_plan_volume_original,
p.ob_vol_override as ob_plan_volume_override,
p.ob_hrs_orig as ob_plan_hours_original,
p.ob_hrs_override as ob_plan_hours_override,
a.sort_units as sort_actual_units,
a.sort_eaches as sort_actual_eaches,
a.sort_hours as sort_actual_hours,
p.sort_vol_orig as sort_plan_volume_original,
p.sort_vol_override as sort_plan_volume_override,
p.sort_hrs_orig as sort_plan_hours_original,
p.sort_hrs_override as sort_plan_hours_override,
a.icqa_units as icqa_actual_units,
a.icqa_eaches as icqa_actual_eaches,
a.icqa_hours as icqa_actual_hours,
p.icqa_vol_orig as icqa_plan_volume_original,
p.icqa_vol_override as icqa_plan_volume_override,
p.icqa_hrs_orig as icqa_plan_hours_original,
p.icqa_hrs_override as icqa_plan_hours_override,
a.nonsort_units as nonsort_actual_units,
a.nonsort_eaches as nonsort_actual_eaches,
a.nonsort_hours as nonsort_actual_hours
FROM actuals a
FULL OUTER JOIN planning p
ON a.site = p.site
AND a.date = p.date
AND a.window = p.window
ORDER BY site, date, window
;