CODEWITHSUNDEEP
sqlsql-servert-sql
Luis felipe De jesus Munoz
Luis felipe De jesus Munoz

Reputation: 7933

SQL partition by on date range

Assume this is my table:

ID  NUMBER  DATE
------------------------
1   45      2018-01-01
2   45      2018-01-02
2   45      2018-01-27

I need to separate using partition by and row_number where the difference between one date and another is greater than 5 days. Something like this would be the result of the above example:

ROWNUMBER   ID  NUMBER  DATE
-----------------------------
1           1   45      2018-01-01
2           2   45      2018-01-02
1           3   45      2018-01-27

My actual query is something like this:

SELECT ROW_NUMBER() OVER(PARTITION BY NUMBER ODER BY ID DESC) AS ROWNUMBER, ...

But as you can notice, it doesn't work for the dates. How can I achieve that?

Upvotes: 11

Views: 37660

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

by using lag and datediff funtion 

select * from
    (
    select t.*,
           datediff(day,
                    lag(DATE) over (partition by NUMBER order by id),
                    DATE
                   ) as diff
    from  t 
      ) as TT where  diff>5

http://sqlfiddle.com/#!18/130ae/11

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270773

I think you want to identify the groups, using lag() and datediff() and a cumulative sum. Then use row_number():

select t.*,
       row_number() over (partition by number, grp order by date) as rownumber
from (select t.*,
             sum(grp_start) over (partition by number order by date) as grp
      from (select t.*,
                   (case when lag(date) over (partition by number order by date) < dateadd(day, 5, date)
                            then 1 else 0
                    end) as grp_start
            from t
           ) t
     ) t;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use lag function :

select *, row_number() over (partition by number, grp order by id) as [ROWNUMBER]
from (select *, (case when datediff(day, lag(date,1,date) over (partition by number order by id), date) <= 1 
                      then 1 else 2 
                 end) as grp
      from table
     ) t;

Upvotes: 6

Related Questions