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
>Solution :
use this
select id,name,date, count(*) over (partition by DATEPART(week,Date))
from employee