469,089 Members | 1,205 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,089 developers. It's quick & easy.

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 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.
Jul 20 '05 #1
1 8398
I think CASE is what you are looking for:

SELECT acct_no, ac_nm, ac_type, 10, -1,
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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by valued customer | last post: by
28 posts views Thread by Benjamin Niemann | last post: by
4 posts views Thread by Bradley | last post: by
10 posts views Thread by John Smith | last post: by
5 posts views Thread by paulo | last post: by
5 posts views Thread by Nitesh | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.