Need help on oracle sql for getting a subset of 'Dropped' data from 1500 hours yesterday to 2400 hours today.
The superset will be 'intransit' from 1500 hours yesterday to 1500 hours today
my query is only giving 'dropped' from 1500 hours yesterday to 1500 hours today
with A99 as
(
SELECT shipment_id,
holder_employee_id
FROM (
SELECT distinct shipment_id,
holder_employee_id,
shipment_status_id,
row_number()
OVER (
PARTITION BY shipment_id ORDER BY CHANGE_DATE DESC
) AS rn1
FROM db.table1
where (
(TRUNC(CHANGE_DATE) = to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD')-1 AND TO_CHAR(CHANGE_DATE,'HH24') >=15)
or
(TRUNC(CHANGE_DATE) = to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD')
AND TO_CHAR(CHANGE_DATE,'HH24') <=15)
)
and region_id=4
)
WHERE rn1 = 1 AND shipment_status_id in ('intransit')
)
select distinct cs.tracking_id,
ce.store_id,
ce.store_name,
cst.station_code,
case
when cssh.shipment_status_id in ('dropped')
and TRUNC(cssh.CHANGE_DATE) >= to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD')-1
then 0
else 1
end as DAS_STATUS
from A99
inner join db.table1 cssh
on cssh.shipment_id=A99.shipment_id
inner join db.table2 cs
on cs.shipment_id=A99.shipment_id
inner join db.table3 ce
on ce.employee_id = A99 .holder_employee_id
inner join db.table4 cst
on cst.station_id= ce.station_id
where TRUNC(cssh.CHANGE_DATE) >= to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD')-1
