CODEWITHSUNDEEP
sqlsql-serversequential
CorribView
CorribView

Reputation: 741

SQL Server: Find rows in table where both columns not sequential

I have a table with data that looks like this:

pK    Customer     DateTime1                  DateTime2
1     6            2016-04-01 00:00:00.000    2016-10-09 00:00:00.000
2     6            2016-07-01 00:00:00.000    2016-10-21 00:00:00.000
3     6            2016-10-01 00:00:00.000    2016-10-20 00:00:00.000

I want to find rows where, when DateTime1 is ordered, the corresponding DateTime2 value (when filtered on customer ID) are not following the same ordering.

So in the case above I would like to find row with pK 3 as when DateTime1 is ordered ascending, then DateTime2 isn't greater than DateTime2 in row 2.

It seems similar to this question but it deals with the order of items rather then inequality: TSQL check if specific rows sequence exists

I tried using a version of the CTE statement

Upvotes: 2

Views: 92

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Declare @YourTable table (pK int,Customer int,DateTime1 datetime,DateTime2 datetime)
Insert Into @YourTable values
(1,6,'2016-04-01 00:00:00.000','2016-10-09 00:00:00.000'),
(2,6,'2016-07-01 00:00:00.000','2016-10-21 00:00:00.000'),
(3,6,'2016-10-01 00:00:00.000','2016-10-20 00:00:00.000')

;with cte as (
    Select *,Flg=Row_Number() over (Partition By Customer Order By DateTime1) - Row_Number() over (Partition By Customer Order By DateTime2) 
     From  @YourTable
)
Select pK
      ,Customer
      ,DateTime1
      ,DateTime2 
 From  cte 
 Where Flg>0

Returns

pK  Customer    DateTime1               DateTime2
3   6           2016-10-01 00:00:00.000 2016-10-20 00:00:00.000

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269883

This would appear to be a great application of row_number():

select t.*
from (select t.*,
             row_number() over (partition by customer order by datetime1) as seqnum_1,
             row_number() over (partition by customer order by datetime2) as seqnum_2
      from t
     ) t
where seqnum_1 <> seqnum_2;

But this would return all mis-ordered rows, based on the global ordering (pk 2 and 3 in this case).

You just want where the direction is changing on a given row. For that, use lag():

select t.*
from (select t.*,
             lag(datetime1) over (partition by customer order by pk) as prev_dt1,
             lag(datetime2) over (partition by customer order by pk) as prev_dt2
      from t
     ) t
where (dt1 > prev_dt1 and dt2 <= prev_dt2) or
      (dt1 < prev_dt1 and dt2 >= prev_dt2);

Upvotes: 0

Related Questions