calculation based upon GLAcct#, then use records associated Trxamt field in calc 1

[awaria]

SQL Server 2000 Enterprise SP2

Need assistance with a query

Need to perform two calculation(s) to return to two new field(s) at end of recordset.

Table is "GL Account Trx Data" with GLacct#,TrxDate,TrxAmt as key fields.

select constants
Sum(Case When ?? then perform this calculation ?? end) as New_Field
where trxDAte between '2012-09-01' and '2012-09-30'

**This is going to be a query that will monthly**



( ((GLacct# = 4300)+(GLacct# = 4325)) / ((GLacct# = 4300)+(GLacct# = 4325)+(GLacct# = 4350)) ) * (GLacct# = 5000)+(GLacct# = 6000)+numerous other GLAccts


That is the layout of the formula. When GLAcct = x, use TrxAmt for GLAcct x

Not sure if Case statement is best method. Would need help is setting up case stmt, or if there is
a better method, would appreciate suggestions.

Thank you,

awaria

 

[SQLBill]

I would like clarification on what you are looking for.

To take the first part of your calculation, you say you want this:

((GLacct# = 4300)+(GLacct# = 4325)) --which basically adds 4300 and 4325 together.

But do you really mean you want:

((SELECT TrxAmt FROM [GL Account Trx Data] WHERE GLacct# = 4300) + (SELECT TrxAmt FROM [GL Account Trx Data] WHERE GLacct# = 4325)) ???


Also, why are you only on SP2 for SS2000? SP4 has been out for quite a long time and there were security issues with SP2.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[awaria]

Yes, That is what I mean in your example"select Trxamt where GLacct# = ???"

Thank you, I could not get that structure in focus.
I will give that a try.

On the SQL SP level, I agree. We are preparing for an ERP upgrade where the next platform is still in play,
depending upon the selected solution.

Again, thanks much for the help with the syntax.

aw

 

[SQLBill]

No trouble. I notice you use the same value twice in your example:

( (([highlight #F57900]GLacct# = 4300[/highlight])+([highlight #FCE94F]GLacct# = 4325[/highlight][highlight #FCE94F][/highlight])) / (([highlight #FCAF3E]GLacct# = 4300[/highlight])+([highlight #FCE94F]GLacct# = 4325[/highlight])+(GLacct# = 4350)) ) * (GLacct# = 5000)+(GLacct# = 6000)+numerous other GLAccts

Instead of having to go back and select the value again, you could create variables to hold the values and then use the variables to do the math.

DECLARE @GLacct1 INT
DECLARE @GLacct2 INT
DECLARE @GLacct3 INT
<etc>
SET @GLacct1 = SELECT TrxAmt FROM [GL Account Trx Data] WHERE GLacct# = 4300
SET @GLacct1 = SELECT TrxAmt FROM [GL Account Trx Data] WHERE GLacct# = 4325
SET @GLacct1 = SELECT TrxAmt FROM [GL Account Trx Data] WHERE GLacct# = 4350
<etc>

Then the math would be:

( ((GLacct# = 4300)+(GLacct# = 4325)) / ((GLacct# = 4300)+(GLacct# = 4325)+(GLacct# = 4350)) ) * (GLacct# = 5000)+(GLacct# = 6000)+numerous other GLAccts 

((@Glacct1 + @GLacct2) / (@GLacct1 + @GLacct2 + @GLacct3) ....

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875