I am using SQL Server 2000.
I have a table called tblPartners and I want to run an sp every night that updates a field called TotalPaid.
The table that holds payment info is called tblPaymentInstalments and the field is called InstalmentAmnt.
The 2 tables are related via 2 other tables as follows:
tblPaymentInstalments inner join
tblPayments on tblPaymentInstalments.PaymentRef = tblPayments.PaymentRef inner join
tblProposals on tblPayments.ProposalNo = tblProposals.ProposalNo inner join
tblPartners on tblProposals.PartnerID = tblPartners.PartnerID
How do I write the sp that will update tblPartners.TotalPaid with the sum(tblPaymentInstalments.InstalmentAmnt) for each Partner?