CODEWITHSUNDEEP
sqloracle-database
sbrbot
sbrbot

Reputation: 6447

SQL query returnin previous weeks

I need the SQL query which will return previous 10 weeks in format 2017W03 for given week in the sam format.

So for week given in format 2017W05 this query should return:

2017W05
2017W04
2017W03
2017W02
2017W01
2016W52
2016W51
2016W50
2016W49
2016W48

Upvotes: 0

Views: 71

Answers (2)

user5683823
user5683823

Reputation:

Here is a simplified solution, taking advantage of the fact that January 4 always falls in the first ISO week of its year. (January 1 may fall in the last ISO week of the prior year, and that would lead to complications.)

An earlier version (which can be reviewed by clicking on the "edited ..." link below the Answer) did not take advantage of this observation.

Also, credit to tbone - to get the proper ISO year/week combination, we must use the format model component IYYY for the year (in TO_CHAR), rather than YYYY.

with
     input ( str ) as (
       select '2017W05' from dual
     )
select     to_char(to_date(substr(str, 1, 4)||'0104', 'yyyymmdd') +
                     7 * (to_number(substr(str, 6)) - level), 'iyyy"W"iw') as result
from       input
connect by level <= 10
;



RESULT
-------
2017W05
2017W04
2017W03
2017W02
2017W01
2016W52
2016W51
2016W50
2016W49
2016W48

Upvotes: 1

tbone
tbone

Reputation: 15473

This should give you the output you listed:

select TO_CHAR(to_date('20170201','YYYYMMDD')-(7*(level-1)),'IYYY"W"IW') 
from dual
connect by level <= 10

Output:

2017W05
2017W04
2017W03
2017W02
2017W01
2016W52
2016W51
2016W50
2016W49
2016W48

Starting with the arbitrary date of Feb 1st 2017

Upvotes: 0

Related Questions