Eric Ness Eric Ness - 6 months ago 20
SQL Question

Combining split date ranges in a SQL query

I'm working on a query that needs to have some data rows combined based on date ranges. These rows are duplicated in all the data values, except the date ranges are split. For example the table data may look like

StudentID StartDate EndDate Field1 Field2
1 9/3/2007 10/20/2007 3 True
1 10/21/2007 6/12/2008 3 True
2 10/10/2007 3/20/2008 4 False
3 9/3/2007 11/3/2007 8 True
3 12/15/2007 6/12/2008 8 True


The result of the query should have the split date ranges combined. The query should combine date ranges with a gap of only one day. If there is more than a one day gap, then the rows shouldn't be combined. The rows that don't have a split date range should come through unchanged. The result would look like

StudentID StartDate EndDate Field1 Field2
1 9/3/2007 6/12/2008 3 True
2 10/10/2007 3/20/2008 4 False
3 9/3/2007 11/3/2007 8 True
3 12/15/2007 6/12/2008 8 True


What would be the SELECT statement for this query?

Answer

The following code should work. I've made a few assumptions as follows: there are no overlaps of date ranges, there are no NULL values in any of the fields, and the start date for a given row is always less than the end date. If your data doesn't fit these criteria, you'll need to adjust this method, but it should point you in the right direction.

You can use subqueries instead of the views, but that can be cumbersome so I used the views to make the code clearer.

CREATE VIEW dbo.StudentStartDates
AS
    SELECT
    	S.StudentID,
    	S.StartDate,
    	S.Field1,
    	S.Field2
    FROM
    	dbo.Students S
    LEFT OUTER JOIN dbo.Students PREV ON
    	PREV.StudentID = S.StudentID AND
    	PREV.Field1 = S.Field1 AND
    	PREV.Field2 = S.Field2 AND
    	PREV.EndDate = DATEADD(dy, -1, S.StartDate)
    WHERE PREV.StudentID IS NULL
GO

CREATE VIEW dbo.StudentEndDates
AS
    SELECT
    	S.StudentID,
    	S.EndDate,
    	S.Field1,
    	S.Field2
    FROM
    	dbo.Students S
    LEFT OUTER JOIN dbo.Students NEXT ON
    	NEXT.StudentID = S.StudentID AND
    	NEXT.Field1 = S.Field1 AND
    	NEXT.Field2 = S.Field2 AND
    	NEXT.StartDate = DATEADD(dy, 1, S.EndDate)
    WHERE NEXT.StudentID IS NULL
GO


SELECT
    SD.StudentID,
    SD.StartDate,
    ED.EndDate,
    SD.Field1,
    SD.Field2
FROM
    dbo.StudentStartDates SD
INNER JOIN dbo.StudentEndDates ED ON
    ED.StudentID = SD.StudentID AND
    ED.Field1 = SD.Field1 AND
    ED.Field2 = SD.Field2 AND
    ED.EndDate > SD.StartDate AND
    NOT EXISTS (SELECT * FROM dbo.StudentEndDates ED2 WHERE ED2.StudentID = SD.StudentID AND ED2.Field1 = SD.Field1 AND ED2.Field2 = SD.Field2 AND ED2.EndDate < ED.EndDate AND ED2.EndDate > SD.StartDate)
GO
Comments