case problem

[jordan11]

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

 

[DotNetGnat]

something like this:

CASE WHEN
p.state is NOT NULL THEN
CASE WHEN
cl.AMG='1' THEN 10 ELSE 0 END
...
...

End
End AS qualifi

-DNG

 

[jordan11]

Thanks once again DNG,
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

 

[DotNetGnat]

not clear with your question buddy...

-DNG

 

[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