Select Most recent records with recent multiple dates in SQL query

mysqlquestion · January 9, 2019, 8:10pm

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

bitsmed · January 9, 2019, 11:07pm

The "quick and dirty trick" would be to:

SELECT TOP(1) WITH TIES
       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'
 ORDER BY ROW_NUMBER() OVER(PARTITION BY a.EMPLOYEE
                                        ,YEAR(b.Effective_Date)
                                ORDER BY b.Effective_Date DESC
                           )

mannesravya · January 10, 2019, 3:00am

Could you please provide ddl scripts

mysqlquestion · January 10, 2019, 3:08pm

I ran your query and it returns all effective date not only most recent date.

mysqlquestion · January 10, 2019, 3:19pm

Here's is my data in the table

EmployeeID LastName FirstName Effective 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1105 Hill Harvey 1165 James David 1165 James David 1165 James David 1165 James David 1165 James David 1165 James David 1165 James David 1165 James David 1165 James David 1165 James David 1165 James David 1165 James David 1165 James David 1169 Green Odis 1169 Green Odis 1169 Green Odis 1169 Green Odis 1169 Green Odis 1169 Green Odis Date Schedule PayGrade PayStep PayRate
00:00.0 PD P1 9 27.29
00:00.0 PD P1 9 27.29
00:00.0 PD P1 9 28.8288
00:00.0 PD P1 9 30.5412
00:00.0 PD P1 9 31.9096
00:00.0 PD P1 9 31.9096
00:00.0 PD P1 9 31.9096
00:00.0 PD P1 9 32.8669
00:00.0 PD P1 9 33.5242
00:00.0 PD P1 9 34.949
00:00.0 PD P1 9 36.5217
00:00.0 PD P1 9 38.2017
00:00.0 PD P1 9 39.959
00:00.0 FD2912 F3 2 21.98
00:00.0 FD2912 F3 2 21.98
00:00.0 FD2912 F3 2 23.3654
00:00.0 FD2912 F3 2 24.5103
00:00.0 FD2912 F3 2 26.0162
00:00.0 FD2912 F3 2 26.0162
00:00.0 FD2912 F3 2 26.0162
00:00.0 FD2912 F3 2 26.7967
00:00.0 FD2912 F3 2 27.3326
00:00.0 FD2912 F3 2 27.9476
00:00.0 FD2912 F3 2 28.9258
00:00.0 FD2912 F3 2 30.4299
00:00.0 FD2912 F3 2 31.9819
00:00.0 PD P1 9 27.29
00:00.0 PD P1 9 27.29
00:00.0 PD P1 9 28.8288
00:00.0 PD P1 9 30.5412
00:00.0 PD P1 9 31.9096
00:00.0 PD P1 9 31.9096

mysqlquestion · January 10, 2019, 3:21pm

I am not sure how to generate DDL script? can show me how to do it and send over to you

mysqlquestion · January 10, 2019, 3:23pm

Effective Date should be date like 2016-10-01 and so on. I am not sure why I copied and past in here it shown zero in the column.

mysqlquestion · January 10, 2019, 3:26pm

EMPLOYEE LAST_NAME FIRST_NAME Effective_Date SCHEDULE PAY_GRADE PAY_STEP PAY_RATE
1105 Hill Harvey 1/1/1950 PD P1 9 27.29
1105 Hill Harvey 10/11/2004 PD P1 9 27.29
1105 Hill Harvey 10/10/2005 PD P1 9 28.8288
1105 Hill Harvey 10/9/2006 PD P1 9 30.5412
1105 Hill Harvey 10/8/2007 PD P1 9 31.9096
1105 Hill Harvey 10/6/2008 PD P1 9 31.9096
1105 Hill Harvey 10/5/2009 PD P1 9 31.9096
1105 Hill Harvey 10/1/2012 PD P1 9 32.8669
1105 Hill Harvey 1/6/2014 PD P1 9 33.5242
1105 Hill Harvey 1/5/2015 PD P1 9 34.949
1105 Hill Harvey 1/4/2016 PD P1 9 36.5217
1105 Hill Harvey 1/2/2017 PD P1 9 38.2017
1105 Hill Harvey 2/12/2018 PD P1 9 39.959
1165 James David 1/1/1950 FD2912 F3 2 21.98
1165 James David 10/11/2004 FD2912 F3 2 21.98
1165 James David 10/10/2005 FD2912 F3 2 23.3654
1165 James David 10/9/2006 FD2912 F3 2 24.5103
1165 James David 10/8/2007 FD2912 F3 2 26.0162
1165 James David 10/6/2008 FD2912 F3 2 26.0162
1165 James David 10/5/2009 FD2912 F3 2 26.0162
1165 James David 10/1/2012 FD2912 F3 2 26.7967
1165 James David 1/6/2014 FD2912 F3 2 27.3326
1165 James David 1/5/2015 FD2912 F3 2 27.9476
1165 James David 1/4/2016 FD2912 F3 2 28.9258
1165 James David 1/2/2017 FD2912 F3 2 30.4299
1165 James David 2/12/2018 FD2912 F3 2 31.9819
1169 Green Odis 1/1/1950 PD P1 9 27.29
1169 Green Odis 10/11/2004 PD P1 9 27.29

bitsmed · January 10, 2019, 5:36pm

I may have misunderstood what you're trying to accomplish. The query I gave, will return most recent rows, each year, per employee.

Looking a your sample data and output again, I think you might be looking for all rows in the most recent year, per employee. This can be done by changing this:

 ORDER BY ROW_NUMBER() OVER(PARTITION BY a.EMPLOYEE
                                        ,YEAR(b.Effective_Date)
                                ORDER BY b.Effective_Date DESC
                           )

to this:

 ORDER BY ROW_NUMBER() OVER(PARTITION BY a.EMPLOYEE
                                ORDER BY YEAR(b.Effective_Date) DESC
                           )

in the query I gave you earlier.

If this still is not what you're looking for, I'm out of ideas as I (obviously) don't understand what your looking for.

yosiasz · January 10, 2019, 6:18pm

create table #booya(EMPLOYEE int,LAST_NAME varchar(50),
FIRST_NAME varchar(50), Effective_Date datetime,	
SCHEDULE varchar(50),PAY_GRADE varchar(50),PAY_STEP int ,PAY_RATE money)

insert into #booya
select 1105,	'Hill', 'Harvey', '1/1/1950',	'PD', 'P1', 9,	27.29 union
select 1105,	'Hill', 'Harvey', '10/11/2004',	'PD', 'P1', 9,	27.29
--etc

mike01 · January 10, 2019, 8:51pm

This whole post makes no sense. You say effective date should be 2016-10-01, but that date doesn't appear in any of your data. Why not start from the beginning? You sent the data in an earlier post. Use that to tell us what the expected results and don't use the description above because no one knows what and so on is but you

yosiasz · January 11, 2019, 2:01am

not sure if you are looking at all the promotion of the most recent year?

create table #sample (EmployID int,  EffectiveDate datetime, 
LastName varchar(50), FirstName varchar(50), Schedule varchar(50),
PayStep int, PayGrade varchar(50),PayRate money)

insert into #sample
select '1105', '01/05/2015', 'Hill', 'Harvey', 'PD', 9 ,'P1', 34.95 union 
select '1105', '01/04/2016', 'Hill', 'Harvey', 'PD', 9 ,'P1', 36.52 union 
select '1105', '01/02/2017', 'Hill', 'Harvey', 'PD', 9 ,'P1', 38.20 union 
select '1105', '02/12/2018', 'Hill', 'Harvey', 'PD', 9 ,'P1', 39.95 union 
select '1105', '07/01/2018', 'Hill', 'Harvey', 'PD', 9 ,'P1', 41.80 union 
select '1105', '12/20/2018', 'Hill', 'Harvey', 'PD', 9 ,'P1', 43.50  


select * 
 from #sample s
 where YEAR(s.EffectiveDate) = 
(select top 1 YEAR(b.EffectiveDate) 
    from #sample b   order by EffectiveDate desc)

drop table #sample