Geek asks and answers

SQL Logic & Cross Apply Query Error

Given the start date and end date, I need to count instances between these two dates. Therefore, the following is given:

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 looked between the 1st (01/01) and the 2nd (02/01), I would expect a count of 2. If I were looking for a third or fourth, I would expect a count of 3. If I looked at the entire date range then I would expect a count of 4. Do you make sense?

NOTE. Dates are already converted at midnight, for this you do not need to add a code. In addition, dates are in dd / MM / yyyy format throughout this issue.

I currently 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

At some point, I thought it was right, but now I'm not sure ...

I used to 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'm not sure that I am using CROSS APPLY correctly in this case ...

Questions:

1) Cross Cross ( CTE, )?
2) , ? ( , )

/:)

+3
2

:

WHERE [Date] BETWEEN Start_Date AND DATEADD(Day, -1, End_Date)
0

, , <= > =.

, .

+3

More articles:


All Articles