sql server - T-sql count number of times a week on rows with date interval

Question

If you have table like this:

Name	Data type
UserID	INT
StartDate	DATETIME
EndDate	DATETIME

Answer 1

I would suggest a recursive CTE:

with cte as (
      select userid, startdate, enddate
      from t
      union all
      select userid, startdate,
             enddate
      from cte
      where startdate < enddate and
            week(startdate) <> week(enddate)
     )
select year(startdate), week(startdate), count(*)
from cte
group by year(startdate), week(startdate)
option (maxrecursion 0);

The CTE expands the data by adding 7 days to each row. This should be one day per week.

There is a little logic in the second part to handle the situation where the enddate ends in the same week as the last start date. The above solution assumes that the dates are all in the same year -- which seems quite reasonable given the sample data. There are other ways to prevent this problem.