SQL Server query to group sequential date data

Question

I have got a bit of 'brain fade' going on this afternoon, so if anyone can help with this mssql query it would be fantastic.

I have a table called 'seasons' with three columns (there are more but not relevant to the example): seasonId, date, tariffId

SeasonId is a unique key. A date can only have one tariffid, but a tariffId can have many different dates.

For example:

seasonId, date,      tariffId
1        1 jan 2009  1
2        2 jan 2009  1
3        3 jan 2009  2
4        4 jan 2009  3
5        5 jan 2009  3

I'd like to have a query return the sequence/range of dates against a particular tariffId

Eg using the data above, it would return the following:

FromDate,   ToDate,     TariffId
1 Jan 2009  2 Jan 2009  1
3 Jan 2009  3 Jan 2009  2
4 Jan 2009  5 Jan 2009  3

Is this possible?

EDIT Thanks for all the answers so far! I am always amazed how far you get a response!

However, my example data probably wasn't complex enough as a tariff can have 1 or more date ranges

seasonId, date,      tariffId
1        1 jan 2009  1
2        2 jan 2009  1
3        3 jan 2009  2
4        4 jan 2009  3
5        5 jan 2009  3
6        6 jan 2009  1
7        7 jan 2009  1
8        8 jan 2009  3

Would give:

FromDate,   ToDate,     TariffId
1 Jan 2009  2 Jan 2009  1
3 Jan 2009  3 Jan 2009  2
4 Jan 2009  5 Jan 2009  3
6 Jan 2009  7 Jan 2009  1
8 Jan 2009  8 Jan 2009  3

Ideas?

Thanks everyone for their help on this! This site is AWESOME!

Answer 1

Maybe this?

select min(fromdate) as FromDate, max(todate) as ToDate, tarifid
from (

select min(date) as fromdate, null as todate, tarifid
from seasons
group by tarifid

union

select null, max(date), tarifid
from seasons
group by tarifid

) q
group by tarifid

Answer 2

SELECT min(date) as FromDate, MAX(date) as ToDate, tarifid
FROM seasons
GROUP BY tarifID

should do it.

Answer 3

It looks like you find the sequential occurrences of tariffId and then find the minimum and maximum values of Date in that sequential occurrence. The following works for your sample data but I suspect the final join needs some tweaking as it feels smelly.

SELECT MinValues.Seasonid, MinValues.Date, MaxValues.Date, MaxValues.tariffid 
FROM (
    SELECT * 
      FROM [dbo].[Seasons] tbl1
     WHERE NOT EXISTS (SELECT * 
                         FROM [dbo].[Seasons] tbl2 
                        WHERE tbl1.seasonid - tbl2.seasonid = 1 
                          AND tbl1.tariffId = tbl2.tariffId)) as minValues
JOIN (
     SELECT * 
       FROM [dbo].[Seasons] tbl1
      WHERE NOT EXISTS (SELECT *
                          FROM [dbo].[Seasons] tbl2 
                         WHERE tbl2.seasonid - tbl1.seasonid = 1 
                           AND tbl1.tariffId = tbl2.tariffId)) as maxValues
ON MinValues.TariffId = MaxValues.tariffId
AND (MinValues.SeasonId = MaxValues.Seasonid or MinValues.SeasonId +1 = MaxValues.Seasonid)

Answer 4

First some test data:

create table seasons (seasonId int primary key
    , "date" datetime not null unique
    , tariffId int not null)

insert into seasons values (1, '2009-01-01', 1)
insert into seasons values (2, '2009-01-02', 1)
insert into seasons values (3, '2009-01-03', 2)
insert into seasons values (4, '2009-01-04', 3)
insert into seasons values (5, '2009-01-05', 3)
insert into seasons values (6, '2009-01-06', 1)
insert into seasons values (7, '2009-01-07', 1)
insert into seasons values (8, '2009-01-08', 3)
-- add a tarrif with a datespan larger than 2
insert into seasons values (9, '2009-01-09', 4)
insert into seasons values (10, '2009-01-10', 4)
insert into seasons values (11, '2009-01-11', 4)

Building on Dave Barker's answer, within the inline views add row_number() so we know which is the first min values, which is the second, etc. by tariffId. (Actually since a date can't have more than one tariffId, we don't need to partition by tariffId.)

SELECT MinValues.Seasonid, MinValues.Date, MaxValues.Date, MaxValues.tariffid 
FROM (
    SELECT *, row_number() over (partition by tariffId order by "date") as RN 
      FROM [dbo].[Seasons] tbl1
     WHERE NOT EXISTS (SELECT * 
                         FROM [dbo].[Seasons] tbl2 
                        WHERE tbl1.seasonid - tbl2.seasonid = 1 
                          AND tbl1.tariffId = tbl2.tariffId)) as minValues
JOIN (
     SELECT *, row_number() over (partition by tariffId order by "date") as RN
       FROM [dbo].[Seasons] tbl1
      WHERE NOT EXISTS (SELECT *
                          FROM [dbo].[Seasons] tbl2 
                         WHERE tbl2.seasonid - tbl1.seasonid = 1 
                           AND tbl1.tariffId = tbl2.tariffId)) as maxValues
ON MinValues.TariffId = MaxValues.tariffId
and MinValues.RN = MaxValues.RN
order by MinValues.Date

Results:

1   2009-01-01 00:00:00.000 2009-01-02 00:00:00.000 1
3   2009-01-03 00:00:00.000 2009-01-03 00:00:00.000 2
4   2009-01-04 00:00:00.000 2009-01-05 00:00:00.000 3
6   2009-01-06 00:00:00.000 2009-01-07 00:00:00.000 1
8   2009-01-08 00:00:00.000 2009-01-08 00:00:00.000 3
9   2009-01-09 00:00:00.000 2009-01-11 00:00:00.000 4