CODEWITHSUNDEEP
sqlsql-server
GGw
GGw

Reputation: 443

Sql Server Count with date range

Here is my sample query

create table #Date(date date,
 count int)
create table #Reservation(arrival date,
 departure date,
 name varchar(50))

insert into #Reservation(arrival,departure,name) values('2017-11-05','2017-11-07','LeBron James')
insert into #Reservation(arrival,departure,name) values('2017-11-07','2017-11-12','Stephen Curry')
insert into #Reservation(arrival,departure,name) values('2017-11-07','2017-11-12','Kevin Durant')
insert into #Reservation(arrival,departure,name) values('2017-11-11','2017-11-17','Dwyane Wade')
insert into #Reservation(arrival,departure,name) values('2017-11-15','2017-11-21','Kawhi Leonard')


DECLARE @max date 
 SELECT @max = '2017-11-30'

 ;WITH cte
 AS
 (
  SELECT GETDATE() AS [Year]
  UNION ALL
  SELECT [Year] + 1
  FROM cte
  WHERE [Year] < @max
 )

insert into #Date(date)
SELECT * FROM cte

select * from #Reservation
select * from #Date


drop table #Date
drop table #Reservation

I wanted the number of reservation on that specific day like in my getdate() its 2017-11-07, Since LeBron James has a departure date of 2017-11-07 that is also part of the count, while Stephen should have a count of 1 also per date from 2017-11-07 to 2017-11-12 and same as Durant to Leonard

Sample Output:

 date           count
 2017-11-07       3
 2017-11-08       2
 2017-11-09       2   
 2017-11-10       2
 2017-11-11       3
 2017-11-12       3
 2017-11-13       1
 2017-11-14       1
 2017-11-15       2
 2017-11-16       2
 2017-11-17       2
 2017-11-18       1
 2017-11-19       1
 2017-11-20       1
 2017-11-21       1

and the rest must be 0, is there any query to count it till the day he departed?

Upvotes: 2

Views: 3353

Answers (4)

Menelaos
Menelaos

Reputation: 25727

I ran the example on DB-Fiddle with standard SQL syntax. You need a way to generate a DateSequence (a table with daily dates that you provide). Otherwise your problem is easily solved using standard sql syntax:

select date, count(distinct name) from Date ds, Reservation r
where ds.date >= r.arrival   AND
ds.date <= r.departure group by date
order by date;

https://www.db-fiddle.com/f/rA6pBo3fDvyWPjvzgndGyh/3

Upvotes: 1

S3S
S3S

Reputation: 25112

Here's a way with a tally table (CTE) for creating a range of dates. Uncomment out the other columns and add them to the group by if you want to count the days by person, for example. 

create table #Reservation(arrival date,
 departure date,
 name varchar(50))

insert into #Reservation(arrival,departure,name) values('2017-11-05','2017-11-07','LeBron James')
insert into #Reservation(arrival,departure,name) values('2017-11-07','2017-11-12','Stephen Curry')
insert into #Reservation(arrival,departure,name) values('2017-11-07','2017-11-12','Kevin Durant')
insert into #Reservation(arrival,departure,name) values('2017-11-11','2017-11-17','Dwyane Wade')
insert into #Reservation(arrival,departure,name) values('2017-11-15','2017-11-21','Kawhi Leonard')


DECLARE @max date 
SELECT @max = '2017-11-30'


;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

select dateadd(day,-1 * N,@max) DT
into #Date
from cteTally

--select * from #Reservation

select
    d.DT
    ,count(d.DT)
    --,r.name
    --,r.arrival
    --,r.departure
from
    #Date d
full join 
    #Reservation r on
    r.arrival <= d.DT
    and r.departure >= d.DT
where
    r.name is not null
group by
    d.DT


drop table #Date
drop table #Reservation

Upvotes: 1

Ab Bennett
Ab Bennett

Reputation: 1432

Create a table of dates, one date per row (I see you have one)

Select count(*),d.thedate
From yourtable y join dates d where d.thedate between 
     y.startdate and y.enddate
Group by d.thedate order by d.thedate

Upvotes: 1

Rigerta
Rigerta

Reputation: 4039

Is this what you are looking for? 

select d.date, COUNT(r.name) as count
from #Date d left join #Reservation r on r.departure >= d.date and r.arrival <= d.date
group by d.date
order by d.date

Upvotes: 3

Related Questions