how to edit this query for bonus ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to edit this query for bonus ?

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-23 : 00:47:37

Dear Sir,

When I am run this query the bonus is wrong. how to edit it and make it work ?
------------------------------------
SELECT salesman_code,user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6,

Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) as total_counter,

case when trx_acc_amt>1000 then trx_acc_amt/total_counter else 0
case end as bonus

from trx_hdr
inner join user_hdr on user_hdr.user_id = trx_hdr.salesman_code

GROUP BY user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-23 : 04:51:14

SELECT salesman_code,user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6,

Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) as total_counter,

case when trx_acc_amt>1000 then trx_acc_amt/(Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end)) else 0
end as bonus

from trx_hdr
inner join user_hdr on user_hdr.user_id = trx_hdr.salesman_code

GROUP BY user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6

Regards
Viggneshwar A

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-25 : 20:57:32

Error message, " Divide by zero error encountered, how to solve it ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources