Get the last 4 weeks prior to current week-CodePudding

I have a table that has DATE_VALUE, FISCAL_WEEK, FISCAL_YEAR

DATE_VALUE  FISCAL_WEEK FISCAL_YEAR
24-DEC-21   52      2021
25-DEC-21   52      2021
26-DEC-21   52      2021
27-DEC-21   53      2021
28-DEC-21   53      2021
29-DEC-21   53      2021
30-DEC-21   53      2021
31-DEC-21   53      2021
01-JAN-22   53      2021
02-JAN-22   53      2021
03-JAN-22   1       2022
04-JAN-22   1       2022
05-JAN-22   1       2022
06-JAN-22   1       2022
07-JAN-22   1       2022
08-JAN-22   1       2022
09-JAN-22   1       2022
10-JAN-22   2       2022
11-JAN-22   2       2022
12-JAN-22   2       2022
13-JAN-22   2       2022
14-JAN-22   2       2022

The table goes on for the entire FY 2021 & 2022

I want to get the last 4 fiscal weeks (FW) prior to the current week. Let's assume this week is FW20 FY2022, I am able to get this result:

FISCAL_WEEK     FISCAL_YEAR
16              2022
17              2022
18              2022
19              2022

The code used to return the above output is:

SELECT 
  * 
FROM 
  (
    WITH t AS (
      SELECT 
        fiscal_week - 1 lastweek, 
        fiscal_week - 5 week_x, 
        fiscal_year 
      FROM 
        TABLE 
      WHERE 
        Trunc(date_value) = Trunc(sysdate)
    ) 
    SELECT 
      DISTINCT fiscal_week, 
      t.fiscal_year 
    FROM 
      TABLE 
      OUTER JOIN t ON fiscal_week <> week_x 
    WHERE 
      to_char(fiscal_week) BETWEEN lastweek - 4 
      AND lastweek 
    ORDER BY 
      fiscal_week
  )

But if the current week was FW04 FY2022, the code above is not able to return this desired output.

FISCAL_WEEK     FISCAL_YEAR
53              2021
1               2022
2               2022
3               2022

Similarly, if the current was FY03 FY2022, I want the output to be:

FISCAL_WEEK     FISCAL_YEAR
52              2021
53              2021
1               2022
2               2022

How do I need to write the code to get this output? Maybe the case statement could work but I'd like to see if there's any other workaround? Any help would be appreciated.

Thank you!

CodePudding user response：

You may put the condition on the date value not the week to get the required output, then use OFFSET 1 to skip the current week and fetch the next 4 rows only. Try the following:

WITH T AS
  (
    SELECT DISTINCT fiscal_week, fiscal_year 
    FROM TABLE_NAME 
    WHERE Trunc(date_value) <= Trunc(SYSDATE) 
    ORDER BY fiscal_year DESC, fiscal_week DESC
    OFFSET 1 ROWS 
    FETCH NEXT 4 ROWS ONLY
  )
SELECT fiscal_week, fiscal_year
FROM T ORDER BY fiscal_year, fiscal_week

See a demo.

CodePudding user response：

Don't use the FISCAL_WEEK and FISCAL_YEAR columns; just use the DATE column and compare it to a range based on the start of the ISO week:

SELECT DISTINCT fiscal_week, fiscal_year
FROM   table_name
WHERE  "DATE" <  TRUNC(SYSDATE, 'IW')
AND    "DATE" >= TRUNC(SYSDATE, 'IW') - INTERVAL '28' DAY
ORDER BY fiscal_year, fiscal_week;

Which, for the sample data:

Create Table table_name("DATE", FISCAL_WEEK, FISCAL_YEAR) AS
SELECT DATE '2021-12-24', 52, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-25', 52, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-26', 52, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-27', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-28', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-29', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-30', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2021-12-31', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2022-01-02', 53, 2021 FROM DUAL UNION ALL
SELECT DATE '2022-01-03', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-04', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-05', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-06', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-07', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-09', 1, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-10', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-11', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-12', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-13', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-01-14', 2, 2022 FROM DUAL UNION ALL
SELECT DATE '2022-02-14', 6, 2022 FROM DUAL;

If SYSDATE was 2022-01-17, would output:

FISCAL_WEEK	FISCAL_YEAR
52	2021
53	2021
1	2022
2	2022

fiddle