Help, I don''t want to loop through this code!!!
-
OK guys, help me out with some set-based code to get my job done. First, let me lay out the scenario.
I have two tables: TableA and TableB
CREATE TABLE dbo.TableA(
ID int,
Acct_Month varchar(6),
Begin_Balance decimal(15, 2),
Debit_Amount decimal(12, 2),
Credit_Amount decimal(12, 2),
Month_Balance decimal(15, 2),
End_Balance decimal(15, 2))
CREATE TABLE dbo.TableB(
ID int,
Acct_Month varchar(6),
Amount decimal(10, 2))
Sample Data
TableA
ID
Acct_Month
Begin_Balance
Debit_Amount
Credit_Amount
Month_Balance
End_Balance
123
200508
100.00
0.00
0.00
0.00
100.00
123
200509
100.00
15.00
0.00
15.00
115.00
123
200510
115.00
0.00
-20.00
-20.00
95.00
123
200511
95.00
0.00
0.00
0.00
95.00
123
200512
95.00
40.00
0.00
40.00
135.00
456
200508
250.00
0.00
0.00
0.00
250.00
456
200509
250.00
50.00
0.00
50.00
300.00
TableB
ID
Acct_Month
Amount
123
200511
75.00
789
200509
125.00
123
200509
-30.00
Now, here is what I need to do. I need to take the data from TableB and update the Debit Amount or Credit Amount in TableA with the amount from TableB, plus the amount already in TableA, based on the records matching on ID and Acct_Month.
After updating the Debit or Credit Amount accordingly, I will update the Month_Balance to be the total of the Debit and Credit amounts for each record.
The End_Balance should be the total of the Begin_Balance + (Debit_Amount + Credit_Amount).
The Begin_Balance should equal the previous month’s End_Balance.
If a record doesn’t exist for an ID and Acct_Month, it is added to TableB with the same rules applying.
When I am done, using the sample data, TableA should look like the following:
TableA
ID
Acct_Month
Begin_Balance
Debit_Amount
Credit_Amount
Month_Balance
End_Balance
123
200508
100.00
0.00
0.00
0.00
100.00
123
200509
100.00
15.00
-30.00
-15.00
85.00
123
200510
85.00
0.00
-20.00
-20.00
65.00
123
200511
65.00
75.00
0.00
75.00
140.00
123
200512
140.00
40.00
0.00
40.00
180.00
456
200508
250.00
0.00
0.00
0.00
250.00
456
200509
250.00
50.00
0.00
50.00
300.00
789
200509
125.00
0.00
0.00
0.00
125.00
Updating the Debit_Amount, Credit_Amount, and Month_Balance are no problem because they are specific only to that individual record.
My problems start when I start trying to update the Begin_Balance and End_Balance because each Acct_Month for an ID that is greater than the earliest month I updated for that ID must have both its Begin_Balance and End_Balance updated but I cannot update the Begin_Balance for a record until I have updated the End_Balance for the previous record. (Updating the End_Balance on the earliest month is also easy because it’s Begin_Balance does not change.)
I know there has got to be a way to do this but I keep hitting a brick wall. Because I have to update a record based on the previous record, which may or may not have to be updated, I cannot find a way to do this without doing some kind of looping (not necessarily cursors but looping none the less).
I hope this makes sense.
Any ideas?
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
Yes. There is an idea. 2 actually.
First, try to post this topic on this site on SQL Server - T-SQL Forum. They may have more ideas.
Second. Get a hold of the book "Inside Microsoft SQL Server 7.0 " by Ron Soukup and Kalen Delaney. You may even try to get it in the library or look at it in the bookstore, you need just pages 693 and 694 from chapter 12 "Examples and Brainteasers" example "Keeping Running Totals". They have a very elegant solution that you may try to use. I am not sure Kalen has the same example in her "Inside Microsoft SQL Server 2000" book, but it worth to find out.
Yelena
Regards,Yelena Varsha
-
How about running 2 simple update statements. I haven't verified this synax, but how about this:
--Update 1
Update A
Set A.Debit_Amount = A.Debit_Amount + B.Amount,
A.EndBalance = A.Begin_Balance + (A.Debit_Amount+A.Credit_Amount)
From
TableA A
Join
TableB B on A.ID = B.ID
and A.Acct_Month = B.Acct_Month
--Update 2
Update A1
Set A1.Begin_Balance = A2.End_Balance
From
TableA A1
Join
TableA A2 on A1.ID = A2.ID
and DateAdd(m,-1,Substring(A1.AcctMonth,5,2) + '/1/' + Substring(A1.AcctMonth,1,4)) = Substring(A2.AcctMonth,5,2) + '/1/' + Substring(A2.AcctMonth,1,4))
You might need to add a where clause to the second one to limit how many are updated, but I would probably try something like this if at all possible.
Good Luck!
-
Thanks for the replies.
First, I posted here because I usually get a better response in this forum than in a specialized forum.
Second, J Wright, I appreciate your help but the problem here is that, using my example, your first query would update the ending balance for ID = 123 and months 200509 and 200511 first. The second query would then update the beginning balance which would then make the ending balances incorrect. This creates an endless cycle.
I have to proceed with something so I am going to try to use a (GASP!) cursor and some looping to see what kind of performance I get.
The only good thing is that this will be running at night so even if I can't get great performance, I can still get acceptable performance for an off-hours task.
I'm still open to suggestions, though. I'm getting a lot of grief here because I preach against cursors but this time I can't find another way.
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
I don't now,
but
you try the triggers?
-
Hawg,
you do have to read these 2 pages that I said from Ron Soukup's book. I am sure you can use his solution.
You may send me your email address by clicking on the link with my name and sending a private message. I will scan these 2 pages and send them to you.
Yelena
Regards,Yelena Varsha
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply