SQL Server连续日期-将多行汇总为连续的开始和结束日期行，而没有CTE，循环，... s [英] SQL Server contiguous dates - summarizing multiple rows into contiguous start and end date rows without CTE's, loops,...s

问题描述

是否可以编写一个sql查询，将带有开始和结束日期的行汇总到具有连续的开始和结束日期的行中?

约束是它必须是常规sql，即不使用CTE，循环等，因为第三方工具只能使用sql语句以Select开头.

例如:

  ID StartDate EndDate1001，2018年1月1日，2018年1月4日1002，一月5-2018，一月13-20181003，一月14-2018，一月18-20181004，2018年1月25日，2018年2月5日 

所需的输出必须是:

  2018年1月1日至2018年1月18日2018年1月25日至2018年2月5日 

谢谢

解决方案

我希望以下SQL查询可以帮助您确定给定情况下的差距和涵盖日期

我没有使用

Is it possible to write an sql query that will summarize rows with start and end dates into rows that have contiguous start and end dates?

The constraint is that it has to be regular sql, i.e. no CTE's, loops and the like as a third party tool is used that only allows an sql statement to start with Select.

e.g.:

ID       StartDate         EndDate

1001,      Jan-1-2018,       Jan-04-2018
1002,      Jan-5-2018,       Jan-13-2018
1003,      Jan-14-2018,      Jan-18-2018 
1004,      Jan-25-2018,      Feb-05-2018 

The required output needs to be:

Jan-1-2018,      Jan-18-2018 
Jan-25-2018,     Feb-05-2018 

Thank you

解决方案

I hope following SQL query can help you to identify gaps and covered dates for given case

I did not use a CTE expression of a dates table function, etc On the other hand, I used a numbers table using master..spt_values to generate the dates table as the main table of a LEFT join You can create a numbers table or a dates table if it does not fit to your requirements

In the query, to catch changes between borders I used SQL LAG() function which enables me to compare with previous value of a column in a sorted list

select
    max(startdate) as startdate,
    max(enddate) as enddate
from (
    select 
        date,
        case when exist = 1 then date else null end as startdate,
        case when exist = 0 then dateadd(d,-1,date) else null end as enddate,
        ( row_number() over (order by date) + 1) / 2 as rn
    from (
        select date, exist, case when exist <> (lag(exist,1,'') over (order by date)) then 1 else 0 end as changed
        from (
            select 
                d.date,
                case when exists (select * from Periods where d.date between startdate and enddate) then 1 else 0 end as exist
            from (
                SELECT dateadd(dd,number,'20180101') date
                FROM master..spt_values
                 WHERE Type = 'P' and dateadd(dd,number,'20180101') <= '20180228'
            ) d 

        ) cte   
    ) tbl
    where changed = 1
) dates
group by rn

Here is the result

这篇关于SQL Server连续日期-将多行汇总为连续的开始和结束日期行，而没有CTE，循环，... s的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！