On Time Delivery calculation
How can i to write the coding as below expectation:
Conditions:
----------------
a.) Shedule Ship Date - Actual Shipment > 0 Then "Failded OTD"
b.) Shedule Ship Date - Actual Shipment <= 0 Then "Pass OTD OTD"
c.) If actual shipment date is null, then "Not Yet Deliver"
d.) Days variance : Shedule Ship Date - Actual Shipment
SQL Command:
----------------------
SELECT
case when (trunc(schedule_ship_date) - to_date(ool.actual_shipment_date,'DD-MON-YY')) < 0 then 'FAILED'
else 'PASS' end as "OTD"
,trunc(schedule_ship_date) - to_date(ool.actual_shipment_date,'DD-MON-YY') "DAYS VARIANCE"
From oe_order_lines_all ool