Link to home
Avatar of Queennie L
Queennie L

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.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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

Avatar of Queennie L
Queennie L



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

Thank you.
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.
Avatar of ste5an
Flag of Germany image

Blurred text
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
^^ 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.
?? OR has the lesser precedence.

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

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

Open in new window


User generated image
For clarity later and overall accuracy, I'd be clear about the conditions:

FROM   TableName
WHERE  PaymentType IN ( 'ACH', 'NON' )
       OR (PaymentType = 'CHK'  AND Amount >= 500)
       OR (PaymentType = 'BOP' AND CompanyName = 'CARE 1ST')
@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.
Thank you for all your help.

I really appreciate it.