CorribView CorribView - 1 month ago 7
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
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