Transact SQL teaser

Hi Gurus!
I need your immediate help with a brainteaser…
My problem:
I need a setbased SQL for loading a Data warehouse. I have a dataflow with
many rows per category. These rows should be grouped per date interval and ID
and category. min (from_date) and max (to_date) should be delivered as in the
result below.

I can easily handle this with a cursor, but that’s to slow…

What would you suggest?

Best, Jocke

The example:
ID Category from_date to_date
1691789 43 2004-09-01 00:00:00.000 2005-03-01 00:00:00.000
1691789 43 2005-03-01 00:00:00.000 2005-09-01 00:00:00.000
1691789 43 2005-09-01 00:00:00.000 2007-08-02 00:00:00.000
1691789 43 2007-08-02 00:00:00.000 2007-08-30 00:00:00.000
1691789 28 2007-08-30 00:00:00.000 2008-08-26 00:00:00.000
1691789 28 2008-08-26 00:00:00.000 2008-09-01 00:00:00.000
1691789 28 2008-09-01 00:00:00.000 2008-11-06 00:00:00.000
1691789 28 2008-11-06 00:00:00.000 2009-07-23 00:00:00.000
1691789 43 2009-07-23 00:00:00.000 2009-09-01 00:00:00.000
1691789 43 2009-09-01 00:00:00.000 2010-09-01 00:00:00.000
2222222 1 2008-08-26 00:00:00.000 2008-09-01 00:00:00.000


Should result in:
ID Category from_date to_date
1691789 43 2004-09-01 00:00:00.000 2007-08-30 00:00:00.000
1691789 28 2007-08-30 00:00:00.000 2009-07-23 00:00:00.000
1691789 43 2009-07-23 00:00:00.000 2010-09-01 00:00:00.000
2222222 1 2008-08-26 00:00:00.000 2008-09-01 00:00:00.000

Here is one set based solution:

SELECT id, category, from_date,
(SELECT MIN(to_date)
FROM Foo AS C
WHERE NOT EXISTS
(SELECT *
FROM Foo AS D
WHERE C.id = D.id
AND C.category = D.category
AND C.to_date >= DATEADD(DAY, -1, D.from_date)
AND C.to_date < D.to_date)
AND C.to_date >= A.from_date
AND C.id = A.id
AND C.category = A.category) AS to_date
FROM Foo AS A
WHERE NOT EXISTS
(SELECT *
FROM Foo AS B
WHERE A.id = B.id
AND A.category = B.category
AND A.from_date > B.from_date
AND A.from_date <= DATEADD(DAY, 1, B.to_date));

--
Plamen Ratchev
http://www.SQLStudio.com