Select most recent records for a recent mutliple dates

1

I need help with my query to pull most recent date that employee has promoted during the year. I only get one most recent date not multiple dates during the year. I don't know how to get it works. Here's my query and the output return from this query and THE OUTPUT I WANT below. Thank you very much in advance.

SELECT a.EMPLOYEE, a.LAST_NAME, a.FIRST_NAME, b.Effective_Date, b.SCHEDULE, b.PAY_GRADE, b.PAY_STEP, b.PAY_RATE
FROM EMPLOYEE a right outer join (select MAX(EFFECT_DATE) as Effective_Date, SCHEDULE, PAY_GRADE, PAY_STEP, PAY_RATE from PRSAGDTL
group by EFFECT_DATE, SCHEDULE, PAY_GRADE, PAY_STEP, PAY_RATE) b
on a.SCHEDULE = b.SCHEDULE and a.PAY_GRADE = b.PAY_GRADE and a.PAY_STEP = b.PAY_STEP
where a.EMP_STATUS = 'A1
RESULT COME BACK FROM THIS QUERY IS
EmployID EffectiveDate LastName FirstName Schedule PayStep PayGrade PayRate
1105 01/05/2015 Hill Harvey PD 9 P1 34.95
1105 01/04/2016 Hill Harvey PD 9 P1 36.52
1105 01/02/2017 Hill Harvey PD 9 P1 38.20
1105 02/12/2018 Hill Harvey PD 9 P1 39.95
1105 07/01/2018 Hill Harvey PD 9 P1 41.80
1105 12/20/2018 Hill Harvey PD 9 P1 43.50

I WANT THE OUTPUT LIKE THIS
EmployeeID EffectiveDate LastName FirstName Schedule PayStep PayGrade PayRate
1105 02/12/2018 Hill Harvey PD 9 P1 39.95
1105 07/01/2018 Hill Harvey PD 9 P1 41.80
1105 12/20/2018 Hill Harvey PD 9 P1 43.50

2

Your data and output don't match your request. You want the the most recent promoted date, but then you list 3 dates for the year. Based on the data, the output should be the last row

1105 12/20/2018 Hill Harvey PD 9 P1 43.50

Below is your data and results that you asked for, but I'm not sure that's what you really want

drop table if exists #t
go
create table #t (
EmployID int,
EffectiveDate date, 
LastName varchar(10),
FirstName  varchar(10),
Schedule varchar(2),
PayStep  tinyint,
PayGrade varchar(2),
PayRate numeric(10,2)
) 
insert into #t values
 (1105,'01/05/2015','Hill','Harvey','PD',9,'P1',34.95)
,(1105,'01/04/2016','Hill','Harvey','PD',9,'P1',36.52)
,(1105,'01/02/2017','Hill','Harvey','PD',9,'P1',38.20)
,(1105,'02/12/2018','Hill','Harvey','PD',9,'P1',39.95)
,(1105,'07/01/2018','Hill','Harvey','PD',9,'P1',41.80)
,(1105,'12/20/2018','Hill','Harvey','PD',9,'P1',43.50)

select * from #t
where EffectiveDate >= '1/1/2018'
3

I can't do temp table because we have a lot of records and effective date, usually happened on 2/12 every year and sometime they do have promotion during the year. I have tried what you said, but that is not what I want. I need to do dynamic and don't know how. Thank you very much for your response.

4

I used a temp table because you didn't supply any ddl or sample data. You need to take the code I provided and apply it to your specific situation. If you provide ddl, sample data and expected results, then we can help. Not sure what you mean by dynamic, you didn't answer my question about recent promoted date

1 Like