445,857 Members | 1,793 Online Need help? Post your question and get tips & solutions from a community of 445,857 IT Pros & Developers. It's quick & easy.

# Is there a better way to handle a conditional sum?

 P: n/a 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 