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
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-25 : 20:57:32
Error message, " Divide by zero error encountered, how to solve it ?
Go to Top of Page
   

- Advertisement -