title: Sql Server Count with date range
body: <p>Here is my sample query</p>
  
  <pre><code>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] &lt; @max
   )
  
  insert into #Date(date)
  SELECT * FROM cte
  
  select * from #Reservation
  select * from #Date
  
  
  drop table #Date
  drop table #Reservation
  </code></pre>
  
  <p>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</p>
  
  <p>Sample Output:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>and the rest must be 0, is there any query to count it till the day he departed?</p>