SQL script question

abarefoot
abarefoot used Ask the Experts™
on
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
 2.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Information Services Manager
Commented:
Try this...

select order_no
, item_id
, CASE WHEN (DATEDIFF(dd, dateadd(day,1, requested_date), ship_date)) <= 0 THEN '1' END AS item_on_time
, CASE WHEN order_no IN(SELECT order_num FROM sales_history WHERE (DATEDIFF(dd, dateadd(day,1, requested_date), ship_date)) > 0) THEN '1' END as order_on_time
from sales_history

GROUP BY order_no, item_id, requested_date, ship_date

Open in new window

SharathData Engineer

Commented:
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?
Ryan ChongSoftware Team Lead

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial