CODEWITHSUNDEEP
amazon-redshift
Velu
Velu

Reputation: 57

How to get last 3 Months of "Monday to Sunday" dates In Redshift?

How can I get last 3 Months of "Monday to Sunday" dates in Redshift?

S.no    Start_dt    End_dt  week
1   18-Jul-16   24-Jul-16   Week1
2   25-Jul-16   31-Jul-16   Week2
3   1-Aug-16    7-Aug-16    Week3
4   8-Aug-16    14-Aug-16   Week4
5   15-Aug-16   21-Aug-16   Week5
6   22-Aug-16   28-Aug-16   Week6
7   29-Aug-16   4-Sep-16    Week7
8   5-Sep-16    11-Sep-16   Week8
9   12-Sep-16   18-Sep-16   Week9
10  19-Sep-16   25-Sep-16   Week10
11  26-Sep-16   2-Oct-16    Week11
12  3-Oct-16    9-Oct-16    Week12
13  10-Oct-16   16-Oct-16   Week13

I've tried this:

select 
  trunc(date_trunc('week',sysdate)) st_dt,
  trunc(date_trunc('week', sysdate)+6) ed_dt,
  'week'||row_number() over (order by null) as week

but it only returns the current week's Monday and Sunday.

Upvotes: 0

Views: 1710

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 269410

You can use generate_series() to generate a range of dates:

SELECT
  trunc(day)     as start_date,
  trunc(day + 6) as end_date
FROM
  (select date_trunc('week', sysdate) + (generate_series(1, 12) * interval '1 week') as day)
ORDER BY 1 ASC

This results in:

week start  week end
2016-10-24  2016-10-30
2016-10-31  2016-11-06
2016-11-07  2016-11-13
2016-11-14  2016-11-20
2016-11-21  2016-11-27
2016-11-28  2016-12-04
2016-12-05  2016-12-11
2016-12-12  2016-12-18
2016-12-19  2016-12-25
2016-12-26  2017-01-01
2017-01-02  2017-01-08
2017-01-09  2017-01-15

Please note that generate_series() in Amazon Redshift cannot be joined with existing tables. It can only be used as a "Leader-only" query.

Upvotes: 1

Related Questions