Counting Rows between dates...

Question

I'm using a CTE to generate a range of dates.

12/02/2010 10:00:00 12/02/2010 10:59:59
12/02/2010 11:00:00 12/02/2010 11:59:59
12/02/2010 12:00:00 12/02/2010 12:59:59

I then left join this to a indexed view containing huge amounts of date.

I have 2 options for counting between the date ranges

1) i would SUM(case) test the log_date to test if it is between the start and end dates, + 1 for true, 0 for false - so if no results i would always get '0'

12/02/2010 10:00:00 12/02/2010 10:59:59    0
12/02/2010 11:00:00 12/02/2010 11:59:59    1
12/02/2010 12:00:00 12/02/2010 12:59:59    0

2) i can count(*) using a WHERE clause per date range.

12/02/2010 11:00:00 12/02/2010 11:59:59    1

As you would expect 1) is effective but has a massive overhead on performance 2) is possibly 8000% more efficent BUT fails to return the range should a filter be applied which returns null results between the specified date range.

Is there a way to use the efficent WHERE clause but retain the date range row detailing '0'?

here is some SQL for the case solution:

SELECT     [LABEL], [Display Start Date], [Display End Date], 
    SUM(CASE WHEN ([LOG].line_date BETWEEN [Start Date] AND [End Date]) THEN 1 ELSE 0 END) AS [Total Calls], 
    SUM(CASE WHEN ([LOG].line_date BETWEEN [Start Date] AND [End Date]) AND ([LOG].line_result = 1) THEN 1 ELSE 0 END) AS [1 Calls], 

FROM         [DATE RANGE FUNCTION] LEFT JOIN
                      dbo.vCallLog WITH (noexpand) as [LOG] on 0 > -1
GROUP BY [Start Date], [End Date], [Display Start Date], [Display End Date], [LABEL]

here is some SQL for the WHERE solution:

SELECT     [LABEL], [Display Start Date], [Display End Date], 
                    COUNT(dbo.vCallLog.line_id) AS [Total Calls], 
                    SUM(CASE WHEN ([LOG].line_result = 1) THEN 1 ELSE 0 END) AS [1 Calls], 
FROM         [DATE RANGE FUNCTION] LEFT JOIN
                      dbo.vCallLog WITH (noexpand) as [LOG] on 0> -1
WHERE     ([LOG].line_date BETWEEN [Start Date] AND [End Date]) 
GROUP BY [Start Date], [End Date], [Display Start Date], [Display End Date], [LABEL]

Answer 1

If I understand you correctly you could try something like

DECLARE @DateRanges TABLE(
        StartDate DATETIME,
        EndDate DATETIME
)

INSERT INTO @DateRanges (StartDate,EndDate) SELECT '12/02/2010 10:00:00','12/02/2010 10:59:59' 
INSERT INTO @DateRanges (StartDate,EndDate) SELECT '12/02/2010 11:00:00','12/02/2010 11:59:59' 
INSERT INTO @DateRanges (StartDate,EndDate) SELECT '12/02/2010 12:00:00','12/02/2010 12:59:59'

DECLARE @DateValues TABLE(
        DateVal DATETIME
)

INSERT INTO @DateValues (DateVal) SELECT '12/02/2010 11:00:00'
INSERT INTO @DateValues (DateVal) SELECT '12/02/2010 11:01:00'
INSERT INTO @DateValues (DateVal) SELECT '12/02/2010 12:01:00'

SELECT  t.StartDate,
        t.EndDate,
        COUNT(tv.DateVal) CountVal
FROM    @DateRanges t LEFT JOIN
        @DateValues tv ON tv.DateVal BETWEEN t.StartDate AND t.EndDate
GROUP BY    t.StartDate,
            t.EndDate

Output

StartDate               EndDate                 CountVal
----------------------- ----------------------- -----------
2010-12-02 10:00:00.000 2010-12-02 10:59:59.000 0
2010-12-02 11:00:00.000 2010-12-02 11:59:59.000 2
2010-12-02 12:00:00.000 2010-12-02 12:59:59.000 1

Answer 2

Ok, a little pokery and i realised i made a slight issue for my self:

The issue is the WHERE clause and its position to the COUNT clause. If i'm using WHERE & count on the same result set then i get nothing for zero rows between dates. IF, however, i count everything and omit the WHERE from the same result set and place the WHERE clause in the JOIN i.e [x] left join (select [a] from [b] where [a] between @x & @y) as [c] i return all the rows, count then litrally counts.

I think the issue was the WHERE clause previous ommited the count select because there was no action to take (according to the compiler)