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

 

[amrita418]

You are trying to do too many things in one shot. Take it step by step. Test small part of the code then build on it.

Try this case stmt and see what you get:

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

Regards,
AA

 

[amrita418]

oops, you do not need the last brace ')'

change
else 0 end) AS salary

to
else 0 end AS salary

 

[jordan11]

amrita, I am sorry but i still get the error

Incorrect syntax near '–'

and this line is

when (case when paytype = 'hourly'


I have tried to solve it but it will not go away
thanks

 

[amrita418]

Can you post the whole code?

 

[jordan11]

hi,

this is my full code
SELECT distinct C.oID, Phy.pid
,(CASE WHEN C.CLocationState = pp.PreferredState THEN 20 ELSE 0 END

+CASE WHEN C.CLocationState = p.med THEN 50 ELSE 0 END
+CASE WHEN C.CLocationState = pp.StateBirth THEN 10 ELSE 0 END) AS geography
,(case
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

,(CASE WHEN p.med IS NULL THEN 0
WHEN p.med= 'int' then 0 else CONVERT(int,C.AMG)*10 END
+CASE WHEN p.med IS NULL THEN 0
WHEN p.med= 'int' then CONVERT(int,C.IMG)*10 else 0 END
+CASE WHEN p.med IS NULL THEN 0 ELSE CONVERT(int,C.RESGRAD)*10 END
+ CASE WHEN p.med IS NULL THEN 0 ELSE CONVERT(int,c.PRACPHY)* 10 end) AS qualifi
,(CASE WHEN C.bs = P.pbs THEN 40 ELSE 0 END) AS qualifications
,(CASE WHEN C.bs = P.pbs THEN 40 ELSE 0 END) AS lifestyle
FROM C
LEFT JOIN Pc
ON Pc.physpecialty = C.specialty
AND UPPER(C.specialty) = 'ar'
INNER JOIN pp
ON pp.Phy.pid = Pc.Phy.pid
INNER JOIN P
ON P.Phy.pid =Pc.Phy.pid

thanks

 

[amrita418]

Try this and if you have issues please provide create table scripts with some sample data.

SELECT distinct 
       C.oID, 
       Phy.pid,
       CASE WHEN C.CLocationState = pp.PreferredState THEN 20 
            WHEN C.CLocationState = p.med THEN 50 
            WHEN C.CLocationState = pp.StateBirth THEN 10 ELSE 0 END AS geography,
       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               
                       else ((P.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 end < 10 then 100
             when case when paymenttype = 'hourly' 
                            then  ((P.salaryminimum * 40 * 52 – c.Salaryfrom * 40 * 52) / c.Salaryfrom* 40 * 52) * 100.0               
                       else ((P.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 end  < 20 then 80
             when case when paymenttype = 'hourly' 
                            then  ((P.salaryminimum * 40 * 52 – c.Salaryfrom * 40 * 52) / c.Salaryfrom* 40 * 52) * 100.0               
                       else ((P.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 end  < 30 then 60
             when case when paymenttype = 'hourly' 
                            then  ((P.salaryminimum * 40 * 52 – c.Salaryfrom * 40 * 52) / c.Salaryfrom* 40 * 52) * 100.0               
                       else ((P.salaryminimum - c.Salaryto) / c.Salaryto) * 100.0 end   >60 then 20
             else 0 end) AS salary,
       CASE WHEN p.med IS NULL or p.med = 'int' THEN CONVERT(int,C.IMG)*10 
            else CONVERT(int,C.AMG)* 10 END
       + CASE WHEN p.med IS NULL THEN 0 ELSE CONVERT(int,C.RESGRAD)*10  + CONVERT(int,c.PRACPHY)* 10 END AS qualifi,
       CASE WHEN C.bs = P.pbs THEN 40 ELSE 0 END) AS qualifications,
       CASE WHEN C.bs = P.pbs THEN 40 ELSE 0 END) AS lifestyle
FROM   C
       LEFT JOIN Pc
       ON Pc.physpecialty = C.specialty
       AND UPPER(C.specialty) = 'ar'
       INNER JOIN pp 
       ON pp.Phy.pid = Pc.Phy.pid
       INNER JOIN P 
       ON P.Phy.pid =Pc.Phy.pid

Regards,
AA