Is there a better way to handle a conditional sum?

[Bob]

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(c onvert(char(4), b.post_yr), @post_yr) * CHARINDEX('-',
CONVERT(char(2) , b.post_prd - @post_prd - 1)) * b.prd_trn_amt),
-1 * sum(CHARINDEX(c onvert(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 I’m 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 can’t change
anything else other than the STORED PROCEDURE.

[David Portas]

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
--