update 1 table from another table by next earliest date

Question

I have 2 tables: budget and budget_rate:

Budget Table

resource period hours dollars
-------- ------ ----- -------
ADMIN03 01/31/16 160 8000
ADMIN03 02/28/16 150 7500

Rate Table

resource rate eff_date
-------- ---- --------
ADMIN03 50.00 01/01/16
ADMIN03 52.50 01/01/17

When the rates change in the rate table, I need to update the budget based on the rate that matches the resource name and is the first rate record earlier than the budget record.

Can this be accomplished with a single UPDATE?

Something like:

update b  
  set b.dollars = b.hours*r.rate  
from  
   budget b join rate r on  
   b.resource = r.resource and  
   b.period >= r.eff_date

score 1 · Accepted Answer · answered Sep 01 '16 at 00:47

I assume rate table is realtive small, so I would recalculate it to have range columns.

with oRates as (
        select  resource,
                rate,
                eff_date,
                ROW_NUMBER() over(partition by resource order by eff_date desc) rn
        from  Rates
    ), 
    pRates as (
        select  r1.resource,
                r1.rate,
                r1.eff_date from_date,
                isnull(r2.eff_date,'2070-01-01') to_date
        from oRates r1 
            left join oRates r2 on (r1.rn = r2.rn+1)
     )
update b
set dollars = hours * r.rate
from Budget b
    join pRates r on (b.resource = r.resource 
                    and b.period >= from_date 
                    and b.period < to_date)

score 0 · Answer 2 · edited May 23 '17 at 10:29

One possible solution is using a computed column instead of some sort of manual update.

An example of how this could be done can be seen here: formula for computed column based on different table's column

For a working example with your data, you'd create a function like this:

CREATE FUNCTION dbo.ufn_BudgetDollars (@resource NVARCHAR(255), @date DATE, @hours INT)
RETURNS DECIMAL(10, 2)
AS BEGIN
    DECLARE @out DECIMAL(10, 2);
    SELECT @out = @hours * rate
    FROM (
        SELECT rate, ROW_NUMBER() OVER (ORDER BY eff_date DESC) rn
        FROM tblRate
        WHERE eff_date <= @date
        AND resource = @resource) T
    WHERE RN = 1;
    RETURN @out;
END
GO

When you've created your function, you would want to drop and recreate the Dollars column on the budget table...

ALTER TABLE tblBudget DROP COLUMN Dollars;
ALTER TABLE tblBudget ADD Dollars AS dbo.ufn_BudgetDollars(resource, Period, Hours);
GO

update 1 table from another table by next earliest date

2 Answers2