error trx_acc_amt/total_staff - 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

General SQL Server Forums

New to SQL Server Programming

error trx_acc_amt/total_staff

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-25 : 23:42:23

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

------- column is workable.
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_staff ,

------ column error in trx_acc_amt/total_staff

case when trx_acc_amt<1000 then trx_acc_amt/total_staff
else 0 end as bonus_amt

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<>'' or trx_acc_amt>0
group by salesman_code, user_group,user_pos,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-26 : 08:54:04

You are trying to call an alias in a single query, you cant do this, try a cte.

With cte
As
(
SELECT salesman_code,user_group,user_pos,user,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6,

------- column is workable.
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_staff

------ column error in trx_acc_amt/total_staff

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<>'' or trx_acc_amt>0
group by salesman_code, user_group,user_pos,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6
)
Select *, case when trx_acc_amt<1000 then trx_acc_amt/total_staff
else 0 end as bonus_amt
From cte

We are the creators of our own reality!

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-26 : 20:56:53

I am trying edit query but also have error message. help !

With tx
As
(
SELECT
sh_code as "??",
user_name AS "????",
user_hdr.user_id as "????",
trx_acc_amt AS "??????? ",

trx_date AS "????",
trx_no AS "??",

salesman_code AS "??",
salesman_code2 AS "??",
salesman_code3 AS "??",
salesman_code4 AS "???",
salesman_code5 AS "???",
salesman_code6 AS "???",
user_group "??",
case when salesman_code is null or salesman_code=' ' then 0 else 1 end +
case when salesman_code2 is null or salesman_code2=' ' then 0 else 1 end +
case when salesman_code3 is null or salesman_code3=' ' then 0 else 1 end +
case when salesman_code4 is null or salesman_code4=' ' then 0 else 1 end +
case when salesman_code5 is null or salesman_code5=' ' then 0 else 1 end +
case when salesman_code6 is null or salesman_code6=' ' then 0 else 1 end as total_counter,

case when trx_acc_amt<1000
then trx_acc_amt/nullif((Sum(
case when salesman_code is null or salesman_code=' ' then 0 else 1 end +
case when salesman_code2 is null or salesman_code2=' ' then 0 else 1 end +
case when salesman_code3 is null or salesman_code3=' ' then 0 else 1 end +
case when salesman_code4 is null or salesman_code4=' ' then 0 else 1 end +
case when salesman_code5 is null or salesman_code5=' ' then 0 else 1 end +
case when salesman_code6 is null or salesman_code6=' ' then 0 else 1 end)),0)
else
trx_acc_amt / 2
--- end as "?????????"
end as bonus

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 trx_acc_amt>0 and user_group='CON' and salesman_code<>'Company' and salesman_code<>'Marketing' and salesman_code<>'MKT' and salesman_code<>'BOSS'
group by user_name,user_hdr.user_id,salesman_code, user_group,trx_acc_amt,trx_date,trx_no,sh_code,
salesman_code2,
salesman_code3,
salesman_code4,
salesman_code5,
salesman_code6
)
select trx_acc_amt / bonus*100 as Aver_per
From tx

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-27 : 05:04:41

I dont have any sample data?

Are you trying to just get the percentage of trx_acc_amt / bonus from the case? whats the error?

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources