Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

need help to write SQL

Posted on 2011-09-28
4
Medium Priority
?
406 Views
Last Modified: 2013-11-16
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
0
Comment
Question by:jjxia2001
  • 2
4 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 36812872
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
 
LVL 2

Expert Comment

by:sanofi-aventis
ID: 36815076
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
 
LVL 2

Accepted Solution

by:
sanofi-aventis earned 2000 total points
ID: 36815129
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
 

Author Closing Comment

by:jjxia2001
ID: 36818157
thanks!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question