T-SQL : Slicing status by related time periods and other status
-
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 lik
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 resolve that problem ?
Thanks for help !
-
Sorry to keep modifying this response. It's taken me a while to formulate a question I am happy with.
In your sample results, we see XN78/980.
The latest row for this combination has a status of 'Preparation' and there is no later status of "collected" or "shipper". Based on your definition, it is therefore not a 'left package'. Please clarify.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Hello @Phil Parkin
Thanks for replying
Like a said in the post
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"
Thats mean if i have at leat Dropoff or preparation and after thant not thing it's consider a left package
Also if i have dropoff or prepation and after that i have collected or shipper or both it's consider too a left package.
Hope it's clear
-
Happy Thanksgiving
Based on the written requirements (as interpreted) this query might be helpful. The comparisons being made across rows are partitioned by (OrderNumber, RelayPoint) and ordered by DateShipper. To identify which events have occurred within the partitioning window it uses MAX and evaluates the [Status] column value to set a 1/0 flag. Based on the 'left_package' column there were only 3 (OrderNumber, RelayPoint) pairs not collected:
XN75 123 collected 2022-02-02 07:50:45.000
XN77 555 collected 2022-09-09 09:33:09.000
XN78 555 collected 2022-09-09 09:33:09.000
with
d_or_p_cte as (
select *, max(iif([Status] in('Dropoff', 'preparation'), 1, 0)) over (partition by OrderNumber, RelayPoint order by DateShipper) d_or_p
from #ShipperStatusHistory)
select *, max(iif(d_or_p=1 and [Status] in('collected', 'shipper'), 1, 0)) over (partition by OrderNumber, RelayPoint order by DateShipper) left_package
from d_or_p_cte;How this fits onto the spreadsheet idk. It's not clear
Also: it uses IN which I don't like especially when the column is nullable. The sample data doesn't contain NULL values in the [Status] column but I added the constraint to the DDL anyway.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
It's an ungainly piece of code, but I think it gets the right results.
SELECT ssh.id
,ssh.OrderNumber
,ssh.RelayPoint
,ssh.Status
,ssh.DateShipper
FROM #ShipperStatusHistory ssh
WHERE ssh.Status IN ( 'Dropoff', 'Preparation' )
AND
(
EXISTS
(
SELECT 1
FROM #ShipperStatusHistory ssh2
WHERE ssh2.OrderNumber = ssh.OrderNumber
AND ssh2.DateShipper > ssh.DateShipper
AND ssh2.Status IN ( 'collected', 'shipper' )
)
OR NOT EXISTS
(
SELECT 1
FROM #ShipperStatusHistory ssh3
WHERE ssh3.OrderNumber = ssh.OrderNumber
AND ssh3.DateShipper > ssh.DateShipper
)
)
ORDER BY ssh.OrderNumber
,ssh.DateShipper;If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
-
Hello @phil-parkin
Thanks for your help.
Your solution resolve the problem with the sample that i give but i executed with other data i got some wrong result
-
Maybe get rid of the MAX OVER and just evaluate the Status column
select *, iif([Status] in('Dropoff', 'preparation'), 1, 0) d_or_p
from #ShipperStatusHistory
order by OrderNumber, DateShipper;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- MrRobot wrote:
Hello @phil-parkin
Thanks for your help.
Your solution resolve the problem with the sample that i give but i executed with other data i got some wrong result
Perfect, thank you. That tells me exactly what the problem is.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply