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?
 
ste5anConnect With a Mentor Senior 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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
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
 
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
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.