CODEWITHSUNDEEP
sql-server-2008group-by
Kerry
Kerry

Reputation: 51

Need last value per Year and previous value if null for year

I have employees that change rates each year, sometimes several times in a year. Trying to find last rate for year and if there was not rate changes for the year, then to pull the value for the previous year. I have gotten the last record for the year, but having trouble using previous value for year that did not have a change. Ideally I will have a result for each year. But in saying that, I realize that could get out of control quickly. So if there is a rate for 2008 and 2010, but not for 2009, I need 2008's rate to show up for 2009. Using SQL Server 2008 R2 Considered doing a Cross or outer Apply with a Calendar, but I keep getting turned around. I have already created the calendar, using Todd Fifield post and just adding in Yr with Year value. http://www.sqlservercentral.com/articles/T-SQL/70482/

This is what I have so far

with YrHist as
(
select h.HRCo, h.HRRef, h.EffectiveDate, YEAR(h.EffectiveDate) as EffectiveYr,DATEADD(m,DATEDIFF(m,0,h.EffectiveDate),0) as EffMth, h.Type, h.NewSalary, h.OldSalary

from HRSH h
        left outer join PREHName p on h.HRCo=p.PRCo and h.HRRef=p.Employee
)

select distinct ROW_NUMBER() over(Order By tt.HRCo,tt.HRRef,tt.EffectiveYr) as RowID,
tt.HRCo, tt.HRRef,tt.EffMth, tt.EffectiveYr, tt.EffectiveDate, tt.Type, tt.NewSalary as MaxRateForYr, tt.OldSalary

from YrHist as tt

        inner join
            (select HRCo, HRRef, EffectiveYr, MAX(EffectiveDate) as MaxEffDate
            from YrHist
            Group By HRCo, HRRef, EffectiveYr) as m on tt.EffectiveYr=m.EffectiveYr and tt.EffectiveDate=m.MaxEffDate and tt.HRCo=m.HRCo and tt.HRRef=m.HRRef

Example of Results

Upvotes: 0

Views: 79

Answers (2)

Kerry
Kerry

Reputation: 51

Ended up changing tables; instead of looking at table that kept salary changes, I used table for check history and used max date for each year.

Upvotes: 1

FLICKER
FLICKER

Reputation: 6693

You can use this example and solve your problem

Assume you have a calendar table which includes all years

create table #cal (Yr int)
insert into #cal values (2010), (2011), (2012), (2013), (2014), (2015), (2016)

and assume this is the table you have the rates for each year

with x as (
  select 1 AS ID, 2010 AS Y, 58 AS Rate
  union
  select 2, 2011, 48
  union
  select 3, 2013, 38
  union
  select 4, 2013, 59
  union
  select 5, 2014, 68
  union
  select 6, 2014, 78
  union
  select 7, 2014, 56
  union
  select 8, 2016, 45
)
select * into #t from x

Then this query will give you the result you are looking for 

select Yr
, isnull(max(cc.Rate), (select top 1 Rate from #t where Y < Yr order by ID desc)) Rate
from #cal
    left join #t ot on #cal.Yr = ot.Y
    outer apply (select top 1 ID, Rate from #t where Y = ot.Y order by ID desc) AS cc
group by #cal.Yr

Result

╔══════╦══════╗
║ Yr   ║ Rate ║
╠══════╬══════╣
║ 2010 ║ 58   ║
╠══════╬══════╣
║ 2011 ║ 48   ║
╠══════╬══════╣
║ 2012 ║ 48   ║
╠══════╬══════╣
║ 2013 ║ 59   ║
╠══════╬══════╣
║ 2014 ║ 56   ║
╠══════╬══════╣
║ 2015 ║ 56   ║
╠══════╬══════╣
║ 2016 ║ 45   ║
╚══════╩══════╝

Upvotes: 0

Related Questions