CODEWITHSUNDEEP
sql-serverdatemaxminsequential
Jason Shaw
Jason Shaw

Reputation: 33

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

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:

enter image description here

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

enter image description here

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

enter image description here

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

Regards

Jason

Upvotes: 0

Views: 863

Answers (1)

Becuzz
Becuzz

Reputation: 6866

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.

Upvotes: 1

Related Questions