Is there a better way to handle a conditional sum?

I need to know if there is a better way to construct this SQL statement.
(Error handling is omitted)

MS SQL Server 2000

Insert into FSSUTmp
Select a.acct_no, a.ac_nm, a.ac_type, 10, -1,
-1 * sum(CHARINDEX(convert(char(4), b.post_yr), @.post_yr) * CHARINDEX('-',
CONVERT(char(2), b.post_prd - @.post_prd - 1)) * b.prd_trn_amt),
-1 * sum(CHARINDEX(convert(char(4), b.post_yr), @.post_yr) * CHARINDEX('0',
CONVERT(char(1), b.post_prd)) * b.prd_trn_amt)
FROM GLAccounts a, GLBalances b
WHERE b.cmpny_cd = a.cmpny_cd
AND b.acct_no = a.acct_no
AND a.cmpny_cd = @.cmpny_cd
AND ac_ctrl_type between '200' and '219'
Group by a.acct_no, a.ac_nm, a.ac_type

The part Im wondering about is the 2 sum sections.
The GLBalances table has following important fields:
Post_yr -- the posting year
Post_prd the posting period
Prd_trn_amt The beginning balances if the period is 0, or the net
transactions for periods 1 through 12.

The first sum gives the current balance as of the period @.post_prd by adding
all of the periods from 0 to @.post-prd
The second sum is just the beginning balance.

It is doing a conditional sum by using CHARINDEX to be 0 if the record
should not be added and 1 if it should.

There is a problem as it stands when you are looking for the balances when
the @.post_prd is 9 or greater because b.post_prd - @.post_prd 1 will
be 10 or smaller. Then the CONVERT(char(2) .. is an error, so CHARINDEX
is 0 when it needs to be 1.

I can fix that by using SIGN and it will work fine. What I what to know, is
there a better way to populate the table, where one of the values is a
conditional sum?

This is a STORED PROCEDURE from a commercial product, so I cant change
anything else other than the STORED PROCEDURE.I think CASE is what you are looking for:

INSERT INTO FSSUTmp
SELECT acct_no, ac_nm, ac_type, 10, -1,
SUM(prd_trn_amt),
SUM(CASE post_prd WHEN 0 THEN prd_trn_amt END)
FROM GLAccounts
WHERE cmpny_cd = @.cmpny_cd
AND ac_ctrl_type BETWEEN '200' AND '219'
AND post_prd <= @.post_prd
AND post_yr = @.post_yr
GROUP BY acct_no, ac_nm, ac_type

--
David Portas
----
Please reply only to the newsgroup
--