February 21, 2008 at 2:34 pm
I am using a stored procedure to get installment loan information. Part of the proc creates the new istallment information
Select month(deffdate)as Month, year(deffdate)Year, count(*)New_Installments, sum(nbegbal) Amount
into #newinstalls
from s8instal
group by month(deffdate), year(deffdate)
this results in the following data:
Month Year New_Instals Amount
10 2007 145 116279.03
11 2007 2 1618
12 2007 2 1253
1 2008 6 8136
2 2008 8 5268
I need to add a balance column that carries the balance forward. Since October was the initial month, the balance would equal the amount. For November the balance would equal October plus the new loans from November.
Any assistance would be greatly appreciated.
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec
February 21, 2008 at 2:45 pm
hi there.
please take a look at this link::
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/sequentialordering/2261/
I have not read it completely, but, I have a feeling you may get some ideas from this page and might be able to built your solution ..
good luck..
John Esraelo
Cheers,
John Esraelo
February 21, 2008 at 3:49 pm
also look at this thread: http://www.sqlservercentral.com/Forums/Topic453576-149-1.aspx
February 21, 2008 at 3:49 pm
Here's another thread. The article is a how to on how to build running totals (what you're trying to accomplish)....
http://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 21, 2008 at 3:51 pm
Jeff I see that you are in the post. I was about to plug your article on the update method. 😀
Now I guess you can do that ;).
Using Jeff's method, you can accomplish the "running total" like this:
CREATE TABLE #T(
ID INT,
Month INT,
Year INT,
New_Instals INT,
Amount MONEY
)
CREATE NONCLUSTERED INDEX IX_T_ID_Month_Year
ON #T (ID, Month, Year);
INSERT INTO #T
SELECT 1, 10, 2007, 145, 116279.03 UNION ALL
SELECT 1, 11, 2007, 2, 1618 UNION ALL
SELECT 1, 12, 2007, 2, 1253 UNION ALL
SELECT 2, 1, 2008, 6, 8136 UNION ALL
SELECT 2, 2, 2008, 8, 5268
--===== Declare the variables for the "Code Basis"
DECLARE @PrevGrpBal MONEY
SET @PrevGrpBal = 0
DECLARE @PrevAcctID INT
SET @PrevAcctID = 0
--===== Changed the formula to "reset" when the account changes
UPDATE #T
SET @PrevGrpBal = AMOUNT = CASE
WHEN ID = @PrevAcctID
THEN @PrevGrpBal + Amount
ELSE Amount -- Restarts total at "0 + current amount"
END,
@PrevAcctID = ID
FROM #T WITH (INDEX(IX_T_ID_Month_Year),TABLOCKX)
--===== Display the results in the correct order
SELECT *
FROM #T
ORDER BY ID
DROP TABLE #T
February 21, 2008 at 3:54 pm
Jeff, I guess you didnt make a post. Myschif you should read up on the article. It is wonderfully written and explains the potentials pitfalls, in solving a problem similar to yours.
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
February 21, 2008 at 4:02 pm
I saw you in the post, Adam... I knew you would do as good as I could. And, thanks for the plug!
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2008 at 4:11 pm
Myschif,
Adam and the other folks are too kind with their words about my articles. The articles do, however, explain some major pitfalls with the performance of some of the more traditional methods of calculuating running balances over larger row-sets. Take a look at the articles... the one on "Trianagular Joins" shows just how bad a running balance calculation can get and why they take so long. Here're the URL's just for convenience sake.
http://www.sqlservercentral.com/articles/T-SQL/61539/
http://www.sqlservercentral.com/Forums/Topic449802-203-1.aspx
Lots of folks frequently say "Well, I'm only working with 5 rows right now". I always try to remind them that, first, a triangular join will cause 15 internal rows to be worked for that and, second, it's 5 rows right now... you want to wait until a critical time (oh, say, year end processing?) to have it fail on scalability? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2008 at 1:22 pm
Thank you all for the information.
Adam - I tried your code and received the following message:
Must declare the scalar variable "@PreGrpBal"
I am unsure how to resolve this.
Additionally, I do not understand how to apply this as the data evolves each month. In order to use this in the existing stored proc, I would need to select the data into the #T table wouldn't I?
I am obviously in over my head; thank you very much for your assistance. 🙂
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec
February 25, 2008 at 1:38 pm
MySchif -
a few things:
- your error must be a typo. It should be @PrevGrpBal and not @PreGrpBal (notice that there's a letter missing).
- you're correct about #T. Since it's a temporary table, you'd have to populate it with the data you wish to include in the running total. However - from testing the various methods - repopulating that temp table and generating the results using the method above tends to be faster than simply trying to "do it the hard way" using a correlated sub-query. So - your stored procedure would start assuming that #T doesn't exist, would build it from scratch, then extract the results you wish, and dispose of #T. (Or - you start out by checking for #T and if it exists, then start by dropping it, THEN build it from scratch, etc...).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 25, 2008 at 1:43 pm
myschif (2/25/2008)
Thank you all for the information.Adam - I tried your code and received the following message:
Must declare the scalar variable "@PreGrpBal"
I am unsure how to resolve this.
Additionally, I do not understand how to apply this as the data evolves each month. In order to use this in the existing stored proc, I would need to select the data into the #T table wouldn't I?
I am obviously in over my head; thank you very much for your assistance. 🙂
My $0.02. Most accounting systems would populate the outstanding balance either via a trigger or during some nightly process. So, you may want to consider adding the current balance as a column on the table which houses transactions or to the table which stores account details.
February 25, 2008 at 4:35 pm
You are correct Matt. I typed it in the message incorrectly; the error reads:
Must declare the scalar variable "@PrevGrpBal".
If I understand you correctly regarding populating the table, I would create the #T table and insert into it from my current data. I believe I could use a case statement to assign the correct ID based on the Year value.
Thank you for your time.
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec
February 26, 2008 at 7:16 am
antonio.collins (2/25/2008)
My $0.02. Most accounting systems would populate the outstanding balance either via a trigger or during some nightly process. So, you may want to consider adding the current balance as a column on the table which houses transactions or to the table which stores account details.
I agree with that! And, if you simply can't add a column to a table because you may break a 3rd party application, you can certainly make a "sister" table that contains the PK and the running balance info. If it ever get's "out of sync", you can always snap rename (or "roll a synonym") a corrected version in place without breaking anything or causing any blocks.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 26, 2008 at 10:32 am
Can someone explain the [Must declare the scalar variable "@PrevGrpBal"] error message?
The following section of Adam's script appears to declare the @PrevGrpBal variable:
--===== Declare the variables for the "Code Basis"
DECLARE @PrevGrpBal MONEY
SET @PrevGrpBal = 0
DECLARE @PrevAcctID INT
SET @PrevAcctID = 0
Thank you for your assistance 🙂
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec
February 26, 2008 at 10:41 am
You probably have a "GO" somewhere in your code. If you use "GO", the variable is no longer in scope. Replace and "GO" with a ";".
This will throw the same error, unless you change the "GO" to a ";".
--===== Declare the variables for the "Code Basis"
DECLARE @PrevGrpBal MONEY
SET @PrevGrpBal = 0
DECLARE @PrevAcctID INT
SET @PrevAcctID = 0
go
print @PrevGrpBal
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply