January 23, 2002 at 4:39 am
I've a lot of ad-hoc reports to be generated from a legacy database. This means that the data comes from several tables with a lot of conditional processing. What is the best method to go about it. It's in SQL 2000. I connect using VB6 and ADO 2.6. If I write a SP to do all the processing into a cursor how do I return the final cursor.
Please help.
January 23, 2002 at 7:12 am
It really depends on the type of conditional processing that needs to be done. Does it involve computed columns, string manipulation, or array comparisons? In some cases it would be best to do the operations in SQL Server but in others it would be best in VB. Could you give a few more details?
Also, do you have the option of processing the data into another database prior to the ad hoc queries (a warehousing solution)?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 23, 2002 at 10:36 am
I agree it depends on the complexity. If you can generate in a few line of TSQL, do it. Once you get more complicated, better off to use an app. At that point you may well consider moving everything there and building a standard system for implementing.
Andy
January 23, 2002 at 11:58 am
Or suck in the data into a table and process it there. I agree with Brian and Andy, pls post more info.
Steve Jones
January 24, 2002 at 9:22 am
I am describing a typical report. I have two tables.
1. Ledger
A/c Code primary
Name
Opening balance numeric
Opening Debit/credit (contains only 2 values D or C)
2. Transact
A/C code
Amount
Debit / Credit
The above 2 are the source tables.
I have to take all the account heads with opening balances from the ledger table. For each a/c in the result set I have to compute the total of debit and credit from the transact table and add it to the opening balance.
The final result has to be printed out.
output is shown below
-------------------------------------------
account amount D/C
-------------------------------------------
Will this do ?
Please help
gkk
January 24, 2002 at 10:00 am
You don't have any real "ordering" in the Transact table. How can you determine the "opening balance". Wihtout some date or flag, this isn't really possible. Depending on natural order (order of insertion) is a poor design.
If you have a flag, then SQL can get you the opening balance with a WHERE clause on the flag or a min() function. Something like
select a.accode, b.amount
from ledger a
inner join transact b
on a.accode = b.accode
where b.datefield = ( select min( c.datefield)
from transact c
where c.accode = b.accode
)
You can combine this with a result set that sum's the amounts
select a.accode, sum(b.amount)
from ledger a
inner join transact b
on a.accode = b.accode
where b.datefield > ( select min( c.datefield)
from transact c
where c.accode = b.accode
)
If you want the sum to include the beginning balance, change the > to >= or ignore it (depending on requirements).
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply