Assume the employee dataset is much larger. And we want to partition on a weekly range the date an employee joined, but only for the last 6 months

How would we do that?

Here is expected result.

We see row 1&2 fall in same weekly range and are partitioned together. Row 3 is the only value of that week, so it is by itself as 1. Row 4&5 fall in the same weekly range, therefore they are 2. And row 6 does not fall within a 6 month range, therefore 0.

select id,name,date, count(*) over (partition by Date)
 from employee

CodePudding user response：

use this

select id,name,date, count(*) over (partition by DATEPART(week,Date))
 from employee