How to determine global and local mins and maxs in sql server 2008?

Question

I am trying to figure out the best way to determine global and local mins and maxs for a data set. I think there has got to be an easy way to do this but I cannot figure it out.

I am using Sql Server 2008.

Lets say I have a data set of subscription dates for users.

Start Date      Stop Date       PersonID
12/31/2007      3/31/2008      1
3/31/2008       6/30/2008      1
6/30/2008       9/30/2008      1
2/28/2008       3/31/2008      2
3/31/2008       4/30/2008      2
7/31/2008       8/31/2008      2
5/31/2008       6/30/2008      3

I know some of these dates are quarterly and some are monthly.

The end result should be:

StartDate      StopDate      PersonID
12/31/2007     9/30/2008     1
2/28/2008      4/30/2008     2
7/31/2008      8/31/2008     2
5/31/2008      6/30/2008     3

I just want to find all the possible contiguous segments and do this without looping or a cursor.

Any ideas?

Answer 1

Have you try grouping the data by PersonID and then creating a sum based on that?

Answer 2

If you don't mind using a CTE, following script produces the results you are after.

Testdata

DECLARE @Users TABLE (
  PersonID INTEGER
  , StartDate DATETIME
  , StopDate DATETIME
)

INSERT INTO @Users
SELECT           1, '12-31-2007', '3-31-2008'
UNION ALL SELECT 1, '3-31-2008','6-30-2008'
UNION ALL SELECT 1, '6-30-2008','9-30-2008'
UNION ALL SELECT 2, '2-28-2008','3-31-2008'
UNION ALL SELECT 2, '3-31-2008','4-30-2008'
UNION ALL SELECT 2, '7-31-2008','8-31-2008'
UNION ALL SELECT 3, '5-31-2008','6-30-2008'

SQL Statement

;WITH q AS (
    SELECT  PersonID, StartDate, StopDate
    FROM    @Users
    UNION ALL 
    SELECT  u.PersonId, q.StartDate, u.StopDate
    FROM    q
            INNER JOIN @Users u ON u.PersonID = q.PersonID
                                   AND u.StartDate = q.StopDate
)
SELECT  PersonID, MIN(StartDate), StopDate
FROM    (
          SELECT  PersonID, StartDate, [StopDate] = MAX(StopDate)
          FROM    q
          GROUP BY
                  PersonID, StartDate
        ) p
GROUP BY
        PersonID, StopDate                          
ORDER BY
        PersonID, 2, 3