Link to home
Start Free TrialLog in
Avatar of abarefoot
abarefoot

asked on

SQL script question

I'm needing to find when an order is on time.  The issue is some orders can have items that are considered on time while other items are not.  I need to find when the whole order is on time.  

select order_no, item_id, CASE WHEN (DATEDIFF(dd, dateadd(day,1, requested_date), ship_date)) <= 0 THEN '1' END AS item_on_time
from sales_history
GROUP BY order_no, item_id, requested_date, ship_date

So below is what I'm needing which would be column D
 User generated image
ASKER CERTIFIED SOLUTION
Avatar of Ron Malmstead
Ron Malmstead
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What are the rules for item_on_time and order_on_time?
If the item is requested and shipped on same day, does that mean item_on_time?
If all items in an order requested and shipped on same day, does that mean order_on_time?

order_no=2 and item_id = Grape is requested on 10/3 but shipped on 10/5. How this item on time?
or you can try something like this:

;with cte as
(
	select order_no, item_id, CASE WHEN (DATEDIFF(dd, dateadd(day,1, requested_date), ship_date)) <= 0 THEN '1' END AS item_on_time
	from sales_history
	GROUP BY order_no, item_id, requested_date, ship_date
), cte2 as
(
	select order_no from cte where item_on_time = 0
)
Select distinct a.order_no, a.item_id, a.item_on_time,
case when b.order_no is null then '1' else '0' order_on_time
from cte a
left join cte2 b on a.order_no = b.order_no

Open in new window