2

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

Community
  • 1
  • 1
Yegor Razumovsky
  • 902
  • 2
  • 9
  • 26

1 Answers1

4

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.

Lamak
  • 69,480
  • 12
  • 108
  • 116
  • i am definetly have more then 2500 days. What is cross join ? Can you explain how your code works ? – Yegor Razumovsky Nov 29 '13 at 13:49
  • cross join is a multiplication between all line of the first table by all line of the other table. – Mathese F Nov 29 '13 at 14:48
  • 1
    Is it imaginable, cause sometimes it's easier and it can help, to create a table with all day since 2008 to now and then build the query on that? Sometimes Space vs performance is a good choice – Mathese F Nov 29 '13 at 14:49