Calculate Date Range count for each day SQL Server 2012

I'm having trouble with getting the records for the following

 | DATEFROM     | DATETO
 | 2012-01-02   | 2012-01-03
 | 2012-01-11   | 2012-01-16
 | 2012-01-08   | 2012-01-22
 | 2012-01-29   | 2012-01-30
 | 2012-01-08   | 2012-01-11

I'm trying to get count of ranges containing the day for each day from beginning of first range ending last date of last range.

Sample output:

2012-01-02 | 1
2012-01-03 | 1
2012-01-08 | 2
2012-01-09 | 2
2012-01-10 | 2
2012-01-11 | 3
2012-01-12 | 2
2012-01-13 | 2
2012-01-14 | 2
2012-01-15 | 2
2012-01-16 | 2
......

My database contains data from 2008 to nowadays.

In other words I am trying to get how many times a record is found for a specific date. Not every day is in the TABLE for each month

I found this post Tricky mysql count occurrences of each day within date range but can't convert the code provided to my SQL Server 2012.

You could try here http://sqlfiddle.com/#!6/0855b/1

Answers


Ok, this is one way to do what you want:

DECLARE @MinDate DATE, @MaxDate DATE;

SELECT  @MinDate = MIN(DATEFROM),
        @MaxDate = MAX(DATETO)
FROM ENTRIES;

WITH Dates AS
(
    SELECT DATEADD(DAY,number,@MinDate) [Date]
    FROM master.dbo.spt_values
    WHERE type = 'P'
    AND number > 0
    AND DATEADD(DAY,number,@MinDate) <= @MaxDate
)
SELECT  A.[Date],
        COUNT(*) N
FROM Dates A
LEFT JOIN Entries B
    ON A.[Date] >= B.DATEFROM 
    AND A.[Date] <= B.DATETO
GROUP BY A.[Date]
ORDER BY A.[Date]

If the range dates is over 2047 days, then you'll need to create more values than the ones that are available in master.dbo.spt_values (this is trivial, for instance you can use a CROSS JOIN).

Here is the sqlfiddle for you to try.


Need Your Help

Chat Application in ASP.NET

asp.net ajax chat

I've to write an Ajax chat web application in ASP.NET for a friend, and I've a question: if client1 sends a message to client2, how should the application send the message to client2? Is there a be...

How to get access token with Xamarin.Auth?

c# authentication xamarin oauth-2.0 xamarin.auth

https://components.xamarin.com/gettingstarted/xamarin.auth