Agfdhyk

I want to count total rows of 4 weeks in month and group it by location the results should be like:

location week1 week2 week3 week4

Floor A  10    0     3     4

Floor B  5     2     0     0

...

I've tried this to get a week number by given a date.

CASE WHEN DAY(create_date) >= 1 AND DAY(create_date) < 8 THEN 1 

WHEN DAY(create_date) >= 8 AND DAY(create_date) < 15 THEN 2

WHEN DAY(create_date) >= 15 AND DAY(create_date) < 22 THEN 3

ELSE 4 END AS WeekNumber

It's gives me this result:

+----+-----------+--------------+------------+

| id | create_by | create_date  | WeekNumber |

+----+-----------+--------------+------------+

|  1 |    555001 | '2018-11-01' |          1 |

|  2 |    555002 | '2018-11-05' |          1 |

|  3 |    555004 | '2018-11-06' |          1 |

|  4 |    555001 | '2018-11-10' |          2 |

|  5 |    555003 | '2018-11-17' |          3 |

|  6 |    555002 | '2018-11-17' |          3 |

|  7 |    555001 | '2018-11-18' |          3 |

|  8 |    555003 | '2018-11-20' |          3 |

|  9 |    555001 | '2018-11-22' |          4 |

| 10 |    555001 | '2018-11-25' |          4 |

+----+-----------+--------------+------------+

My tables

tbl_user

+----+----------+----------+

| id | username | location |

+----+----------+----------+

|  1 |   555001 | Floor A  |

|  2 |   555002 | Floor B  |

|  3 |   555003 | Floor C  |

|  4 |   555004 | Floor A  |

|  5 |   555005 | Floor C  |

+----+----------+----------+

tbl_list

+----+-----------+--------------+

| id | create_by | create_date  |

+----+-----------+--------------+

|  1 |    555001 | '2018-11-01' |

|  2 |    555002 | '2018-11-05' |

|  3 |    555004 | '2018-11-06' |

|  4 |    555001 | '2018-11-10' |

|  5 |    555003 | '2018-11-17' |

|  6 |    555002 | '2018-11-17' |

|  7 |    555001 | '2018-11-18' |

|  8 |    555003 | '2018-11-20' |

|  9 |    555001 | '2018-11-22' |

| 10 |    555001 | '2018-11-25' |

+----+-----------+--------------+

You can try using pivot

with cte as

(

select *,location,

CASE WHEN DAY(create_date) >= 1 AND DAY(create_date) < 8 THEN 1 

WHEN DAY(create_date) >= 8 AND DAY(create_date) < 15 THEN 2

WHEN DAY(create_date) >= 15 AND DAY(create_date) < 22 THEN 3

ELSE 4 END AS WeekNumber

from tbl_list a left join tbl_user b on a.create_by=b.username

)



select * from

(select * from cte

pivot 

(

count(create_by) for WeekNumber in ([1],[2],[3],[4])

) as pv)A left join 

(select location,count(username) countofUser from tbl_user group by location)B on 

A.location=B.location

I would simply use conditional aggregation:

select u.location,

       sum(case when day(l.create_date) >= 1 and day(l.create_date) < 8 then 1 else 0

           end) as week1,

       sum(case when day(l.create_date) >= 8 and day(l.create_date) < 15 then 1 else 0

           end) as week2,

       sum(case when day(l.create_date) >= 15 and day(l.create_date) < 22 then 1 else 0

           end) as week3,

       sum(case when day(l.create_date) >= 22 and day(l.create_date) < 29 then 1 else 0

           end) as week4

from tbl_list l join

     tbl_user u

     on l.create_by = u.username

group by u.location;

This seems much, much simpler than attempting to use pivot. Subqueries are simply not needed for this logic.