How to determine each specific date range from one row to the next.

[WoodyGuthrie]

I need to determine the date range(s) for each given employee from the entry date of the status RC to the entry date of the next status change AND then exclude all attributes (paydates, pay, etc.) for those dates from the range of pay dates returned for that employee based on the input pay-date-range parameter. Thanks for the help.

Status Entry Date
Leave 3/1/2006
RC 3/2/2006
Offer 3/3/2006
RC 3/3/2006
Contract 3/9/2006
RC 3/17/2006
Regular 9/28/2006
RC 5/19/2008
Regular 6/12/2008
RC 10/17/2008


Woody

 

[bborissov]

DECLARE @Temp TABLE (Status varchar(10), EntryDate datetime, Id int IDENTITY(1,1))

INSERT INTO @Temp (Status, EntryDate)
SELECT Status, EntryDate 
FROM YourTable
WHERE Status = 'RC'
ORDER BY EntryDate


---- Now Get date ranges:

SELECT Tst.Status, 
       Tst.EntryDate  AS StartDate,
       Tst1.EntryDate AS EndDate
FROM @Temp Tst
LEFT JOIN @Temp Tst1 ON Tst1.Id = Tst.Id+1
-- If you don't want the last period just
-- change LEFT JOIN to INNER JOIN

The you could use the last query as a derived table to other query where you can get what you want.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[WoodyGuthrie]

Thanks for the reply. Sorry, I should've included the possibility that there could be two or more status changes between the RC status. Like below.

RC 5/19/2008
Regular 6/12/2008
RC 10/17/2008
Regular 11/01/2008
Leave 12/09/2008
RC 01/05/2009

Woody