CODEWITHSUNDEEP
sqlsql-server
Dez79
Dez79

Reputation: 607

SQL Loop and update table with sum

I have the following shiftdate table that stores the amount of hours worked at rate1 and rate2 (these are stored in another table). I want to use this data to update the shiftdateTotal field with the total value. Hoping you can help?

SHIFTDATE Table

LISTITEM Table

Example listItem row (23, BankHolidayRate, 12.50)

I'm able to create a SQL select that does the required calculation, see below:

    SELECT shiftDateID,
    hoursRate1, t2.rateOfPay as rate1,
    hoursRate2, t3.rateOfPay as rate2,
    (hoursRate1 * t2.rateOfPay) +  ([hoursRate2] * t3.rateOfPay) as myTotal
    from dbo.ShiftDate t1 

    inner join dbo.ListItem t2
    on t1.hourlyRate1ID = t2.listItemID

    inner join dbo.ListItem t3
    on t1.hourlyRate2ID = t3.listItemID

But now i'm a bit lost.. Using this query or something similar how can I insert the myTotal value into the shiftDateTotal for the required shiftdateID row?

Can i store this into a temp table, then loop through the data and update the SHIFTDATE table with myTotal?

Upvotes: 0

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269943

You can just turn this into an update. The transformation is pretty direct:

update sd
    set shiftDateTotal = (sd2.hoursRate1 * li1.rateOfPay) + (sd2.hoursRate2 * li2.rateOfPay) 
from dbo.ShiftDate sd inner join
     dbo.ListItem li1
     on sd.hourlyRate1ID = li1.listItemID inner join
     dbo.ListItem li2
     on sd.hourlyRate2ID = li2.listItemID;

Notes:

  • I replaced the meaningless table aliases (t1, t2, t3) with more meaningful ones (abbreviations for the table names).
  • I qualified all column names.
  • You might want to be careful, in case any of the rate ids are NULL/missing in the original table. In that case, you will want left joins and coalesce()s.

Upvotes: 2

Related Questions