Get next row not working from selected based on date time

Question

I have the following data and I want to be able to put all the rows into one line based on the stop type id. So a stop type is in order which means a 0 or 2 will come before a 3. I believe Lead is what I want to use, but doesn't seem to be working like I want and haven't been able to figure out why.

This is what the raw data looks like based on the GMT Date time.

OrderId          GmtDeliveryDateTime        StopTypeId
3650             2019-01-11 13:04:44.000        0       
3650             2019-01-11 14:22:09.000        3       
3650             2019-01-11 15:13:35.000        2       
3650             2019-01-11 16:05:14.000        3

And I want to get it to look like this:

OrderId  GmtDeliveryDateTime    StopTypeId   GmtDeliveryDateTime    StopTypeId
3650    2019-01-11 13:04:44.000     0       2019-01-11 14:22:09.000     3
3650    2019-01-11 15:13:35.000     2       2019-01-11 16:05:14.000     3

Here is the query I am using:

SELECT *
FROM (
    SELECT OrderId,
           GmtDeliveryDateTime,
           StopTypeId,        
           LEAD(StopTypeId) OVER (ORDER BY GmtDeliveryDateTime, StopTypeId) NxtStop
    FROM table
)

Here are the result the above produces:

OrderId GmtDeliveryDateTime     StopTypeId  NxtStop
3650    2019-01-11 13:04:44.000     0         2
3650    2019-01-11 15:13:35.000     2         2
3650    2019-01-11 14:22:09.000     3         3
3650    2019-01-11 16:05:14.000     3         2

What is wrong with my query?

can there be a case of 2 consecutive 3's? – Vamsi Prabhala Mar 03 '19 at 03:46 — Vamsi Prabhala, Mar 03 '19 at 03:46

score 1 · Answer 1 · answered Mar 03 '19 at 12:45

If you can guarantee that the rows interleave, you can do:

SELECT t.*
FROM (SELECT OrderId,
             GmtDeliveryDateTime,
             StopTypeId,        
             LEAD(GmtDeliveryDateTime) OVER (PARTITION BY OrderId ORDER BY GmtDeliveryDateTime, StopTypeId) as next_GmtDeliveryDateTime,
             LEAD(StopTypeId) OVER (PARTITION BY OrderId ORDER BY GmtDeliveryDateTime, StopTypeId) as next_StopTypeId
      FROM table t
     ) t
WHERE StopTypeId <> 3;

Vamsi Prabhala · Answer 2 · 2019-03-03T04:12:44.800

Assuming rows with 0,3 or 2,3 as consecutive stop id's identify a group for a given order id, you can do this using a running sum to classify consecutive 0,3 or 2,3 rows into a group and then use group by to get the desired result.

SELECT OrderId,
       MIN(GmtDeliveryDateTime),
       MIN(StopTypeId),
       MAX(GmtDeliveryDateTime),
       MAX(StopTypeId)
FROM (SELECT t.*,sum(case when StopTypeId=3 then 1 else 0 end) over(partition by OrderId order by GmtDeliveryDateTime) as grp
      FROM table t
     ) t 
GROUP BY OrderId,grp

score 0 · Answer 3 · answered Mar 03 '19 at 04:01

I understand that you are trying to group records two by two, each record with the next one, ordered by GmtDeliveryDateTime.

Here is a solution that uses LAG() within a subquery to recover the relevant values, and ROW_NUMBER() to assign a number to each record, ordered by GmtDeliveryDateTime. The outer query filters out one record out of two, using the row number (even row numbers are filtered out):

SELECT *
FROM (
    SELECT 
        OrderId,
        GmtDeliveryDateTime,
        StopTypeId,        
        LEAD(GmtDeliveryDateTime) OVER (ORDER BY GmtDeliveryDateTime) NxtGmtDeliveryDateTime,
        LEAD(StopTypeId) OVER (ORDER BY GmtDeliveryDateTime) NxtStopTypeId,
        ROW_NUMBER() OVER (ORDER BY GmtDeliveryDateTime) rn
    FROM mytable 
) x WHERE rn % 2 <> 0

NB : I removed the ORDER BY on StopTypeId, since your sample data does not show duplicate GmtDeliveryDateTime.

This demo on DB Fiddle with your sample data returns:

<pre>
OrderId | GmtDeliveryDateTime | StopTypeId | NxtGmtDeliveryDateTime | NxtStopTypeId | rn
------: | :------------------ | ---------: | :--------------------- | ------------: | :-
   3650 | 11/01/2019 00:00:00 |          0 | 11/01/2019 00:00:00    |             3 | 1
   3650 | 11/01/2019 00:00:00 |          2 | 11/01/2019 00:00:00    |             3 | 3
</pre>

score 0 · Answer 4 · answered Mar 03 '19 at 04:04

You can try below -

DEMO

SELECT OrderId,
       MIN(GmtDeliveryDateTime) as starttime,
       MIN(StopTypeId) as startStopTypeId,
       MAX(GmtDeliveryDateTime) as endtime,
       MAX(StopTypeId) as nextStopTypeId
       from
(
SELECT t.*,
row_number() over(order by GmtDeliveryDateTime)-
sum(case when StopTypeId=3 then 1 else 0 end) over(partition by OrderId order by GmtDeliveryDateTime) as grp
      FROM  t1 t
)A group by grp,OrderId

OUTPUT:

OrderId starttime           startStopTypeId endtime             nextStopTypeId
3650    11/01/2019 13:04:44   0             11/01/2019 14:22:09  3
3650    11/01/2019 15:13:35   2             11/01/2019 16:05:14  3

score 0 · Answer 5 · answered Mar 03 '19 at 04:21

I know others have answered, but I used your initial query and modified it slight to get your desired results:

DROP TABLE IF EXISTS #SO;

CREATE TABLE #SO
    (
        OrderID INT ,
        DeliveryDate DATETIME ,
        StopTypeID INT
    );

INSERT INTO #SO ( OrderID ,
                  DeliveryDate ,
                  StopTypeID )
VALUES ( 3650, '2019-01-11 13:04:44.000', 0 ) ,
       ( 3650, '2019-01-11 14:22:09.000', 3 ) ,
       ( 3650, '2019-01-11 15:13:35.000', 2 ) ,
       ( 3650, '2019-01-11 16:05:14.000', 3 );

SELECT x.OrderID ,
       x.DeliveryDate ,
       x.StopTypeID ,
       x.NxtStop ,
       ROW_NUMBER () OVER ( ORDER BY x.DeliveryDate ) AS rownumber
INTO   #TestData
FROM
       (
           SELECT OrderID ,
                  DeliveryDate ,
                  StopTypeID ,
                  LEAD ( StopTypeID ) OVER ( ORDER BY DeliveryDate , StopTypeID ) NxtStop
           FROM   #SO
       ) AS x;

SELECT a.OrderID ,
      a.DeliveryDate ,
      a.StopTypeID ,
      b.DeliveryDate ,
      b.StopTypeID
FROM  #TestData AS a
      INNER JOIN #TestData AS b ON b.OrderID = a.OrderID
                                   AND a.NxtStop = b.StopTypeID
                                   AND a.rownumber + 1 = b.rownumber
WHERE a.StopTypeID < b.StopTypeID;

DROP TABLE IF EXISTS #TestData;

Get next row not working from selected based on date time

5 Answers5