CODEWITHSUNDEEP
mysql
Rajdip Chauhan
Rajdip Chauhan

Reputation: 345

How to Get List of weeks between the given date range?

I want week list with week number, week start date and end date between two dates. Let me give you an example,

If I am passing start date as 2019-12-11 and end date as 2019-12-25, then expected output should look like below:

Week Number | Week start date | Week end date

W1           2019-12-11       2019-12-14     
W2           2019-12-15       2019-12-21     
W3           2019-12-22       2019-12-25

I have tried using below query but I got output like 

 Week start date | Week end date

 2019-12-15        2019-12-21     
 2019-12-22        2019-12-28     

select start_date, date_add(start_date, INTERVAL 6 DAY) as end_date
from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) start_date
from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where start_date between '2019-12-11' and '2019-12-25'
AND date_format(start_date, '%w') = 0

Upvotes: 1

Views: 2029

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43604

You can use the following solution using WEEK function and ROW_NUMBER:

-- calendar table: https://stackoverflow.com/a/45992247/3840840
SELECT CONCAT('W', ROW_NUMBER() OVER (ORDER BY WEEK(start_date))) AS `Week Number`, 
  MIN(start_date) AS `Week start date`, MAX(start_date) AS `Week end date`
FROM (
  SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS start_date
  FROM
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
    (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
) v
WHERE start_date BETWEEN '2019-12-11' AND '2019-12-25'
GROUP BY WEEK(start_date)
ORDER BY WEEK(start_date)

By using WEEK you don't have to calculate the weeks yourself. MySQL can detect the weeks. With WEEK you can get the week number of a date value. You can group by this week number to get the rows for every week. With MIN and MAX on the date value you get the first and last date of each week.

With ROW_NUMBER you can add a row number to your result. This function is available since MySQL 8.0. On earlier versions of MySQL you can use a solution like the following:

SELECT CONCAT('W', @row_number:=@row_number+1) AS `Week Number`, 
  MIN(start_date) AS `Week start date`, MAX(start_date) AS `Week end date`
FROM (
  SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS start_date
  FROM
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
    (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
  ORDER BY start_date
) v, (SELECT @row_number:=0) rn
WHERE start_date BETWEEN '2019-12-11' AND '2019-12-25'
GROUP BY WEEK(start_date)
ORDER BY WEEK(start_date)

demo on dbfiddle.uk

Upvotes: 1

Related Questions