Home > Net >  How to get last N week data in different year
How to get last N week data in different year

Time:10-20

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

CodePudding user response:

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

CodePudding user response:

you can use dateadd to get first day of week six weeks ago like this:

Select * from tableName where [dateColumn] between dateadd(WEEK,-6,getdate()) and getdate()

CodePudding user response:

You can use DATEADD to get last 6 weeks of data as follows:

Select * from [TableName] where [DateColumn] between
DATEADD(WEEK,-6,GETDATE()) and GETDATE();
  • Related