Service Dates

Here's the problem:


We have a table of services for our clients (about 2 million rows).
The rows are simply the client's ID and the date of service. Rows may
dupe as clients may have more than one service per date.


We need to be able to define a case start and end date for each client
ID. The end date is defined as the last service date with no further
activity within some number (usually 180) of days. Furthermore, I'd
need to enumerate the cases per client.


So using the example below, I'd be looking for results such as:


Client Start End Case
55577 2/01/2004 5/11/2004 1
55577 1/09/2005 1/09/2005 2
55577 3/04/2006 OPEN 3
72395 4/04/2006 OPEN 1


In these cases, the OPEN dates indicate that there has not been a 180
day period of inactivity since the most recent date.

Solution #1 (me):

SELECT DISTINCT ClientID, ServDate AS Start
, (
SELECT MIN(s1.ServDate)
FROM #ServList s1
WHERE s1.ClientID = s.ClientID
AND s1.ServDate >= s.ServDate
---------- instead of getdate(), use last midnight
AND s1.ServDate < DATEADD(DAY, -179, GETDATE())
AND NOT EXISTS(SELECT 1 FROM #ServList s2 WHERE s2.ClientID =
s1.ClientID AND s2.ServDate > s1.ServDate AND s2.ServDate <=
DATEADD(DAY, 179, s1.ServDate))
)
FROM #ServList s
WHERE NOT EXISTS(SELECT 1 FROM #ServList s1 WHERE s1.ClientID =
s.ClientID AND s1.ServDate >= DATEADD(DAY, -179, s.ServDate) AND
s1.ServDate < s.ServDate)

Solution #2