|
|
Posted by Bill Bob on 10/01/47 11:40
I have an accounting database which contains data from various years.
The frontend is a VB.Net program. At the year end, the program creates
new voucher and transaction tables and creates new stored procedures for
them.
I just append the 'new year' at the end and create them
ie, Vouchers2001, Vouchers2002, Vouchers2003
Similarly Transactions2001, Transactions2002.
The data for all the years is in the same database.
Also, I maintain a table called 'Books' which contains the Years for
which data is present in the Database. The Structure of the Books table
is
BookID BookYear
1 2001
2 2002
3 2003
4 2004
My Problem is that i need to know the current balance of any ledger for
any year. The method to calculate the balance for any year is to start
from the Minimum year in the Books table and continue upto the required
year. The SQL is as follows.
DECLARE @iLedgerID AS INT --will be passed as parameter
DECLARE @iYear as INT --will be passed as parameter
DECLARE @CurrentBalance as MONEY
SET @iLedgerID =1
DECLARE @MinBook as INTEGER
DECLARE @String nVarchar(4000)
SELECT @MinBook = Min(BookYear)
FROM Books
WHILE @MinBook <= @iYear
BEGIN
SET @String = ' DECLARE @TT Money ' + char(13) +
' SELECT @TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )'
+ ' FROM transactions' + CAST(@MinBook AS CHAR(4)) + ' LEFT OUTER JOIN
dbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + ' ON dbo.Transactions' +
CAST(@MinBook AS CHAR(4)) + '.VoucherID' + ' = dbo.Vouchers' +
CAST(@MinBook AS CHAR(4)) + '.VoucherID ' +
'WHERE (LedgerID = @iLedgerID)'
EXEC sp_executesql @String, N'@iLedgerID Int', @iLedgerID */
SET @MinBook = @MinBook + 1
END
Now this is just a sample code. It may have a few glitches. My question
is
a) Do I have to create a dynamic sql if the name of the database is not
known ahead of time. If No then
b) I need to add the balance of each year to the grand total. How do i
return a value from a dynamic sql.
TIA
*** Sent via Developersdex http://www.developersdex.com ***
[Back to original message]
|