how to get all weeks in january to december in sql-CodePudding

I need to get 1st week to 52 weeks in the year in SQL query. I try using datepaert ,datediff functions in SQL. what can I use for it and how to get all weeks from the whole year

I try using this query

SELECT DATEPART(week, activity_detail.created_date) AS week,
COUNT(activity_detail.activity_type_config_id) As count,
user_det.full_name
FROM activity_detail activity_detail,activity_type_config activity_type_config,activity_user user_det 
WHERE activity_detail.activity_type_config_id = activity_type_config.activity_type_config_id and user_det.activity_user_id = activity_detail.activity_user_id
AND activity_detail.created_date BETWEEN '2021-01-08' AND '2021-11-17' 
GROUP BY DATEPART(week, activity_detail.created_date),user_det.full_name
ORDER BY DATEPART(week, activity_detail.created_date)

output

week|count|name
41  11  john
41  67  carter
41  885 rapit

this query was executed output is only database week, I need to get all weeks in a year.

I need like this output

week|count|name
1   0   null
2   0   null
3   0   null
4   0   null ...
41  67  carter
41  885 rapit
51  0   null
52  0   null

please help me

CodePudding user response：

If you have MySQL 8.0 or better, we can use recursion to generate the week numbers. There are other ways to do this for prior versions.

WITH RECURSIVE weeks (n) AS (
        SELECT 0 UNION ALL
        SELECT n 1 FROM weeks WHERE n < 52
     )
SELECT w.*, t.*
  FROM      weeks AS w
  LEFT JOIN test  AS t
    ON w.n = WEEK(t.sdate)
   AND t.sdate < '2022-01-01'
 WHERE w.n > 35             -- Check just the last few weeks of this year.
 ORDER BY n
;

Setup (to provide a few weeks of data):

CREATE TABLE test ( id  int primary key auto_increment, sdate DATE );

INSERT INTO test (sdate) VALUES
  (current_date - INTERVAL    '2' WEEK)
, (current_date - INTERVAL  0*'2' WEEK)
, (current_date   INTERVAL  1*'2' WEEK)
, (current_date   INTERVAL  2*'2' WEEK)
, (current_date   INTERVAL  3*'2' WEEK)
, (current_date   INTERVAL  4*'2' WEEK)
, (current_date   INTERVAL  5*'2' WEEK)
, (current_date   INTERVAL  6*'2' WEEK)
, (current_date   INTERVAL  7*'2' WEEK)
, (current_date   INTERVAL  8*'2' WEEK)
, (current_date   INTERVAL  9*'2' WEEK)
, (current_date   INTERVAL 10*'2' WEEK)
, (current_date   INTERVAL 11*'2' WEEK)
, (current_date   INTERVAL 12*'2' WEEK)
, (current_date   INTERVAL 13*'2' WEEK)
, (current_date   INTERVAL 14*'2' WEEK)
;

The result:

 ------ ------ ------------ 
| n    | id   | sdate      |
 ------ ------ ------------ 
|   36 | NULL | NULL       |
|   37 | NULL | NULL       |
|   38 |    1 | 2021-09-21 |
|   39 | NULL | NULL       |
|   40 |    2 | 2021-10-05 |
|   41 | NULL | NULL       |
|   42 |    3 | 2021-10-19 |
|   43 | NULL | NULL       |
|   44 |    4 | 2021-11-02 |
|   45 | NULL | NULL       |
|   46 |    5 | 2021-11-16 |
|   47 | NULL | NULL       |
|   48 |    6 | 2021-11-30 |
|   49 | NULL | NULL       |
|   50 |    7 | 2021-12-14 |
|   51 | NULL | NULL       |
|   52 |    8 | 2021-12-28 |
 ------ ------ ------------

Test case for SQL Server:

CREATE TABLE test ( id  int identity, sdate DATE );

INSERT INTO test (sdate) VALUES
  (DATEADD(week,  2, getdate()))
, (DATEADD(week, 22, getdate()))
, (DATEADD(week,  4, getdate()))
, (DATEADD(week,  6, getdate()))
, (DATEADD(week,  7, getdate()))
, (DATEADD(week,  9, getdate()))
, (DATEADD(week, 11, getdate()))
, (DATEADD(week, 13, getdate()))
, (DATEADD(week, 12, getdate()))
, (DATEADD(week, 15, getdate()))
, (DATEADD(week, 16, getdate()))
;

WITH weeks (n) AS (
        SELECT 0 UNION ALL
        SELECT n 1 FROM weeks WHERE n < 53
     )
SELECT w.*, t.*
  FROM      weeks AS w
  LEFT JOIN test  AS t
    ON w.n = DATEPART(week, t.sdate)
   AND t.sdate < '2022-01-01'
 WHERE w.n > 40
 ORDER BY n
;

The Result: