CODEWITHSUNDEEP
sqlsql-serversql-server-2012
user2823833
user2823833

Reputation: 105

Grouping by date difference in SQL

I am using SQL Server 2012 and can't seem to figure out how to populate the "episode" column with a sequence of numbers where there is an increment of 1 only where the dates("start_date","end_date") difference is greater than 30 days.

Any help will be appreciated.

sample

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can use lag() and a cumulative sum. But, you've already done the lag(), so:

select t.*,
       sum(case when date_delta > 30 then 1 else 0 end) over (partition by id order by start_date) + 1 as episode
from t;

If you need to calculate date_delta as well:

select t.*,
       sum(case when start_date > dateadd(day, 30, prev_date)
                then 1 else 0
           end) over (partition by id order by start_date) + 1 as episode
from (select t.*,
             lag(start_date) over (partition by id order by start_date) as prev_date
      from t
     ) t;

Upvotes: 2

Related Questions