General
Enthusiast

group by date ranges

hi experts!

imagine a table with only dates, and a defined group size of 5. can we group by ranges of 5 days?

source data:

DATE

--------

1/1/2001

2/1/2001

3/1/2001

4/1/2001

5/1/2001

6/1/2001

7/1/2001

8/1/2001

9/1/2001

10/1/2001

11/1/2001

12/1/2001

13/1/2001

14/1/2001

15/1/2001

result:

1/1/2001-5/1/2001

6/1/2001-10/1/2001

11/1/2001-15/1/2001

Accepted Solutions
Junior Contributor

Re: group by date ranges

This could be simplified to

```SELECT  START_DATE,  START_DATE+4
FROM my_temp_viewQUALIFY   ROW_NUMBER() OVER (ORDER BY START_DATE) = 1 OR ROW_NUMBER() OVER (ORDER BY START_DATE) MOD 5 = 0```

But are you sure it's the result you want?

The end date of first row overlaps with the start date of the 2nd:

01/12/2016 05/12/2016
05/12/2016 09/12/2016

10/12/2016 14/12/2016

1 ACCEPTED SOLUTION
5 REPLIES
Enthusiast

Re: group by date ranges

just to show I'm trying, I made this, but I don't like it (source table is continuos dates of december/2016)

SyntaxEditor Code Snippet

```SELECT  START_DATE,  START_DATE+4
FROM
(SELECT  START_DATE , ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY  START_DATE) AS rn,MOD( rn  ,5) rnmod
FROM my_temp_view) AS tempp1 WHERE rnmod = 0 OR rn=1```

result:

01/12/2016 05/12/2016
05/12/2016 09/12/2016
10/12/2016 14/12/2016
15/12/2016 19/12/2016
20/12/2016 24/12/2016
25/12/2016 29/12/2016
30/12/2016 03/01/2017

Junior Contributor

Re: group by date ranges

This could be simplified to

```SELECT  START_DATE,  START_DATE+4
FROM my_temp_viewQUALIFY   ROW_NUMBER() OVER (ORDER BY START_DATE) = 1 OR ROW_NUMBER() OVER (ORDER BY START_DATE) MOD 5 = 0```

But are you sure it's the result you want?

The end date of first row overlaps with the start date of the 2nd:

01/12/2016 05/12/2016
05/12/2016 09/12/2016

10/12/2016 14/12/2016

Enthusiast

Re: group by date ranges

Hi Dieter!

That solution is a lot more fancier thanks!

I really don't mind that overlapping, ideally it should be 1 til 5, then 6 til 10, then 11 til 14 and so on. But it's fine because I just want to process batches.

I also wanted to avoid the +4 and make use of a single "5" all around, but hey...

thanks again,

Junior Contributor

Re: group by date ranges

Then it's easier, change the QUALIFY to

`   (Row_Number() Over (ORDER BY START_DATE) -1) MOD 5 = 0`

Or simpler:

`Row_Number() Over (ORDER BY START_DATE) MOD 5 = 1`

Re: group by date ranges

SyntaxEditor Code Snippet

```SEL STARTDATE, STARTDATE+4
FROM TIMETABLE
QUALIFY ROW_NUMBER() OVER(ORDER BY STARTDATE)=1
OR ROW_NUMBER() OVER(ORDER BY STARTDATE) MOD 5=1;```

STARTDATE (STARTDATE+4)
01/30/18 02/03/18           /* Date Format is 'mm/dd/yy' */
02/04/18 02/08/18
02/09/18 02/13/18