• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 41
  • Last Modified:

Only show rows with same filename that meets criteria.

Hello Experts,

The SQL output would be only shows the Filenames with all the rows  that meets the criteria below:

1) All [Column 4] that has ACH and NON in  [Column 0] that is "DD"
2) If  [Column 2] shows "Altire" in  [Column 0] that is "HH1" and BOP in  [Column 0] that is "DD"
3) If  [Column 4] shows "CHK" and [Column 2] that is greater than "500" in  [Column 0] and that is "DD"

See attached example.

My query below is not working.

SELECT [UniqueID]
      ,[Column 0]
      ,[Column 1]
      ,[Column 2]
      ,[Column 3]
      ,[Column 4]
 
      ,[Filename]
      ,[RowID]


  FROM dbo.SQLResults

  WHERE [FILENAME] NOT IN (SELECT [FILENAME] FROM (SELECT * FROM dbo.SQLResults 
                           WHERE [Column 4] IN ( 'ACH', 'NON' )
                             OR ([Column 4] = 'CHK'  AND [Column 2] >= '500')
                             OR ([Column 4] = 'BOP' AND [Column 2] = 'Altire'))

Open in new window


Please help.

Thank you for all your help.
OnlyShowFilenameWithCriteriaINWhere.xlsx
0
Queennie L
Asked:
Queennie L
  • 11
  • 5
  • 5
  • +2
1 Solution
 
Bill PrewCommented:
Please reread what you posted and then rewrite more clearly the "criteria" you want to use more clearly and accurately.

For example

and BOP in  [Column 0] that is "DD"

doesn't make sense or isn't clear as worded.

And I don't see any references to [Column 0] in your WHERE clause, even though you mention it in the criteria, why is that?


»bp
0
 
Queennie LAuthor Commented:
@Bill Prew:

That is "BOP" in [Column 4] in [Column 0] under "DD"

Thank you.
0
 
Queennie LAuthor Commented:
Any help please?

Thank you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Queennie LAuthor Commented:
I tried this WHERE CLAUSE:


WHERE  EXISTS  (
    SELECT 1  FROM (SELECT * FROM dbo.SQLResults
                           WHERE [Column 4] IN ( 'ACH', 'NON' )
                             OR ([Column 4] = 'CHK'  AND [Column 2] >= '500')
                             OR ([Column 4] = 'BOP' AND [Column 2] = 'Altire'))
0
 
Bill PrewCommented:
Okay, I still can't write you a query if I don't understand what you want.  Please look at the below and adjust in the same fashion I have written to be accurate.

It sounds like you want to see records in the results when any of three situations exist, correct?

From your first post and later comment here is what I am guessing at.  Please check it and adjust so that we can build a working query.

It's still not clear to me where 'HH1' and 'BOP' should be fit in, or if there is a 4th case, so I left those out for now.

Case 1
    - [Column 0] must equal 'DD'
    - [Column 4] must be either 'ACH' or 'NON'
Case 2
    - [Column 0] must equal 'DD'
    - [Column 2] must equal 'Altire'
Case 3
    - [Column 0] must equal 'DD'
    - [Column 4] must equal 'CHK'
    - [Column 2] must be greater than '500'


»bp
0
 
Queennie LAuthor Commented:
@Bill Prew:

"Case 1
    - [Column 0] must equal 'DD'
    - [Column 4] must be either 'ACH' or 'NON'"

Case 1: Show all rows with the same filename if above condition met. This case is correct.


"Case 2
    - [Column 0] must equal 'DD'
    - [Column 2] must equal 'Altire'"
Case 2: Show all rows with the same filename IF [Column 0] = 'DD' and [Column 4] = 'BOP'
                                                                             AND [Column 0] = 'HH1' and [Column 2] = 'Altire'

Case 3
    - [Column 0] must equal 'DD'
    - [Column 4] must equal 'CHK'
    - [Column 2] must be greater than '500'
0
 
Bill PrewCommented:
Case 2: Show all rows with the same filename IF [Column 0] = 'DD' and [Column 4] = 'BOP'
                                                                             AND [Column 0] = 'HH1' and [Column 2] = 'Altire'
This one I don't understand.  As written you require [Column 0] to be equal to two different values, which can never be true.


»bp
0
 
Queennie LAuthor Commented:
@Bill Prew:

"Case 1
    - [Column 0] must equal 'DD'
    - [Column 4] must be either 'ACH' or 'NON'"

Case 1: Show all rows with the same filename if above condition met. This case is correct.
       



"Case 2
    - [Column 0] must equal 'DD'
    - [Column 2] must equal 'Altire'"
Case 2: Show all rows with the same filename IF ([Column 0] = 'DD' and [Column 4] = 'BOP')
                                                                             AND ([Column 0] = 'HH1' and [Column 2] = 'Altire')

Case 3
    - [Column 0] must equal 'DD'
    - [Column 4] must equal 'CHK'
    - [Column 2] must be greater than '500'
0
 
Queennie LAuthor Commented:
@Bill Prew:

I don't think it is possible.
0
 
Bill PrewCommented:
You seem to have posted the same thing again, which has the same contradiction I mentioned.


»bp
0
 
Queennie LAuthor Commented:
@Bill Prew:

I thought my question does not make any sense. I accidentally deleted it. Sorry.
0
 
Bill PrewCommented:
No problem, it can be hard to explain exactly what you are after sometimes.

Can you try and explain a bit more what criteria you are trying to get with this one:

Case 2: Show all rows with the same filename IF ([Column 0] = 'DD' and [Column 4] = 'BOP')
                                                                             AND ([Column 0] = 'HH1' and [Column 2] = 'Altire')



»bp
0
 
Mark WillsTopic AdvisorCommented:
Anything is possible :)


Psuedo-Code - need to test and check syntax...
; with cte_filename as
(select filename from dbo.SQLResults 
                           WHERE ([Column 4] IN ( 'ACH', 'NON' ) and [column 0] = 'DD')
                             OR ([Column 4] = 'CHK'  AND [Column 2] >= '500' and [column 0] = 'DD' )
                             OR ([Column 0] = 'DD' AND [Column 4] = 'BOP')
                             OR ([Column 0] = 'HH1' AND [Column 2] = 'Altire')
) select s.* 
  from dbo.SQLResults S
 where S.filename in (select filename from cte_filename)

Open in new window

0
 
aikimarkCommented:
How about this:
SELECT [UniqueID]
  ,[Column 0]
  ,[Column 1]
  ,[Column 2]
  ,[Column 3]
  ,[Column 4]
  ,[Filename]
  ,[RowID]


FROM dbo.SQLResults

WHERE (
           [Column 4] IN ( 'ACH', 'NON' )
       OR ([Column 4] = 'CHK'  AND [Column 2] >= '500')
       OR ([Column 4] = 'BOP' AND [Column 2] = 'Altire')
      )

Open in new window

0
 
Queennie LAuthor Commented:
I hope this example will clarify.

I don't think this is possible.

See attached.

@MarkWillis:

I will test it and get back with you.

@AikiMark:

I don't think that query is working. I tried that earlier but it only shows certain based on the criteria but not all rows.

Thank you.
OnlyShowFilenameWithCriteriaINWhere.xlsx
0
 
Mark WillsTopic AdvisorCommented:
correction

instead of :
                             OR ([Column 0] = 'DD' AND [Column 4] = 'BOP')
                             OR ([Column 0] = 'HH1' AND [Column 2] = 'Altire')

could be :
                             OR ([column 2] = 'Altire' and [column 0] in ('BOP','DD'))


Hmmmm... 2nd thoughts... maybe not.....
0
 
Mark WillsTopic AdvisorCommented:
case 2 is a problem...

the only 'BOP' I can see is column 4 and has 'DD' in column 0
the only 'altire i can see has 'HH1' in column 0

which means  : column 2 = 'altire' + column 0 = 'HH1' or  column 0 ='DD' + column 4 = 'BOP'

back to my original...
0
 
aikimarkCommented:
I can get 11111,2222,3333,7777 filenames with this Access query.  However, your criteria fails 44444.  There is no row in the 44444 set that matches  [Column 4] = 'BOP' AND [Column 2] = 'Altire'
There are two separate rows matching one of these two conditions, but no single row that matches both.
SELECT [UniqueID]
  ,[Column 0]
  ,[Column 1]
  ,[Column 2]
  ,[Column 3]
  ,[Column 4]
  ,[Filename]
  ,[RowID]

  FROM Q_range

WHERE
  Exists (
    Select *
    From Q_Range as I
    Where
        [Q_range].Filename = I.Filename And
        I.[Column 4] IN ( 'ACH', 'NON' )
  )
   OR Exists (
    Select *
    From Q_Range as I
    Where 
        [Q_range].Filename = I.Filename And
        I.[Column 4] = 'CHK'  AND I.[Column 2] >= '500'
  )
  OR Exists (
    Select *
    From Q_Range as I
    Where 
        [Q_range].Filename = I.Filename And
        I.[Column 4] = 'BOP' AND I.[Column 2] = 'Altire'
  )

Open in new window

0
 
Queennie LAuthor Commented:
@MarkWillis:

Your SQL query is working but it is still showing CHK >='500' all the amount, it did not limit only to greater than 500. I tried to convert it to this, CONVERT(NVARCHAR(36), [Column 2]) > CAST(500 AS NUMERIC(35,2)) but there is an Error converting data type nvarchar to numeric.

I have to convert it because all values in [Column 2] are different types.

Thank you again.
0
 
Mark WillsTopic AdvisorCommented:
OK,

What version of SQL ? If 2012 or more recent, you can use

TRY_CAST([COLUMN 2] as MONEY) > = 500

otherwise you will need to do something like

( CASE WHEN ISNUMERIC([Column 2]) = 1 THEN CAST([Column 2] as DECIMAL(35,2)) ELSE 0.00 END ) >= 500

but isnumeric() is not fullproof.... it can regard some things as numeric that might cause the CAST() to fail.
1
 
Queennie LAuthor Commented:
@MarkWillis:

OMG! WOW! It is working.

The original output query  is total of 62,521 and with all the criteria meets the output query is total of 53,473.

Thank you again for your help.

I really appreciate it. I will test it more and I will close this as soon as I finish.
0
 
awking00Commented:
with cte as
(select filename from sqlresults where col0= 'DD' and col4 in ('ACH','NON')
 union
 select filename from sqlresults where col0= 'DD' and col4 = 'CHK' and col2 > 500
 union
 (select filename from sqlresults where col0 = 'DD' and col4 = 'BOP'
  intersect
  select filename from sqlresults where col0 = 'HH1' and col2 = 'Altire'))
select * from sqlresults where filename in
(select filename from cte);
0
 
awking00Commented:
Just noticed my unions query would also have to be modified to accommodate converting the nvarchar data to numeric. But it looks like you have a solution anyway.
0
 
Queennie LAuthor Commented:
This is the SQL query that works best for me of my situation. Simple and Clean query to where not a lot of WHERE Clause:

; with cte_filename as
(select filename from dbo.SQLResults
                           WHERE ([Column 4] IN ( 'ACH', 'NON' ) and [column 0] = 'DD')
                             OR ([Column 4] = 'CHK'  AND TRY_CAST([COLUMN 2] AS MONEY) > = 500)) and [column 0] = 'DD' )
                             OR (([Column 0] = 'DD' AND [Column 4] = 'BOP')
                             AND ([Column 0] = 'HH1' AND [Column 2] = 'Altire'))

) select s.*
  from dbo.SQLResults S
 where S.filename in (select filename from cte_filename)


Thank you for all your help Mark and all who participated to make this a success!  Without your intelligence and expertise, I dont' think I can make this.

Thank you again from the bottom of my heart.
1
 
Mark WillsTopic AdvisorCommented:
Nice CTE - Well Done :)
1
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 11
  • 5
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now