Finding out if the Orders have Shipped/Delivered Early or Late

Transact-SQL
1

Hi All,

Hope you are doing well!...I am trying to create an output table to understand if the orders have shipped early,ontime and delivered earlyorontime with regard to the promised ship date and the delivered date.(early and ontime means the same for me)...An order can have multiple trackers (For example different pieces of a furniture are shipped separately and have different trackers)...

Following is the condition that I am trying to understand if the order is shipped or delivered early or late

  1. If any one of the tracker is shipped early for an order( a date which is on or before the promised ship date for that order)..The actual ship is found from the #tracker table and the promised ship date is found from the #promised table..

2)If any one of the tracker is delivered early for an order( a date which is on or before the promised delivery date for that order)..The actual delivered is found from the #tracker table and the promised delivery date is found from the #promised table..

(the shipment early ,the delivery early need not be the same tracker for that order ..They can belong to different trackers for that specific order )

Also I am trying to understand if 50% of the trackers for a specific order have shipped early , delivered early , (Say there are 4 trackers for a specific order -2 of them should have shipped early or on time and 2 of them should have delivered early or on time for shipping and delivery respectively)

Please find below the DDL for the tables with the output table...Can you please help here..(Also I am trying to understand the percentage of the trackers that have shipped or delivered early for every order)..

create table ##trackers
(orderid int,
tracker varchar(50),
actualpickup date NULL,
actualdelivery date NULL,
)

INSERT INTO ##trackers values
('3156','152885249270',NULL,NULL),
('3156','152885249383',NULL,NULL),
('3156','152885249534',NULL,NULL),
('3156','152885249707',NULL,NULL),
('3156','152885250045','2020-01-14','2020-01-17'),
('7899','152885618666',NULL,NULL),
('7899','152886555384','2020-01-21','2020-01-28'),
('7899','152887170510','2020-02-02','2020-02-08'),
('7899','152887560215','2020-01-22','2020-01-27'),
('8014','152888104169','2020-01-05','2020-01-11'),
('8014','152888595117','2020-01-04',NULL),
('8014','152889219559','2020-01-06','2020-01-07'),
('9022','152890069395',NULL,NULL),
('9022','152891023454',NULL,NULL),
('9022','152891023757','2020-01-17','2020-01-24')

create table #promised
(orderid int,
promisedpickup date,
promiseddelivery date)

insert into #promised values
('3156','2020-01-14','2020-01-17'),
('7899','2020-01-22','2020-01-28'),
('8014','2020-01-04','2020-01-06'),
('9022','2020-01-18','2020-01-25')

create table #output
(orderid int,
AtleastonceShippedOntimeorearly varchar(10),
AtleastoncedeliveredOntimeorearly varchar(10),
fiftypercshippedOntimeorearly varchar(10),
fiftypercdeliveredOntimeorearly varchar(10)
)

insert into #output values
('3156','Yes','Yes','No','No'),
('7899','Yes','Yes','Yes','Yes'),
('8014','Yes','No','No','No'),
('9022','No','No','No','No')

Thanks,
Arun

2

Seeking some help here!!..

3

Please try the following and are you sure that for OrderID=9022 the final output is all No:

SELECT *
INTO #TMP
FROM #trackers TR
OUTER APPLY (
SELECT 1
FROM #promised
WHERE orderid=TR.orderid
AND promisedpickup >= TR.actualpickup
)TAB(Col_dlvry)
OUTER APPLY (
SELECT 1
FROM #promised
WHERE orderid=TR.orderid
AND promiseddelivery >= TR.actualdelivery
)TAB2(Col_ship)

;WITH CTE AS
(
SELECT orderid,
MAX(CASE WHEN Col_dlvry = 1 THEN 1 ELSE 0 END) AS AtleastonceShippedOntimeorearly,
MAX(CASE WHEN Col_ship = 1 THEN 1 ELSE 0 END) AS AtleastoncedeliveredOntimeorearly,
MAX(TAB.CNT) AS CNT
FROM #TMP T
CROSS APPLY(SELECT COUNT(Tracker) AS CNT FROM #trackers WHERE orderid=T.ORDERID)TAB
GROUP BY orderid
)

SELECT orderid,
CASE WHEN AtleastonceShippedOntimeorearly=1 THEN 'Yes' ELSE 'No' END AS AtleastonceShippedOntimeorearly,
CASE WHEN AtleastoncedeliveredOntimeorearly=1 THEN 'Yes' ELSE 'No' END AS AtleastoncedeliveredOntimeorearly,
CASE WHEN (TAB.CNT100)/(T.CNT1.0)>=50 THEN 'Yes' ELSE 'No' END AS fiftypercshippedOntimeorearly,
CASE WHEN (TAB2.CNT100)/(T.CNT1.0)>=50 THEN 'Yes' ELSE 'No' END AS fiftypercdeliveredOntimeorearly
FROM CTE T
OUTER APPLY(SELECT COUNT() AS CNT FROM #TMP WHERE orderid=T.ORDERID AND Col_dlvry=1)TAB
OUTER APPLY(SELECT COUNT() AS CNT FROM #TMP WHERE orderid=T.ORDERID AND Col_ship=1)TAB2

DROP TABLE IF EXISTS #TMP

4

@rocknpop:Appreciate your response!...I will get back to you after testing it...

Thanks,
Arun