Given a start date and an end date, I need a count of instances between those two dates. So given the following:
Table:
Col 1 Start_Date End_Date
1 01/01/2010 02/01/2010
2 01/01/2010 04/01/2010
3 03/01/2010 04/01/2010
4 03/01/2010 04/01/2010
If I was looking between the 1st (01/01) and the 2nd (02/01) I would expect a count of 2. If I was looking for the 3rd to the 4th I would expect a count of 3. If I was looking across the whole date range then I would expect a count of 4. Make sense?
NOTE: The dates are already converted to midnight, no code needs to be added for this. Also, dates are in dd/MM/yyyy format throughout this question.
Currently I have something similar to the following:
SELECT COUNT(*), Group_Field
FROM MY_Table
WHERE Start_Date < DATEADD(DAY, 1, @StartDate) AND End_Date > @EndDate
GROUP BY Group_Field
I did at some point think that this was right, but i'm not convinced now...
I did previously have:
WITH Dates AS (
SELECT [Date] = @StartDate
UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
FROM Dates WHERE [Date] < @EndDate
)
SELECT COUNT(*), Group_Field -- In this case it is [Date]
FROM MY_Table
CROSS APPLY Dates
WHERE Start_Date < DATEADD(DAY, 1, @StartDate) AND End_Date > [Date]
GROUP BY Group_Field
But I am not sure that I am using CROSS APPLY properly in this case...
The questions:
1) Am I using Cross Apply right in the 2nd example (and the CTE for that matter)?
2) If so, which logic is right? (I think it's the 2nd)
/Discuss :)
The solution ended up being:
WHERE [Date] BETWEEN Start_Date AND DATEADD(Day, -1, End_Date)