How to get last N week data in different year

Question

I need to get last 6 weeks data from some table, right now the logic that I use is this

WEEK([date column]) BETWEEN WEEK(NOW()) - 6 AND WEEK(NOW())

It run as I want, but January is near and I realize that this query will not working as it is. I try to run my query on 15th January 2022, I only get data from 1st January to 15th January when I use my logic.

TGL           MINGGU_KE
2022-01-01  |     1
2022-01-02  |     2
2022-01-03  |     2
2022-01-04  |     2
2022-01-05  |     2
2022-01-06  |     2
2022-01-07  |     2
2022-01-08  |     2
2022-01-09  |     3
2022-01-10  |     3
2022-01-11  |     3
2022-01-12  |     3
2022-01-13  |     3
2022-01-14  |     3
2022-01-15  |     3

Can I get the last 6 weeks data including last year?

This is my dbfiddle: https://dbfiddle.uk/o9BeAFJF

Answer 1

You can round the dates to the first day of the week using ROUND, TRUNC or THIS_WEEK

WITH
SEARCH_WEEK (TGL) AS (
  VALUES date '2020-12-01'
  UNION ALL
  SELECT tgl + 1 DAY FROM SEARCH_WEEK WHERE tgl < CURRENT date
  ),
BASE_DATE (base_date) AS (
    VALUES date '2022-01-15'
),
OPTIONS (OPTION, OPTION_BASE_DATE) AS (
    SELECT OPTION, option_base_date FROM base_date CROSS JOIN LATERAL (
        VALUES
        ('ROUND D', ROUND(base_date, 'D')),
        ('ROUND IW', ROUND(base_date, 'IW')),
        ('ROUND W', ROUND(base_date, 'W')),
        ('ROUND WW', ROUND(base_date, 'WW')),
        ('TRUNC D', TRUNC(base_date, 'D')),
        ('TRUNC IW', TRUNC(base_date, 'IW')),
        ('TRUNC W', TRUNC(base_date, 'W')),
        ('TRUNC WW', TRUNC(base_date, 'WW')),
        ('THIS_WEEK', THIS_WEEK(base_date)),
        ('THIS_WEEK + 1 DAY', THIS_WEEK(base_date) + 1 DAY)
    ) a (OPTION, OPTION_BASE_DATE)
)
SELECT
    OPTION,
    MIN(TGL) BEGIN,
    max(tgl) END,
    dayname(MIN(TGL)) day_BEGIN,
    dayname(max(tgl)) day_end,
    days_between(max(tgl), min(tgl)) + 1 duration_in_days
FROM
  SEARCH_WEEK
  CROSS JOIN options
WHERE
  TGL BETWEEN option_base_date - 35 DAYS AND option_base_date + 6 DAYS
GROUP BY OPTION

OPTION	BEGIN	END	DAY_BEGIN	DAY_END	DURATION_IN_DAYS
ROUND D	2021-12-12	2022-01-22	Sunday	Saturday	42
ROUND IW	2021-12-13	2022-01-23	Monday	Sunday	42
ROUND W	2021-12-11	2022-01-21	Saturday	Friday	42
ROUND WW	2021-12-11	2022-01-21	Saturday	Friday	42
THIS_WEEK	2021-12-05	2022-01-15	Sunday	Saturday	42
THIS_WEEK + 1 DAY	2021-12-06	2022-01-16	Monday	Sunday	42
TRUNC D	2021-12-05	2022-01-15	Sunday	Saturday	42
TRUNC IW	2021-12-06	2022-01-16	Monday	Sunday	42
TRUNC W	2021-12-11	2022-01-21	Saturday	Friday	42
TRUNC WW	2021-12-11	2022-01-21	Saturday	Friday	42

fiddle