group by date ranges

General
Enthusiast

group by date ranges

hi experts!

I've been looking a few threads about this topic but none seem to adjust to what I need: given a defined range, can we group date ranges?

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

 

 

thanjs in advance!!

 

 

 


Accepted Solutions
Junior Contributor

Re: group by date ranges

This could be simplified to

SELECT  START_DATE,  START_DATE+4 
FROM my_temp_view
QUALIFY
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_view
QUALIFY
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
Teradata Employee

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