CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
Heisenberg
Heisenberg

Reputation: 5279

How to select only one year data from this week in Redshift

I have a table like following

year  month isoweek day
・
・
2020  12     49  2021/12/5
・
・ 
2020  12　   50  2021/12/7
・
・
2021  12     51  2021/12/20 
2021  12     51  2021/12/21

I'd like to select only one year in isoweek in this example, today is 2021/12/20 and its isoweek in this table is 51 I'd like to get 2020 isoweek 52 to 2021 isoweek 51. Are there any way to achieve this?

year  month isoweek day
2020  12　   52  2020/12/21 
・
・
2021  12     51  2021/12/20 
2021  12     51  2021/12/21

I tried following query, but it still cant extract one year range.

select *
from table
where day <= current_date

If someone has opinion,please let me know Thanks

Upvotes: 2

Views: 320

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

select      t.*
from        tbl                           t
cross join  (
              select  yr,
                      isoweek
              from    tbl
              where   dt = trunc(sysdate)
            )                             x
where       (t.yr = x.yr - 1 and t.isoweek > x.isoweek)
         or (t.yr = x.yr     and t.isoweek <= x.isoweek);

I've changed some column and table names for purposes of avoiding reserved words in a fiddle -- http://sqlfiddle.com/#!4/98de6d/1/0

The year of data in this query is based on the current date, as that is what you alluded to being desired. If a different date were desired, replace trunc(sysdate) with a given date.

Upvotes: 1

Related Questions