Start Date 1

[mdtimo]

I have a salary table that has a record for every person for every payrun. Even if your salary never changes a new record is posted to this table each payrun with the effective date of that payroll

I want to return the records from this table so that just one record per salary per person with a date denoting when the salary started.

But there is one caveat. If your salary changes then reverts back to the old salary I want two records for that salary for that person.

Sample Data in the table
EID Start_Date Salary
8682 11/6/2001 50000
8682 12/4/2001 50000
8682 12/18/2001 50000
8682 12/28/2001 52000
8682 1/7/2002 52000
8682 1/29/2002 50000

My desired results
EID Start_Date Salary
8682 11/6/2001 50000
8682 12/28/2001 52000
8682 1/29/2002 50000

My initial stab was

select EID,min(Start_Date),Salary from payruns
where EID='8682'
group by EID,Salary

I can't use min start date because the min start date will drop the return to 50000 salary.

It just returns:

My desired results
EID Start_Date Salary
8682 11/6/2001 50000
8682 12/28/2001 52000

Losing the fact the salary returned to 50000 in 2002.

Any thoughts?

 

[RiverGuy]

Try this out on a test database:

DECLARE @Salaries TABLE (
EID INT,
StartDate DATETIME,
Salary DECIMAL(18,2) )


INSERT INTO @Salaries SELECT 1234, '2008-01-01', 60000
INSERT INTO @Salaries SELECT 1234, '2008-02-01', 60000
INSERT INTO @Salaries SELECT 1234, '2008-03-01', 65000
INSERT INTO @Salaries SELECT 1234, '2008-04-01', 70000

INSERT INTO @Salaries SELECT 456, '2008-01-01', 20000
INSERT INTO @Salaries SELECT 456, '2008-02-01', 33000
INSERT INTO @Salaries SELECT 456, '2008-03-01', 33000
INSERT INTO @Salaries SELECT 456, '2008-04-01', 33000
INSERT INTO @Salaries SELECT 456, '2008-05-01', 44000
INSERT INTO @Salaries SELECT 456, '2008-06-01', 44000


SELECT a.*
FROM @Salaries a
WHERE 
	(EXISTS 
		(SELECT * FROM @Salaries b WHERE a.EID = b.EID AND a.Salary <> b.Salary AND b.StartDate = 
				(SELECT TOP 1 StartDate FROM @Salaries c WHERE StartDate < a.StartDate AND EID = a.EID ORDER BY StartDate DESC )))
OR
	(StartDate = (SELECT MIN(StartDate) FROM @Salaries d WHERE EID = a.EID))

 

[mdtimo]

That works!!!

Thanks.