alter proc ddOtBonusCalc
-- ddOtBonusCalc 'bonus'
@Batch varchar(15)
as
--initial data pull
--use the open BONUS batch to provide the date ranges ON A PER EMPLOYEE BASIS
select pt.EMPLOYID, PAYROLCD, sum(UNTSTOPY) as 'Hours' , PAYRATE, sum(UPRTRXAM) as TrxAmnt, ROW_NUMBER() over (partition by pt.employid order by payrate) as RowID
into #tmp
from upr30300 pt
join upr10302 bo on bo.EMPLOYID = pt.EMPLOYID --holds the current batch, get the bonus amount
where pt.TRXBEGDT >= bo.TRXBEGDT and pt.TRXENDDT <= bo.TRXENDDT
and PYRLRTYP = 1
and PAYROLCD in ('35','36','37','40','45','57','66','HOUR','MINWAG','OT','QPTOH','RETRO','VACH','','','','')
--and pt.EMPLOYID = '250-97-9055'
group by pt.employid, PAYROLCD, PAYRATE
-- get the row with the highest PAY RATE
select employid, max(rowid) as RowID
into #max
from #tmp t
where employid in (select EMPLOYID from #tmp t where t.PAYROLCD = 'OT')
group by employid
-- ddOtBonusCalc '9/27/2016','10/10/2016','bonus'
--get the OT row, add to it the correct OT pay rate
select t.employid, RowID ,pcm.PAYRTAMT
into #OT
from #tmp t
join upr00400 pcm on pcm.EMPLOYID = t.EMPLOYID and pcm.PAYRCORD = t.payrolcd
where payrolcd = 'ot'
group by t.employid, rowid, pcm.PAYRTAMT
--merge in the new OT rate, add Hours2, Payrate2, TrxAmnt2
select t.RowID, t.EMPLOYID, t.PAYROLCD, t.Hours, t.PAYRATE, t.TrxAmnt,
t.Hours as Hours2,
case when t.PAYROLCD = 'ot' then ot.PAYRTAMT * 3 else t.payrate end as PayRate2,
t.TrxAmnt as TrxAmnt2,
case when m.RowID is null then 0 else 1 end as maxPayRate,
convert(numeric(19,2),0) as Bonus,
convert(numeric(19,2),0) as OTHourlyRate
into #tmp2
from #tmp t
left join #ot ot on ot.EMPLOYID = t.EMPLOYID and ot.RowID = t.RowID
left join #max m on m.EMPLOYID = t.EMPLOYID and m.RowID = t.RowID
where t.EMPLOYID in (select employid from #ot)
order by t.employid
--edit the hours down for the highest paycode (maxPayRate)
update #tmp2 set Hours2 = t.hours2 - t2.Hours
from #tmp2 t
join #tmp t2 on t2.EMPLOYID = t.EMPLOYID and t2.PAYROLCD = 'ot'
where t.maxPayRate = 1
-- ddOtBonusCalc '9/27/2016','10/10/2016', 'bonus'
--calculate the new bonus hourly rate
select pt.employid, pt.PAYRTAMT , hours2, convert(numeric(19,2),pt.PAYRTAMT/ hours2) as OTHourlyRate
into #otRate
from upr10302 pt --holds the current batch, get the bonus amount
--get the sum of Hours2
join (
select employid, sum(hours2) as hours2 from #tmp2 group by EMPLOYID
) pt2 on pt2.EMPLOYID = pt.EMPLOYID
where BACHNUMB = @Batch
--bring the newly calculated OT rate into the table
--** not all lines will be updated
update #tmp2 set OTHourlyRate = otr.OTHourlyRate,
PayRate2 = case when t.PAYROLCD = 'ot' then t.PayRate2 + (otr.OTHourlyRate * 1.5) else otr.OTHourlyRate + t.PayRate2 end,
Bonus = otr.PAYRTAMT
from #tmp2 t
join #otRate otr on otr.EMPLOYID = t.EMPLOYID
--read-add PayRate2
update #tmp2 set TrxAmnt2 = convert(numeric(19,2),hours2 * PAYRATE2)
--select t.RowID, t.EMPLOYID, t.PAYROLCD, t.Hours, t.PAYRATE, t.TrxAmnt,t.Hours2, t.PayRate2, t.TrxAmnt2, t.maxPayRate, t.OTHourlyRate
-- from #tmp2 t
-- where t.EMPLOYID = '250-53-3535 '
-- order by t.EMPLOYID, t.PAYROLCD
-- ddOtBonusCalc '9/27/2016','10/10/2016', 'bonus'
select t.EMPLOYID, sum(t.TrxAmnt) as TrxAmt, sum(t.TrxAmnt2) as TrxAmnt2, t.Bonus,sum(t.TrxAmnt2) - sum(t.TrxAmnt) - t.Bonus as Correction
from #tmp2 t
where not t.OTHourlyRate = 0
--where t.EMPLOYID = '250-53-3535 '
group by t.EMPLOYID, t.Bonus