在SQL查询中组合分割日期范围 [英] 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

这个查询的SELECT语句是什么？

What would be the SELECT statement for this query?

推荐答案

以下代码应该可以正常工作。我做了一些假设如下：没有重叠的日期范围，在任何字段中没有NULL值，并且给定行的开始日期总是小于结束日期。如果您的数据不符合这些条件，您需要调整此方法，但它应该指向正确的方向。

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

这篇关于在SQL查询中组合分割日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！