I have a couple of tables. One tracks Rate and Effective Date, another that tracks Earned Hours and Earn Date. I made a table, see below, that mimics this combined information (I use a sum and Cross Join).
If I need to pay out Earned Hours at the most recent rate and working backwards. How can I do this?
Insert Into #MyTempTable (EE, EffDate, Salary, Accrued)
Values (720, '12/1/2020',18,40)
, (720, '11/1/2020',16,53.28)
, (720, '10/1/2020',14,113.16)
, (720, '9/1/2020',12,39.24)
, (720, '8/1/2020',10,2.91)
If I wanted to pay out 150 hours, then it should be at:
40 @ 18
53.28 @ 16
56.72 @ 14
Thank you for any help.