We help IT Professionals succeed at work.

SQL script question

122 Views
Last Modified: 2018-10-22
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

Information Services Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sharath SData Engineer
CERTIFIED EXPERT

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?
CERTIFIED EXPERT

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

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.