CODEWITHSUNDEEP
sqlsql-server
NM24
NM24

Reputation: 97

SQL Server Comparing two rows in two different columns

ID      Date I      Date II
-----------------------------
1000    1/6/2016    2/3/2016
1000    1/28/2016   2/25/2016
1000    3/11/2016   4/8/2016
1000    4/8/2016    5/6/2016
1000    5/10/2016   6/7/2016

I want to create a flag that tells me if 2/3/2016 is less than 1/28/2016, 2/25/2016 is less than 3/11/2016 and so on.. in SQL Server.

How would I go about doing this?

Upvotes: 1

Views: 66

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use lead as below:

Select *,Flag = Case when [date II] < lead([date I]) over(partition by id order by [date I]) then 1 else 0 end 
    from #yourdate

Output as below:

+------+------------+------------+------+
|  Id  |   date I   |  date II   | Flag |
+------+------------+------------+------+
| 1000 | 2016-01-06 | 2016-02-03 |    0 |
| 1000 | 2016-01-28 | 2016-02-25 |    1 |
| 1000 | 2016-03-11 | 2016-04-08 |    0 |
| 1000 | 2016-04-08 | 2016-05-06 |    1 |
| 1000 | 2016-05-10 | 2016-06-07 |    0 |
+------+------------+------------+------+

If you are use SQL Server < 2012 then you can use query as below:

;With cte as (
    Select *, RowN = Row_Number() over(partition by Id order by [date I]) from #yourdate
) 
Select a.*, Flag = Case when a.[date II] < a1.[date I] then 1 else 0 end
from cte a left join cte a1 
on a.RowN = a1.RowN - 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming that the values are stored properly as dates, just use case and lead():

select (case when date2 < lead(date1) over (partition by id order by date1)
             then 1 else 0
        end) as is_less_than

Upvotes: 2

Related Questions