从MS中的日期记录中获取日期范围

Question

我有约会记录

with DateTable (dateItem) as 
(  
  select  '2022-07-03'  union all
  select  '2022-07-05'  union all
  select  '2022-07-04'  union all
  select  '2022-07-09'  union all
  select  '2022-07-12'  union all
  select  '2022-07-13'  union all
  select  '2022-07-18' 
)
select dateItem  
from DateTable 
order by 1 asc

我想得到这样的记录之间的日期范围

with DateTableRange (dateItemStart, dateItemend) as 
(  
  select  '2022-07-03','2022-07-05'  union all
  select  '2022-07-09','2022-07-09'  union all
  select  '2022-07-12','2022-07-13'  union all
  select  '2022-07-18','2022-07-18' 
 
)
select dateItemStart, dateItemend  
from DateTableRange 

我可以通过循环使用while或循环在SQL中执行，方法是获取第一个日期并检查下一个日期，如果它们是1+，则将其添加到结束日期中，并在循环中执行相同的操作。

但我不知道最佳或优化的方法是什么，因为有很多循环和临时表涉及编辑:在数据中，我们有3,4,5和6,7,8缺少，所以范围是3-5。

9已存在，10缺失，因此范围为9-9，因此范围完全取决于数据表中的连续数据。

如有任何建议，将不胜感激

Answer 1

经过澄清后，这绝对是一个“空隙和岛屿”问题。

解决办法可以是这样的

WITH DateTable(dateItem) AS
(  
    SELECT * FROM (
    VALUES 
        ('2022-07-03'),
        ('2022-07-05'),
        ('2022-07-04'),
        ('2022-07-09'),
        ('2022-07-12'),
        ('2022-07-13'),
        ('2022-07-18')
    ) t(v)
)
SELECT
    MIN(dateItem) AS range_from,
    MAX(dateItem)  AS range_to
FROM (
    SELECT
        *,
        SUM(CASE WHEN DATEADD(day, 1, prev_dateItem) >= dateItem THEN 0 ELSE 1 END) OVER (ORDER BY rn) AS range_id
    FROM (
        SELECT
            ROW_NUMBER() OVER (ORDER BY dateItem) AS rn,
            CAST(dateItem AS date) AS dateItem,
            CAST(LAG(dateItem) OVER (ORDER BY dateItem) AS date) AS prev_dateItem
        FROM DateTable
    ) groups
) islands
GROUP BY range_id

您可以检查一个工作的演示

Answer 2

有了一些额外的清晰性，这就需要一种间隙和岛屿方法来首先将相邻的行识别为组，然后您可以使用一个窗口来标识每个组的第一个和最后一个值。

我相信这一点还可以进一步完善，但应该会给出你想要的结果：

 with DateTable (dateItem) as 
 (  
  select  '2022-07-03'  union all
  select  '2022-07-05'  union all
  select  '2022-07-04'  union all
  select  '2022-07-09'  union all
  select  '2022-07-12'  union all
  select  '2022-07-13'  union all
  select  '2022-07-18' 
), valid as (
   select *,  
     case when exists (
       select * from DateTable d2 where Abs(DateDiff(day, d.dateitem, d2.dateitem)) = 1
     ) then 1 else 0 end v
  from DateTable d
  ), grp as (
    select *,
      Row_Number() over(order by dateitem) - Row_Number() 
        over (partition by v order by dateitem) g
    from Valid v
  )
select distinct
  Iif(v = 0, dateitem, First_Value(dateitem) over(partition by g order by dateitem)) DateItemStart, 
  Iif(v = 0, dateitem, First_Value(dateitem) over(partition by g order by dateitem desc)) DateItemEnd
from grp
order by dateItemStart;

请参阅演示Fiddle