Get range of dates from dates record in MS SQL-CodePudding

I have dates record

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

I want to get ranges of dates between this record like this

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

I am able to do it in SQL with looping using while or looping by getting first one and check the next dates and if they are 1 plus then I add it in enddate and do the same in loop

But I don't know what the best or optimized way is, as there were lots of looping and temp tables involve Edited : as in data we have 3,4,5 and 6,7,8 is missing so range is 3-5

9 exist and 10 is missing so range is 9-9 so ranges is purely depend on the consecutive data in datetable

Any suggestion will be appreciated

CodePudding user response：

With some additional clarity this requires a gaps-and-islands approach to first identify adjacent rows as groups, from which you can then use a window to identify the first and last value of each group.

I'm sure this could be refined further but should give your desired results:

 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;

See Demo Fiddle

CodePudding user response：

After clarification, this is definitely a 'gaps and islands' problem.

The solution can be like this

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

You can check a working demo