asked on

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

Jim Horn

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

Queennie L

ASKER

@JimHorn:

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

Thank you.

Jim Horn

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.

ASKER CERTIFIED SOLUTION

ste5an

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Jim Horn

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

ste5an

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

Scott Pletcher

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')

Queennie L

ASKER

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

Queennie L

ASKER

Thank you for all your help.

I really appreciate it.