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 |
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();