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

Yassir 201 Reputation points
2023-11-23T08:29:29.8033333+00:00

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 :

User's image

Any idea how can i do that ?

Thanks for help !

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,267 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHongMSFT-4306 25,571 Reputation points
    2023-11-24T07:20:00.6533333+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Viorel 114.4K Reputation points
    2023-11-23T09:41:40.82+00:00

    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')