>Except that grouping by OPS_ZIP would not accomplish the same thing. Multiple
>zip codes map to the same REGION_ID and the zip codes might not be contiguous.
>
>Guess I'm going to have to rethink this.
What about
with tmp(PB_LOAD_TYPE, APPT_DATE, REGION_ID, PICKUP, DELIVERY) as
(SELECT PB_LOAD_TYPE,
CAST(COALESCE(OPS_ONBOARD_TIME, OPS_DELIVERED_TIME, OPS_APPT_LOW,
'TODAY') AS DATE),
COALESCE((SELECT FIRST 1 CTRR_REGION_ID
FROM CT_REGION_RANGES
WHERE OPS_ZIP BETWEEN CTRR_LOW_ZIP AND CTRR_HIGH_ZIP
ORDER BY CTRR_REGION_ID), 'N/A'),
IIF(OPS_TYPE = 'P', 1, 0),
IIF(OPS_TYPE = 'P', 0, 1)
FROM OPS_STOP_REC
LEFT JOIN OPS_HEADER ON PB_ID = OPS_ORDER_ID
WHERE PB_TYPE = 'O' AND
PB_DT_ENT >= :PB_DT_ENT)
SELECT PB_LOAD_TYPE, APPT_DATE, REGION_ID, sum(PICKUP) as PICKUPS,
sum(DELIVERY) as DELIVERIES
FROM tmp
GROUP BY PB_LOAD_TYPE, APPT_DATE, REGION_ID
ORDER BY APPT_DATE, PB_LOAD_TYPE, REGION_ID
HTH,
Set
