Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
402 views
in Technique[技术] by (71.8m points)

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

If you have table like this:

Name Data type
UserID INT
StartDate DATETIME
EndDate DATETIME

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...