January 10, 2006 at 6:34 pm
I have a billing table and a family table. The family table contains the Student name AcctID and charges. I have billing records that are entered daily and I need to update the Billing AcctID to match the Family AcctID. I thought I had this working but it is not updating records with the distinct AcctID of each student. I have the following code to update the acctID and then update the billing information for each family. I need some help figuring this out please.
Update Billing
SET Billing.AcctID = Family.AcctID from Family
Where Billing.Student = Family.Student
UPDATE Family
SET FAMILY.CHARGES = B.Charge
FROM (SELECT AcctID, SUM(CHARGE) Charge
FROM BILLING GROUP BY AcctID) B
WHERE FAMILY.AcctID = B.AcctID
UPDATE Family
SET FAMILY.payments = B.Payment
FROM (SELECT AcctID,SUM(payment) payment
FROM BILLING GROUP BY AcctID) B
WHERE FAMILY.AcctID = B.AcctID
UPDATE Family
SET BALANCE = CHARGES - PAYMENTS
************************************
I seem to be able to grab some of the AcctID's for a student but then it will either leave the field null or add the same acctID for several different students
is there a way to do this I need help.
Thanks
Josh
January 10, 2006 at 6:50 pm
Try this instead:
Update Billing
SET Billing.AcctID = Family.AcctID
from Billing inner join Family
on Billing.Student = Family.Student
UPDATE Family
SET FAMILY.CHARGES = B.Charge,
FAMILY.payments = B.Payment
FROM (SELECT AcctID, SUM(CHARGE) Charge, SUM(payment) payment
FROM BILLING GROUP BY AcctID) B inner join family
on FAMILY.AcctID = B.AcctID
UPDATE Family
SET BALANCE = CHARGES - PAYMENTS
January 10, 2006 at 8:29 pm
That is giving me the same result. I have 9 students listed in the family table but I am only getting AcctID # 1 and # 3 updated in the billing table. What am I missing here?
January 11, 2006 at 2:14 am
Are there any NULLs in the CHARGE or PAYMENT entries in BILLING?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 11, 2006 at 2:33 am
The problem I am having is not with updating the payments. I am not able to update the acctID on a student in the billing table. The code I need help with is:
Update Billing
SET Billing.AcctID = Family.AcctID
from Billing inner join Family
on Billing.Student = Family.Student
The above does not update all records but if I used the code below for each student I will get the results I want. I am wondering if there is a better way to do this, so I don't have to add this new line of code every time I add a new student.
Update Billing
SET Billing.AcctID = '500'
Where Billing.Student = 'Josh'
thanks
josh
January 11, 2006 at 2:52 am
OK. Have a look at the results of running
SELECT Billing.AcctID, Family.AcctID
from Billing inner join Family
on Billing.Student = Family.Student
Which will presumably correlate with the BILLING records that are being updated.
You should be able to work out what's happening with your data just by looking at what has not been selected by the above query and working out why.
By the way, matching on names is not a recommended practice. What would happen if you had two students called "John Smith", for example? Or is Student the PK of Family?
On a separate point, when you use UPDATE clauses, for performance reasons it is a good idea to include a WHERE clause that prevents SQL Server updating something which already has the correct value (as writes are much more time consuming than reads). Eg:
Update Billing
SET Billing.AcctID = Family.AcctID
from Billing inner join Family
on Billing.Student = Family.Student
WHERE Billing.AcctID <> Family.AcctID
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply