在SQL Server中将单列日期列表转换为多个日期范围

SELECT 
    date_column,
    DATEDIFF(DAY, LAG(date_column) OVER (ORDER BY date_column), date_column) AS date_diff
FROM 
    dates

SELECT 
    date_column,
    SUM(date_diff) OVER (ORDER BY date_column) AS date_range
FROM 
    (
        SELECT 
            date_column,
            DATEDIFF(DAY, LAG(date_column) OVER (ORDER BY date_column), date_column) AS date_diff
        FROM 
            dates
    ) AS t

SELECT 
    MIN(date_column) AS start_date,
    MAX(date_column) AS end_date
FROM 
    (
        SELECT 
            date_column,
            SUM(date_diff) OVER (ORDER BY date_column) AS date_range
        FROM 
            (
                SELECT 
                    date_column,
                    DATEDIFF(DAY, LAG(date_column) OVER (ORDER BY date_column), date_column) AS date_diff
                FROM 
                    dates
            ) AS t
    ) AS t2
GROUP BY 
    date_range