Show SQL output query results based on certain criteria.

I have a data that I want to query but the problem is it does not show what I wanted. I know this is just a simple SQL query but I cannot get the logic.

Rules to the SQL query:

1) Show all data if ACH.
2) Show all data if NON.
3) Show data for CHK if the Amount is greater or equal to $500.00
4) Show data for BOP if the CompanyName is equal to CARE 1st.

See attached file.

Please help.

I really appreciate any help.

Thank you.
Query_to_Include_OR_not_to_Include.xlsx
Queennie LAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here you go.  Substitute TableName for whatever your table name is.

>1) Show all data if ACH.
SELECT * FROM TableName WHERE PaymentType='ACH'

Open in new window


>2) Show all data if NON.
SELECT * FROM TableName WHERE PaymentType='NON'

Open in new window


>3) Show data for CHK if the Amount is greater or equal to $500.00
SELECT * FROM TableName WHERE PaymentType='CHK' AND Amount >= 5000

Open in new window


>4) Show data for BOP if the CompanyName is equal to CARE 1st.
SELECT * FROM TableName WHERE PaymentType='BOP' AND CompanyName = 'CARE 1ST'

Open in new window

0
Queennie LAuthor Commented:
@JimHorn:

Do I have to UNION ALL for all those querries to get the output results?

Thank you.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Is it your intent to display all four results in a single set?  
If yes, use UNION if you wish to eliminate duplicates between the four queries, and UNION ALL if duplicates are okay.

fyi use of UNION / UNION ALL requires all sets to have the same exact column schema, so if it's a SELECT * FROM TableName then you're good.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ste5anSenior DeveloperCommented:
E.g.

SELECT *
FROM   TableName
WHERE  PaymentType IN ( 'ACH', 'NON' )
       OR PaymentType = 'CHK'
          AND Amount >= 500
       OR PaymentType = 'BOP'
          AND CompanyName = 'CARE 1ST';

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
^^ ste5an - That works too once parentheses ( ) are added after the OR's.  Assuming that's what he wants it's better to do this in one query vs. four.
0
ste5anSenior DeveloperCommented:
?? OR has the lesser precedence.

DECLARE @Sample TABLE
    (
        UniqueID INT ,
        EmployeeName NVARCHAR(150) ,
        PaymentType NVARCHAR(150) ,
        Amount DECIMAL(18, 2) ,
        CompanyName NVARCHAR(150)
    );

INSERT INTO @Sample ( UniqueID ,
                      EmployeeName ,
                      PaymentType ,
                      Amount ,
                      CompanyName )
VALUES ( 1, 'Employee1', 'ACH', 201.00, 'A & B' ) ,
       ( 2, 'Employee2', 'CHK', 650.00, 'All B' ) ,
       ( 3, 'Employee3', 'ACH', 50.00, 'AET' ) ,
       ( 4, 'Employee4', 'CHK', 22.00, 'BLUES' ) ,
       ( 5, 'Employee5', 'CHK', 545.00, 'OMAHA' ) ,
       ( 6, 'Employee6', 'BOP', 35.00, 'CARE 1ST' ) ,
       ( 7, 'Employee7', 'NON', 0.00, 'GHM' ) ,
       ( 8, 'Employee8', 'BOP', 312.00, 'ADDEL' ) ,
       ( 9, 'Employee9', 'NON', 0.00, 'UMR' ) ,
       ( 10, 'Employee10', 'CHK', 15.00, 'UMR' ) ,
       ( 11, 'Employee11', 'BOP', 150.00, 'CARE 1ST' ) ,
       ( 12, 'Employee12', 'CHK', 1050.00, 'GHM' ) ,
       ( 13, 'Employee13', 'BOP', 12.00, 'CARE 1ST' ) ,
       ( 14, 'Employee14', 'CHK', 15.00, 'MCR' ) ,
       ( 15, 'Employee15', 'CHK', 715.00, 'UMR' );

SELECT *
FROM   @Sample
WHERE  PaymentType IN ( 'ACH', 'NON' )
       OR PaymentType = 'CHK'
          AND Amount >= 500
       OR PaymentType = 'BOP'
          AND CompanyName = 'CARE 1ST';

Open in new window

.

Capture.PNG
0
Scott PletcherSenior DBACommented:
For clarity later and overall accuracy, I'd be clear about the conditions:

SELECT *
FROM   TableName
WHERE  PaymentType IN ( 'ACH', 'NON' )
       OR (PaymentType = 'CHK'  AND Amount >= 500)
       OR (PaymentType = 'BOP' AND CompanyName = 'CARE 1ST')
0
Queennie LAuthor Commented:
@ste5an and Scott Pletcher.

Oh wow. It is really working. It took me 2 days to figure this out.

You are all smart people.

I will do more testing to make sure it aligned to my data  and will let you know.

Thank you again for all the help.
0
Queennie LAuthor Commented:
Thank you for all your help.

I really appreciate it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.