case problem 1

[jordan11]

#1 Today, 09:30 AM
Princess Zea

Registered User
Join Date: Apr 2005
Posts: 42
Time spent in forums: 1 Day 4 h 50 m 57 sec
Reputation Power: 0

case problem

--------------------------------------------------------------------------------

I am using a case statement to compare results between tables and then allocate points based on a match. I am having a problems with radio buttons as I would like to allocate points based on the value selected in the radio button which has a value between 1-5.
e.g if table A, a customer has selected any state and in table B the rating for any state is 4 I would like to multiply 4 by 10 and then allocate the points, if no state was selected then they get 0.


,(CASE WHEN C.LocationState = P.PState THEN 20 ELSE 0 END
+CASE WHEN CLocationState = P.mstate THEN 50 ELSE 0 END
+CASE WHEN C.LocationState = p.StateBirth THEN 10 ELSE 0 END) AS geography
,(CASE WHEN c.salaryto = p.SalaryMin THEN 20 ELSE 0 END
+CASE WHEN c.salaryfrom = p.SalaryExp THEN 40 ELSE 0 END) AS salary
,(CASE WHEN c.BoardStatus = p.pBoardStatus THEN 40 ELSE 0 END

This is the problem is not sure how to add the code that will allow a comparison of an int and a varchr and then give the points.
+(CASE cl.AMG ='1' AND p.state IS NOT NULL THEN 1 X 10 ELSE 0 END
+(CASE cl.AMG ='2' AND p.state IS NOT NULL THEN 2 X 10 ELSE 0 END
+(CASE cl.AMG ='3' AND p.state IS NOT NULL THEN 3 X 10 ELSE 0 END
+(CASE cl.AMG ='4' AND p.state IS NOT NULL THEN 4 X 10 ELSE 0 END
+(CASE cl.AMG ='5' AND p.state IS NOT NULL THEN 5 X 10 ELSE 0 END) AS qualifi


Thanks in advance

 

[AtomicWedgie]

something like this

case p.state
when null then '0'
else case cl.AMG
when 1 then '10'
when 2 then '20'
when 3 then '30'
when 4 then '40'
when 5 then '50'
end
end as qualifi

regards,

atomicwedgie

 

[nomadicalloy]

Another thing use of "(" on case statement is good practice.


SQL Help Blog

http://www.xpherion.com/blog.asp

 

[jordan11]

Thanks for your reply,
That worked fine
Sorry I submitted this post to quickly as I also need to know how to do calculations within the case statement for salary as if salary is hourly I need to convert it to annual by 40 * 50, if null then 0 then points are based on percentage difference
So ,(CASE WHEN C..salaryto = P..SalaryMinimum THEN 20 ELSE 0 END
+CASE WHEN C.salaryfrom = P..SalaryExpected THEN 40 ELSE 0 END) AS salary


should be min sal(P.SalaryMinimum) P
min sal(P.Salaryto)C
max sal(X)

So to find out the minimum salary
p equal or less than m = P-M divide by M multiply by 100
if the result is less than 10% then 100 points
20% then 80 points
30% then 60 points

etc

 

[amrita418]

Can you post some sample data and expected output?

Regards,
AA

 

[jordan11]

Thanks for the reply
I will try and explain myself better
table A
clientId salaryfrom salaryto
1 200000 400000
2 300000 500000
4 50000 600000
5 60000 700000




table B
customerid salarymin salarymax
1 200000 400000
2 400000 600000
4 60000 700000
5 60 80


I have table A and table B and I need to know how near the client minimum salary is to the salaryfrom in the opportunity and maximum salary is to the salaryto in the opportunity.

So the first thing I need to do is make sure the salary entered by the client or customerid is annually as they have the options to enter an annually or hourly salary there is a radio button on the form so I will know the difference. If it is hourly then as in customerid 5 the annual wage salarymin * 40 hours * 50 weeks.
60*40*50=annual wage
Once I have the annual value I then want to compare the salaryfrom in table A to the salarymin in Table B by getting a percentage of the difference in values

should be min sal(P.SalaryMin) P
min sal(P.Salaryto)M
max sal(X)

So to find out the minimum salary
P-M divide by M multiply by 100
So for clientid 1 and customerid 1
200000 -200000 /200000*100=%
if the result is less than 10% then 100 points
20% then 80 points
30% then 60 points
etc
I will then do the same thing for max salary but using a different formula
But I need this in the form of a case statement

I hope this is more clearer
thanks

 

[amrita418]

What are the criteria to determine if the salary is annual or monthly or per hour?

 

[jordan11]

there is no criteria basically they just check a radio button called 'type' indicating whetever the amount they put in is hourly or annually. if hourly then the result put in for the salarymax and min has to be * by 40 for hours and 52 for weeks to get the annual figure.

thanks for your reply i am really stuck here
this is my attempt so far not workking not even sure if i am on the right track



SELECT distinct p.ID
c.opportunityID
,(CASE WHEN c.LocationState = p.State THEN 20 ELSE 0 END
+CASE WHEN c.LocationState = P.medstate THEN 50 ELSE 0 END
+CASE WHEN c.LocationState = p.StateBirth THEN 10 ELSE 0 END) AS geography
,(CASE when p.salarymin IS NULL THEN 0 ELSE
IF "paytype = hourly"
THEN salarymin = (p.salarymin * 40 * 50)
and salarymin - c.salaryto/ c.salaryto * 100= percentage
percentage < 30% points =100
percentage >40% points=50
percentage >60% points=0
ELSE
p.salarymin - c.salaryto/c.salaryto * 100= percentage
percentage < 30% points =100
percentage >40% points=50
percentage >60% points=0
end if
END
+CASE p.salarymax IS NULL THEN 0 ELSE
IF "paytype = hourly"
THEN salarymax = (p.salarymax * 40 * 50)
and salarymin - c.salaryfrom/ c.salaryfrom * 100= percentage
percentage < 30% points =100
percentage >40% points=50
percentage >60% points=0
ELSE
ps.salarymin -c.salaryfrom/c.salaryfrom* 100= percentage
percentage < 30% points =100
percentage >40% points=50
percentage >60% points=0
endif
END) AS salary

 

[amrita418]

Here is the code without the use of payment type.

select a.clientid,
       b.customerid, 
       case when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 10 then 100
            when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 20 then 80
            when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 30 then 60
            else 40 end -- you can add more cases
from  TableA a 
      Inner Join TableB b 
      On (b.customerid = a.clientid)

Let me know if it makes sense?

Regards,
AA

 

[jordan11]

Thanks,

i get this error
Divide by zero error encountered.

 

[amrita418]

Add another case
something like

case   when a.salaryfrom = 0 then 0
       when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 10 then 100
       when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 20 then 80
       when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 30 then 60
       else 40 end -- you can add more cases

Regards,
AA

 

[jordan11]

Thanks AA,

That worked fine, is it poosible for me to add the type calculation in somewere as if they enter a hourly salary the calculation will be wromg as results are based on annual figures.

 

[amrita418]

Do you store the type calculation as a column in any of your tables? If yes then all you need is one more case condition within the case.

Sample Data:

case   when a.salaryfrom = 0 then 0
       when ((case when paymenttype = 'hourly' then  b.salarymin * 40 * 52 
                   when paymenttype = 'monthly' then b.salarymin * 12 
                   else b.salarymin end - a.salaryfrom) / a.salaryfrom) * 100.0 < 10 then 100 
.....

Regards,
AA

 

[jordan11]

Thanks,

Because I have to test any value that goes in as a salary is this how the code should look


select a.clientid,
b.customerid,
case when a.salaryfrom = 0 then 0
when ((case when paymenttype = 'hourly' then b.salarymin * 40 * 52
else b.salarymin end - (case when paymenttype = 'hourly' then a.salarymin * 40 * 52
else a.salarymin end)) / (case when paymenttype = 'hourly' then a.salarymin * 40 * 52
else a.salarymin end))) * 100.0 < 10 then 100
when ((case when paymenttype = 'hourly' then b.salarymin * 40 * 52
else b.salarymin end - (case when paymenttype = 'hourly' then a.salarymin * 40 * 52
else a.salarymin end)) / (case when paymenttype = 'hourly' then a.salarymin * 40 * 52
else a.salarymin end))) * 100.0 < 20 then 80
............
else 40 end -- you can add more cases
from TableA a
Inner Join TableB b
On (b.customerid = a.clientid)

 

[amrita418]

Since payment type is same for a given customerID why check it multiple times?

select a.clientid,
       b.customerid, 
       case   when a.salaryfrom = 0 then 0
       when (case when paymenttype = 'hourly' then  ((b.salarymin * 40 * 52 - a.salaryfrom * 40 * 52) / a.salaryfrom * 40 * 52) * 100.0)
                  else ((b.salarymin  - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 10 then 100 
    
............
            else 40 end -- you can add more cases

OR

To make the code easier you could change it to

select  inner.clientid, 
        inner.customerid,
        case when inner.salrayfrom = 0 then 0
             when ((inner.salarymin - inner.salaryfrom) / inner.salaryfrom) * 100.0 < 10 then 100
             when ((inner.salarymin - inner.salaryfrom) / inner.salaryfrom) * 100.0 < 20 then 80
             else 40 end
from    (select   a.clientid clientid, 
                  b.customerid customerid, 
                  case when paymenttype = 'hourly' then b.salarymin * 40 * 52
                       when paymenttype = 'monthly' then b.salarymin * 12
                       else b.salarymin end salarymin,
                  case when paymenttype = 'hourly' then paymenttype * 40 * 52
                       when paymenttype = 'monthly' then a.salaryfrom * 12
                       else a.salaryfrom end salaryfrom
from ......) inner

I have not tested the code so you might have to tweak with it.

Regards,
AA

 

[jordan11]

Thanks again,
Is it possible for me to give you another star as you have been a great help.
so for the first solution I you sent can i do this

select a.clientid,
b.customerid,
case when a.salaryfrom = 0 then 0
when (case when paymenttype = 'hourly' then ((b.salarymin * 40 * 52 - a.salaryfrom * 40 * 52) / a.salaryfrom * 40 * 52) * 100.0)
else ((b.salarymin - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 10 then 100
when (case when paymenttype = 'hourly' then ((b.salarymin * 40 * 52 - a.salaryfrom * 40 * 52) / a.salaryfrom * 40 * 52) * 100.0) when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 10 then 100
else((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 20 then 80
when (case when paymenttype = 'hourly' then ((b.salarymin * 40 * 52 - a.salaryfrom * 40 * 52) / a.salaryfrom * 40 * 52) * 100.0)
else ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 30 then 60
else 40 end



or did you mean this

select a.clientid,
b.customerid,
case when a.salaryfrom = 0 then 0
when (case when paymenttype = 'hourly' then ((b.salarymin * 40 * 52 - a.salaryfrom * 40 * 52) / a.salaryfrom * 40 * 52) * 100.0)
else ((b.salarymin - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 10 then 100
else ((b.salarymin - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 20 then 80
else ((b.salarymin - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 30 then 60



............
else 40 end -- you can add more cases

 

[amrita418]

Use the second one but you need to replace the else with when

when ((b.salarymin  - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 20 then 80 
when ((b.salarymin  - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 30 then 60 ...

Regards,
AA

 

[jordan11]

sorry another problem
you sent me to version does it matter which one i use

select a.clientid,
b.customerid,
case when a.salaryfrom = 0 then 0
when (case when paymenttype = 'hourly' then ((b.salarymin * 40 * 52 - a.salaryfrom * 40 * 52) / a.salaryfrom * 40 * 52) * 100.0)
when ((b.salarymin - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 10 then 100
when ((b.salarymin - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 20 then 80
when ((b.salarymin - a.salaryfrom) / a.salaryfrom) * 100.0) end) < 30 then 60


else 40 end


and

select a.clientid,
b.customerid,
case when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 10 then 100
when (case when paymenttype = 'hourly' then ((b.salarymin * 40 * 52 - a.salaryfrom * 40 * 52) / a.salaryfrom * 40 * 52) * 100.0)
when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 20 then 80
when ((b.salarymin - a.salaryfrom) / a.Salaryfrom) * 100.0 < 30 then 60
else 40 end


which does not have the end for each when

also when I add
when (case when paymenttype = 'hourly' then ((b.salarymin * 40 * 52 - a.salaryfrom * 40 * 52) / a.salaryfrom * 40 * 52) * 100.0)

i get the error message

Incorrect syntax near ')'.

I can not figure out why this error message is coming up.

 

[amrita418]

A case stmt has only one end stmt.
sample code:

Case when a = 10 then 'Print A'
     when (case when b = 2 then c 
               when b = 3 then c
               else a end) = 20 then 'Print B'
     when a = 30 then 'Print C'
end

Hope that clears the air a bit. Make this change and post your whole code and let us see where the error is.

 

[jordan11]

Thanks amrita,

Here is my code

case
when clientopportunity.salaryto = 0 then 0
when (case when paymenttype = 'hourly' then ((P.salaryminimum * 40 * 52 – c.Salaryfrom * 40 * 52) / c.Salaryfrom* 40 * 52) * 100.0)
when ((P.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 ='0' then 100
when ((p.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 < 10 then 100
when ((p.salaryminimum - c.Salaryto / c..Salaryto) * 100.0 < 20 then 80
when ((p.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 <30 then 60
when ((p.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 >40 then 20
when ((p.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 >60 then 0
else 0 end) AS salary


this is the error message i get when i add

when (case when paymenttype = 'hourly' then ((P.salaryminimum * 40 * 52 – c.Salaryfrom * 40 * 52) / c.Salaryfrom* 40 * 52) * 100.0)


Thanks