T-SQL : Slicing status by related time periods and other status

Question

Hi,

I have a table ShipperStatusHistory that tracks orders, Shipping status and relay points (for customers where to collect packages) . .

I want to calculate the number of package That weren''t collected by customers for each a relay point.

To identify a left package (not collected by customer) the status need to be ("Dropoff" or "preparation") ***OR ***("Dropoff" or "preparation") and after the status becomes "collected" or "shipper".

But if i have other status ("shipper Delivery","Relay point Delivery","pickup","announcement") after "Dropoff" or "preparation" the collecatable packages is canceled.

We have other status like "end of time" or "Relay point Collection" but we can ignore them.

AS Bellow a sample of data with The result should look like

Create table #ShipperStatusHistory( 	id Int primary key identity, 	OrderNumber varchar(50), 	RelayPoint INT, 	Status varchar(50), 	DateShipper Datetime )

  insert into #ShipperStatusHistory                              values('XN75',NULL,'Shipment','2022-01-27 15:29:22.000') 								,('XN75',123,'Dropoff','2022-02-01 07:15:53.000') 								,('XN75',123,'Relay point Collection','2022-02-02 07:50:29.000') 								,('XN75',123,'collected','2022-02-02 07:50:45.000') 								,('XN75',45678,'shipper','2022-02-02 16:02:37.000')                                 ,('XN76',NULL,'Shipment','2022-03-25 20:38:19.000') 								,('XN76',234,'Dropoff','2022-04-01 09:16:51.000') 								,('XN76',234,'Relay point Collection','2022-04-03 10:14:06.000') 								,('XN76',16734,'shipper','2022-04-04 15:05:30.000') 								,('XN76',456,'shipper delivery','2022-04-09 10:42:01.000') 								,('XN76',456,'Relay point Delivery','2022-04-09 10:43:46.000') 								,('XN76',456,'Pickup','2022-04-09 14:08:50.000') 								,('XN77',NULL,'Shipment','2022-09-05 18:44:19.000') 								,('XN77',555,'Dropoff','2022-09-09 08:38:39.000') 								,('XN77',555,'Relay point Collection','2022-09-09 09:33:07.000') 								,('XN77',555,'collected','2022-09-09 09:33:09.000') 								,('XN77',198721,'shipper','2022-09-11 11:17:48.000') 								,('XN77',980,'Relay point Delivery','2022-09-22 09:23:35.000') 								,('XN77',980,'End of time','2022-09-29 23:00:06.000') 								,('XN77',980,'Preparation','2022-10-01 10:48:04.000') 								,('XN77',980,'Pickup','2022-10-11 16:48:30.000')                                   ,('XN78',NULL,'Shipment','2022-09-05 18:44:19.000') 								,('XN78',555,'Dropoff','2022-09-09 08:38:39.000') 								,('XN78',555,'Relay point Collection','2022-09-09 09:33:07.000') 								,('XN78',555,'collected','2022-09-09 09:33:09.000') 								,('XN78',198721,'shipper','2022-09-11 11:17:48.000') 								,('XN78',980,'Relay point Delivery','2022-09-22 09:23:35.000') 								,('XN78',980,'End of time','2022-09-29 23:00:06.000') 								,('XN78',980,'Preparation','2022-10-01 10:48:04.000')                                   ,('XN79',NULL,'Shipment','2022-09-25 20:38:19.000') 								,('XN79',979,'Dropoff','2022-10-01 09:16:51.000') 								,('XN79',979,'Relay point Collection','2022-10-03 10:14:06.000') 								,('XN79',198271,'shipper','2022-10-04 15:05:30.000') 								,('XN79',154,'shipper Delivery','2022-10-09 10:42:01.000') 								,('XN79',154,'Relay point Delivery','2022-10-09 10:43:46.000') 								,('XN79',154,'Pickup','2022-10-09 14:08:50.000') 								,('XN79',711,'Dropoff','2022-10-10 13:45:59.000') 								,('XN79',108223,'shipper','2022-10-11 13:45:59.000')

The result expected :

Any idea how can i do that ?

Thanks for help !

Answer 1

Hi @Yassir

Try this query:

;WITH CTE1 AS 
( 
 SELECT OrderNumber 
       ,CASE WHEN Status='shipper' THEN LAG(RelayPoint)OVER(PARTITION BY OrderNumber ORDER BY DateShipper) ELSE RelayPoint END AS RelayPoint 
	   ,Status
 	   ,DateShipper
 FROM #ShipperStatusHistory
),CTE2 AS 
( 
 SELECT C1.OrderNumber AS [Order],C1.RelayPoint,C1.Status,C1.DateShipper AS [Date]  
 FROM CTE1 C1 LEFT JOIN #ShipperStatusHistory S2  
   ON C1.OrderNumber=S2.OrderNumber  
  AND C1.RelayPoint=S2.RelayPoint  
  AND S2.Status IN ('shipper Delivery','Relay point Delivery','Pickup','announcement') 
  AND C1.DateShipper < S2.DateShipper 
 WHERE C1.Status IN ('Dropoff','Preparation') AND S2.id IS NULL 
) 
SELECT C.* 
      ,C1.DateShipper AS [Date Collected]
 	  ,C2.DateShipper AS [Date Shipper]
 	  ,COUNT(1)OVER(PARTITION BY C.[Order],C.RelayPoint) AS [Number of package] 
FROM CTE2 C 
LEFT JOIN CTE1 C1 ON C.[Order] = C1.OrderNumber AND C.RelayPoint = C1.RelayPoint AND C1.Status ='collected' 
LEFT JOIN CTE1 C2 ON C.[Order] = C2.OrderNumber AND C.RelayPoint = C2.RelayPoint AND C2.Status ='shipper' 

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Check an intermediate solution:

select h.OrderNumber, 'Dropoff' as [Status], h.DateShipper as [Date], c.DateShipper as DateCollected, s.DateShipper as DateShipper, 1 as [Number of package]
from #ShipperStatusHistory h
outer apply (select top(1) * from #ShipperStatusHistory where OrderNumber = h.OrderNumber and [Status] in ('Collected') and DateShipper > h.DateShipper order by DateShipper) c
outer apply (select top(1) * from #ShipperStatusHistory where OrderNumber = h.OrderNumber and [Status] in ('Shipper') and DateShipper > h.DateShipper order by DateShipper) s
where h.[Status] in ('Dropoff', 'Preparation')