Slicing status based on conditions
-
Hi,
I have a table ShipperStatusHistory that tracks orders, Shipping status and relay points (for customers where to collect packages) .
I want to flag the 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.
FYI : the Order and relay point is important also when when the status is shipper the id of relay point is different because is the id of the shipper
As Bellow a sample of data :
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')
,('XN80',9881,'Dropoff','2022-10-10 13:45:59.000')
,('XN80',9881,'Dropoff','2022-10-10 13:46:59.000')
,('XN81',1314,'Dropoff','2022-10-10 13:45:59.000')
,('XN81',1314,'Relay point Delivery','2022-10-11 13:46:59.000')
,('XN81',1314,'Preparation','2022-10-11 15:45:59.000')
,('XN81',1314,'Pickup','2022-10-12 08:46:59.000')The result expected :
Thanks for help !
-
This seems to be the same question as https://www.sqlservercentral.com/forums/topic/t-sql-slicing-status-by-related-time-periods-and-other-status
Why did you create it again?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Hello @phil-parkin
Not the seem the output is different
- MrRobot wrote:
Hello @phil-parkin
Not the seem the output is different
But the question is the same!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
@phil-parkin just the text but the question isn't same
-
Maybe something like this. The OUTER APPLY looks for the next occurrence of [Status] in('collected', 'shipper') for each dropoff and preparation. Also, LEAD(DateShipped) looks for the next occurrence of [Status] in('Dropoff', 'preparation'). The second query joins back to the #ShipperStatusHistory table and uses COALESCE with the collected/shipper datetime, next dropoff/preparation datetime, and GETDATE to set the interval end datetime. It assumes there aren't future DateShipper values in the table. The 'grp' value is derived from the row expanded id column
/* find the collected/shipper datetime or the next dropoff/preparaion datetime */
select ssh.*, oa.DtShipper, lead(ssh.DateShipper) over (partition by OrderNumber order by DateShipper) NxtDtShipper
from #ShipperStatusHistory ssh
outer apply (select top(1) ssh.DateShipper
from #ShipperStatusHistory sshh
where sshh.OrderNumber=ssh.OrderNumber
and sshh.DateShipper>ssh.DateShipper
and [Status] in('collected', 'shipper')
order by sshh.OrderNumber, sshh.DateShipper) oa(DtShipper)
where [Status] in('Dropoff', 'preparation')
order by OrderNumber, DateShipper;
/* left join back to #ShipperStatusHistory */
with btwn_cte as (
select ssh.*, oa.DtShipper, coalesce(oa.DtShipper, lead(ssh.DateShipper) over (partition by OrderNumber order by DateShipper), getdate()) DtShipperOrNxtDtShipperOrGetdate
from #ShipperStatusHistory ssh
outer apply (select top(1) sshh.DateShipper
from #ShipperStatusHistory sshh
where sshh.OrderNumber=ssh.OrderNumber
and sshh.DateShipper>ssh.DateShipper
and [Status] in('collected', 'shipper')
order by sshh.OrderNumber, sshh.DateShipper) oa(DtShipper)
where [Status] in('Dropoff', 'preparation'))
select ssh.*, isnull(bc.id, 0) as grp
from #ShipperStatusHistory ssh
left join btwn_cte bc on ssh.OrderNumber=bc.OrderNumber
and ssh.DateShipper >= bc.DateShipper
and ssh.DateShipper < bc.DtShipperOrNxtDtShipperOrGetdate;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Hello @scdecade
Thanks for replying.Well i see the query but it didn't give me the expected result.
Well, i did a query that give the right result just one case it's wrong it's the orderNumber = XN81
The expeted result for XN81
Mine :
;WITH CTE AS(
SELECT *
,CASE WHEN [Status] IN ('Dropoff', 'preparation') THEN 1 ELSE 0 END AS Fl
,COUNT(CASE WHEN [Status] IN ('Dropoff', 'preparation') THEN 1 END) OVER (PARTITION BY OrderNumber,RelayPoint ORDER BY DateShipper) AS Grp
FROM #ShipperStatusHistory a
)
,flag AS(
SELECT
c.*
,CASE WHEN C.Fl=1 and C.Grp=1 THEN 1 ELSE 0 END AS Flag
FROM CTE C
)
SELECT
OrderNumber
,RelayPoint
,Status
,DateShipper
,Flag
,COUNT(CASE WHEN Flag =1 THEN 1 END) OVER (PARTITION BY OrderNumber,RelayPoint ORDER BY DateShipper) AS Groupe
FROM flag
order by OrderNumber, DateShipper;If u have any idea to fix that .
Thanks
-
Looks like your flag element might be missing a condition. You have:
CASE WHEN C.Fl=1 and C.Grp=1 THEN 1 ELSE 0 END AS Flag
but it might need to change for C.Grp=1 to C.Grp >=1 like:
CASE WHEN C.Fl=1 and C.Grp>=1 THEN 1 ELSE 0 END AS Flag
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply