CorribView CorribView - 1 year ago 56
SQL Question

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

Answer Source
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
 From  cte 
 Where Flg>0


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