need help to write SQL

How to write a SQL for the following?  Thanks for the help!

INQREG      Wtd avg pay      Factor
<=0      <= 22                       0.905
<=0      > 22                       0.905
>0      <= 22                       1.000
>0      > 22                       1.257
jjxia2001Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sanofi-aventisConnect With a Mentor Commented:
This could also be used in the WHERE clause like this

Proc SQL;

      SELECT T2.*, T1.*
      FROM TEST    AS T1,
           FACTORS AS T2
      WHERE Case When T1.INQREG <= 0
                       THEN 0.905
                       WHEN T1.Wtd_avg_pay <= 22
                      THEN 1.000
                      ELSE 1.257 END = T2.Factor;

QUIT;

T-Bone
0
 
nemws1Database AdministratorCommented:
You didn't provide very much information (please provide as much information as possible when posting questions!)  What is your table structure?  What does "Factor" mean?

The conditions you list will display all results (you have all cases enumerated).  Are you looking for separate queries for each?
0
 
sanofi-aventisCommented:
I would assume your trying to get the appropriate factor.

This versin matches exacly what you have above.
Case When INQREG <= 0
     THEN ( CASE WHEN Wtd_avg_pay <= 22
                 THEN 0.905
                 ELSE 0.905 END )
     ELSE ( CASE WHEN Wtd_avg_pay <= 22
                 THEN 1.000
                 ELSE 1.257 END )
     END AS Factor

But could be simplified since the rate is identical if INQREG <= 0 no mater what the week-to-date average pay is
Case When INQREG <= 0
     THEN 0.905
     WHEN Wtd_avg_pay <= 22
     THEN 1.000
     ELSE 1.257 END AS Factor

Test SQL;
Proc SQL;

      SELECT Case When INQREG <= 0
                      THEN 0.905
                      WHEN Wtd_avg_pay <= 22
                      THEN 1.000
                      ELSE 1.257 END AS Factor1,
               Case When INQREG <= 0
                      THEN ( CASE WHEN Wtd_avg_pay <= 22
                                  THEN 0.905
                                  ELSE 0.905 END )
                      ELSE ( CASE WHEN Wtd_avg_pay <= 22
                                  THEN 1.000
                                  ELSE 1.257 END )
                      END AS Factor2
        FROM WORK.TEST;

QUIT;


Best of luck;
T-Bone

     
0
 
jjxia2001Author Commented:
thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.