Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

need help to write SQL

Posted on 2011-09-28
4
Medium Priority
?
392 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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