CODEWITHSUNDEEP
sqlsql-serverdatetime
Addeladde
Addeladde

Reputation: 787

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

With data like this:

UserID StartDate EndDate
21 2021-01-02 00:00:00 2021-01-02 23:59:59
21 2021-01-03 00:00:00 2021-01-04 15:42:00
24 2021-01-02 00:00:00 2021-01-06 23:59:59

And you want to calculate number of users that is represented on each day in a week with a result like this:

Year Week NumberOfTimes
2021 1 8
2021 2 10
2021 3 4

Basically I want to to a Select like this:

SELECT YEAR(dateColumn) AS yearname, WEEK(dateColumn)as week name, COUNT(somecolumen)
GROUP BY YEAR(dateColumn) WEEK(dateColumn)

The problem I have is the start and end date if the date goes over several days I want it to counted each day. Preferably I don't want the same user counted twice each day. There are millions of rows that are constantly being deleted and added so speed is key.

The database is MS-SQL 2019

Upvotes: 0

Views: 1273

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.

Upvotes: 3

Charlieface
Charlieface

Reputation: 72415

You need to cross-join each row with the relevant dates.

Create a calendar table with columns of years and weeks, include a start and end date of the week. See here for an example of how to create one, and make sure you index those columns.

Then you can cross-join like this

SELECT
    YEAR(dateColumn) AS yearname,
    WEEK(dateColumn)as weekname,
    COUNT(somecolumen)
FROM Table t
JOIN CalendarWeek c ON c.StartDate >= t.StartDate AND c.EndDate <= t.EndDate
GROUP BY YEAR(dateColumn), WEEK(dateColumn)

Upvotes: 0

Related Questions