Using SQL Server 2012 LAG

I am trying to write a query using the SQL Server 2012 LAG function to retrieve data from my [Order] table where the difference in date and time between the row and the previous row is less than 2 minutes.

As a result, I expect

1234    April, 28 2012 09:00:00

1234    April, 28 2012 09:01:00

1234    April, 28 2012 09:03:00

5678    April, 28 2012 09:40:00

5678    April, 28 2012 09:42:00

5678    April, 28 2012 09:44:00

      

but i see

1234    April, 28 2012 09:00:00

1234    April, 28 2012 09:01:00

1234    April, 28 2012 09:03:00

5678    April, 28 2012 09:40:00

5678    April, 28 2012 09:42:00

5678    April, 28 2012 09:44:00

91011   April, 28 2012 10:00:00

      

The last line should not be returned. Here's what I tried: SQL Fiddle

Anyone with ideas?

+3


source to share


1 answer


Ok, first I added a line to show you where someone else's answer is not working, but they removed it now.

Now for the logic in my request. You said you want each line to be two minutes from the other line. This means that you have to look not only backwards but also forward with LEAD (). In your request, you returned when the previous time was NULL, so it just returned the first value of each OrderNumber, whether it was right or wrong. Incidentally, the first values ​​of each of your OrderNumbers needed to be returned until you get to the last OrderNumber where it broke. My request fixes this and should work for all your data.

CREATE TABLE [Order]  
    (
            OrderNumber    VARCHAR(20) NOT NULL
        ,   OrderDateTime   DATETIME NOT NULL
    );

    INSERT [Order] (OrderNumber, OrderDateTime) 
    VALUES
        ('1234', '2012-04-28 09:00:00'),
        ('1234', '2012-04-28 09:01:00'),
        ('1234', '2012-04-28 09:03:00'),
        ('5678', '2012-04-28 09:40:00'),
        ('5678', '2012-04-28 09:42:00'),
        ('5678', '2012-04-28 09:44:00'),
        ('91011', '2012-04-28 10:00:00'),
        ('91011', '2012-04-28 10:25:00'),
        ('91011', '2012-04-28 10:27:00');

with Ordered as (
  select
    OrderNumber,
    OrderDateTime,
    LAG(OrderDateTime,1) over (
      partition by OrderNumber
      order by OrderDateTime
    ) as prev_time,
    LEAD(OrderDateTime,1) over (
      partition by OrderNumber
      order by OrderDateTime
    ) as next_time
  from [Order]
)

SELECT  OrderNumber,
        OrderDateTime
FROM Ordered
WHERE   DATEDIFF(MINUTE,OrderDateTime,next_time) <= 2  --this says if the next value is less than or equal to two minutes away return it
        OR DATEDIFF(MINUTE,prev_time,OrderDateTime) <= 2 --this says if the prev value is less than or equal to 2 minutes away return it

      



Results (remember, I added a line):

OrderNumber          OrderDateTime
-------------------- -----------------------
1234                 2012-04-28 09:00:00.000
1234                 2012-04-28 09:01:00.000
1234                 2012-04-28 09:03:00.000
5678                 2012-04-28 09:40:00.000
5678                 2012-04-28 09:42:00.000
5678                 2012-04-28 09:44:00.000
91011                2012-04-28 10:25:00.000
91011                2012-04-28 10:27:00.000

      

+3


source







All Articles