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
 2.png
LVL 1
abarefootAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ron MalmsteadInformation Services ManagerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
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 LeadCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.