CODEWITHSUNDEEP
sqlsql-serverdateasp.net-coregroup-by
Lukáš
Lukáš

Reputation: 23

Count rows for every day between two date columns SQL Server

I have a table 'events' :

id | name | date_start | date_end
---+------+------------+------------
1  | aaa  | 2020-01-01 | 2020-01-03
2  | bbb  | 2020-01-02 | 2020-01-05

And I want to count rows for every day between date_start and date_end from that table, for example: 

date       | count
-----------+-------
2020-01-01 | 1
2020-01-02 | 1
2020-01-03 | 1

date       | count
---------------------
2020-01-02 | 1
2020-01-03 | 1
2020-01-04 | 1
2020-01-05 | 1

So the final result should look like this:

date       | count
-----------+-------
2020-01-01 | 1
2020-01-02 | 2
2020-01-03 | 2
2020-01-04 | 1
2020-01-05 | 1

Is it possible to do with sql or should I calculate it on server? That table will have thousand of rows so I dont want to load all of them when I need only that result.

Using ASP.NET Core 3.1 with SQL Server.

Upvotes: 0

Views: 914

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37460

This is usually done with calendar talbe. See sample data below and the query:

declare @tbl table (id int, name varchar(20), date_start date, date_end date)
insert into @tbl values
( 1  , 'aaa'  , '2020-01-01' , '2020-01-03'),
( 2  , 'bbb'  , '2020-01-02' , '2020-01-05');
-- set boundaries for calendar
declare @startDate date, @endDate date;
select @startDate = min(date_start), @endDate = max(date_end) from @tbl;

;with calendar as (
    select @startDate dates
    union all
    select dateadd(day, 1, dates) from calendar
    where dates < @endDate
)
select c.dates, count(*) from calendar c
join @tbl t on c.dates between t.date_start and t.date_end
group by c.dates

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use recursive cte : 

with cte as (
   select t.date_start, t.date_end
   from table t
   union all
   select dateadd(day, 1, date_start), date_end
   from cte c
   where c.date_start < c.date_end
)
select date_start, count(*) as cnt
from cte c
group by date_start
option (maxrecursion 0)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270643

A recursive CTE is convenient here (if you don't have a numbers table or a calendar table on-hand).

with cte as (
      select id, name, date_start, date_end
      from t
      union all
      select id, name, dateadd(day, 1, date_start), date_end
      from t
      where date_start < date_end
     )
select date_start as date, count(*)
from cte
group by date_start
order by date_start;

If any of the periods are longer than 100 days, you need to add option (maxrecursion) to the query.

Upvotes: 0

Related Questions