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.
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
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.