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 :

    Capture

    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?

    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

    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!

    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.

  • @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

    Capture

    Mine :

    Capture

    ;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