case when - SQL Server Forums

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

SQL Server 2012 Forums

Transact-SQL (2012)

case when

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-23 : 02:14:32

Dear Sir,

This query is run time error. how to solve it ?

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 and trx_acc_amt/total_counter then bonus
else 0 end as no_bonus

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-23 : 05:48:54

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,

sum(case when trx_acc_amt>=1000 then trx_acc_amt-1000/ nullif(( 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) ),0 )
else 0 end) as bonus,

sum(case when trx_acc_amt<1000 then trx_acc_amt/( nullif(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) ),0)
else 0 end) as nobonus

from trx_hdr
inner join user_hdr on user_hdr.user_id = trx_hdr.salesman_code
inner join user_dat on user_dat.user_id = trx_hdr.salesman_code
where user_group = 'CON' or user_pos = 'THRAP-USER' or salesman_code4<>'' or salesman_code5<>'' or salesman_code6<>''
group by salesman_code, user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6