SQL - How can I Group sets of sequential numbers and return the MIN and Max Dates

Question

this is driving me crazy! does anyone know how to write some SQL that will return the MIN and MAX dates from groups of sequential numbers? please see screen shots below.

This is the SQL I used:

SELECT
num
, empid
, orderdate

FROM
(SELECT
 ROW_NUMBER() OVER (ORDER BY orderdate) AS Num
 , empid
 , orderdate

 FROM TSQL.Sales.Orders)T1

 WHERE empid = 4

This is what it returns:

What I would like to do is get the Min and Max dates for each set of sequential numbers based on the num column. For example: the first set would be num 3, 4, 5 & 6. so the Min date is 2006-07-08 and the Max date is 2006-07-10

See example of results needed below

Any help with this would be much appreciated, thank you in advance

Update

I have now changed the SQL to do what I needed: example as follows:

  Select 
  empid
  , Island
  , MIN(orderdate) as 'From'
  , Max(orderdate) as 'To'
      From 
      (select 
        empid
        , num
        , num - ROW_NUMBER() OVER (ORDER BY num, orderdate) as Island
        , orderdate
from 
(Select 
 ROW_NUMBER() OVER (ORDER BY orderdate) as Num
 , empid
 , orderdate

 from TSQL.Sales.Orders)T1
  where empid = 4 
  )T2

  group By 
  empid
  , Island

Result

Thank you so much for your help on this, I have been trying this for ages

Regards

Jason

If you google "Gaps and Islands problem in SQL" you will find lots of examples of how to handle this. — Tab Alleman
Thank you for this, I have just looked at "Gaps and Islands problem in SQL" and have now been able to group each Island (sequential number set) and return MIN and MAX dates for each. Thank you for your help with this. — Jason Shaw

Becuzz Becuzz · Accepted Answer · 2015-12-01T20:24:08

This should do it:

;with dateSequences(num, empId, orderDate) as
(
    select ROW_NUMBER() over (order by orderdate) as num
        , empId
        , orderdate
    from yourTable
),
dateGroups(groupNum, empId, orderDate, num) as
(
    select currD.num, currD.empid, currD.orderDate, currD.num
    from dateSequences currD
    left join dateSequences prevD on prevD.num = currD.num - 1 and prevD.empid = currD.empId
    where prevD.num is null

    union all

    select dg.groupNum, d.empId, d.orderDate, d.num
    from dateSequences d
    inner join dateGroups dg on dg.num + 1 = d.num and d.empId = dg.empId
)
select empId, min(orderDate) as MinDate, max(orderDate) as MaxDate
from dateGroups
where empId = 4
group by empId, groupNum

Basically it first makes a CTE to get the row numbers for each row in date order. Then it makes a recursive CTE that first finds all the groups with no previous sequential entries then adds all subsequent entries to the same group. Finally it takes the records with all the group numbers assigned and groups them by their group number and gets the min and max dates.

SQL - How can I Group sets of sequential numbers and return the MIN and Max Dates

1 Answers