loops - sql server procedure to update table on conditions -

i trying write stored procedure run every day , check invoices past due or not. want pull invoices table not paid want go through them , find difference between todays date , date order placed. there want check account terms order are( how long have pay) , if have gone on terms calculate service charge , update balancedue. have basic idea of don't know how go through selected records without looping through each one. thought there better way in sql server.

the invoice table has accountid, ispaid, , creationdate. account table terms account. have accountbalance table several fields update if needed.

accountbalance fields balancedue pastdue30  pastdue60  pastdue90 pastdueover90 

the accountid can me invoice account , accountbalance , date can give me how long has been, update accountbalance accordingly terms , how long has been past due. know little hard understand without seeing it.

this trying not sure how each record

select * invoice ispaid = 0  days = currentdate - invoicecreationdate  switch (days)  case 30  update balance  case 60  update balance  case 90  update balance    if(days > terms)  update balance add servicecharge 

your additions help, i'm still little unsure on what's going on here (for example, pastduexx fields in accountbalance , how relate balancedue field?). also, can 1 account have multiple past due invoices?

it sounds you're looking similar following:

update ab set balancedue =       (case when datediff(i.creationdate,getdate()) > 90 balance due + ...            when datediff(i.creationdate,getdate()) > 60 balance due + ...            ...       end) accountbalance ab join account     on ab.accountid = a.accountid join invoice      on a.accountid = i.accountid 

sorry vagueness, again, still have questions.