Stuck on Query to Conditionally Exclude Data

Question

Post reply

Stuck on Query to Conditionally Exclude Data

jkalmar 43328

Say Hey Kid

Points: 693
More actions
August 25, 2014 at 10:52 am

#298709

Hi. I have a table with columns KitID, BatteryID, TestID and LBSTAT. I need to exclude rows where LBSTAT='NOT DONE', but ONLY if there's not a row with a different BatteryID but the same KitID and TestID where LBSTAT<>'NOT DONE'. I'm trying to do this in the simplest way possible. I just can't seem to come up with the SQL code. Can anyone point me in the right direction? Thanks!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

rxm119528 SSCrazy Points: 2960 More actions · Answer 1

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

jkalmar 43328 (8/25/2014)
Hi. I have a table with columns KitID, BatteryID, TestID and LBSTAT. I need to exclude rows where LBSTAT='NOT DONE', but ONLY if there's not a row with a different BatteryID but the same KitID and TestID where LBSTAT<>'NOT DONE'. I'm trying to do this in the simplest way possible. I just can't seem to come up with the SQL code. Can anyone point me in the right direction? Thanks!

First of all, welcome to the forum. To help us help you, follow the link previously posted and look at the article, it helps you put forward the information we need for providing answers to your question.

😎

The problem sounds straight forward, all we need now are the additional information from you and I'm certain we'll have an answer in no time.

jkalmar 43328 Say Hey Kid Points: 693 More actions · Answer 3

OK, let me try to get you guys what you need...

CREATE TABLE PRE_LOAD(

KitID varchar(20) NULL,

BatteryID varchar(20) NULL,

TestID varchar(20) NULL,

LBSTAT varchar(8) NULL

)

INSERT INTO PRE_LOAD

(KitID, BatteryID, TestID, LBSTAT)

SELECT 'C1473045850','T1878','3730','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3731','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3732','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3733','NOT DONE' UNION ALL

SELECT 'C1473045850','T1954','3730','' UNION ALL

SELECT 'C1473045850','T1954','3731','' UNION ALL

SELECT 'C1473045850','T1954','3732','' UNION ALL

SELECT 'C1473045850','T1954','3733',''

--DROP TABLE PRE_LOAD

I need to exclude all the rows for T1878 because the LBSTAT column is 'NOT DONE' for those rows, but if T1954 (where LBSTAT is blank, or anything other than 'Not Done') did not exist I would NOT want to exclude T1878. Hope that's clear enough...

rxm119528 SSCrazy Points: 2960 More actions · Answer 4

jkalmar 43328 (8/25/2014)
OK, let me try to get you guys what you need...
CREATE TABLE PRE_LOAD(
KitID varchar(20) NULL,
BatteryID varchar(20) NULL,
TestID varchar(20) NULL,
LBSTAT varchar(8) NULL
)
INSERT INTO PRE_LOAD
(KitID, BatteryID, TestID, LBSTAT)
SELECT 'C1473045850','T1878','3730','NOT DONE' UNION ALL
SELECT 'C1473045850','T1878','3731','NOT DONE' UNION ALL
SELECT 'C1473045850','T1878','3732','NOT DONE' UNION ALL
SELECT 'C1473045850','T1878','3733','NOT DONE' UNION ALL
SELECT 'C1473045850','T1954','3730','' UNION ALL
SELECT 'C1473045850','T1954','3731','' UNION ALL
SELECT 'C1473045850','T1954','3732','' UNION ALL
SELECT 'C1473045850','T1954','3733',''
--DROP TABLE PRE_LOAD
I need to exclude all the rows for T1878 because the LBSTAT column is 'NOT DONE' for those rows, but if T1954 (where LBSTAT is blank, or anything other than 'Not Done') did not exist I would NOT want to exclude T1878. Hope that's clear enough...

If i understand it correctly, nothiing will be excluded from your test data. is that correct?

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Something this should work.

select *

from PRE_LOAD

where BatteryID not in

(

select BatteryID

from PRE_LOAD

where LBSTAT = 'NOT DONE'

)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

According to what I understood, this should work:

select *

from PRE_LOAD ex

where EXISTS

(

select 1

from PRE_LOAD i

where LBSTAT <> 'NOT DONE'

AND ex.kitID = i.KitID

)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mickyT SSChampion Points: 10360 More actions · Answer 7

Hi

My understanding of the requirements is slightly different, so this may be way wrong:w00t:

SELECT p.KitID,

p.BatteryID,

p.TestID,

p.LBSTAT

FROM PRE_LOAD p

CROSS APPLY (

SELECT COUNT(*) chk

FROM PRE_LOAD c

WHERE p.KitID = c.KitID AND

p.TestID = c.TestID AND

c.LBSTAT <> 'NOT DONE'

) x

WHERE LBSTAT <> 'NOT DONE' OR

x.chk = 0

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 8

jkalmar 43328 (8/25/2014)
OK, let me try to get you guys what you need...
CREATE TABLE PRE_LOAD(
KitID varchar(20) NULL,
BatteryID varchar(20) NULL,
TestID varchar(20) NULL,
LBSTAT varchar(8) NULL
)
INSERT INTO PRE_LOAD
(KitID, BatteryID, TestID, LBSTAT)
SELECT 'C1473045850','T1878','3730','NOT DONE' UNION ALL
SELECT 'C1473045850','T1878','3731','NOT DONE' UNION ALL
SELECT 'C1473045850','T1878','3732','NOT DONE' UNION ALL
SELECT 'C1473045850','T1878','3733','NOT DONE' UNION ALL
SELECT 'C1473045850','T1954','3730','' UNION ALL
SELECT 'C1473045850','T1954','3731','' UNION ALL
SELECT 'C1473045850','T1954','3732','' UNION ALL
SELECT 'C1473045850','T1954','3733',''
--DROP TABLE PRE_LOAD
I need to exclude all the rows for T1878 because the LBSTAT column is 'NOT DONE' for those rows, but if T1954 (where LBSTAT is blank, or anything other than 'Not Done') did not exist I would NOT want to exclude T1878. Hope that's clear enough...

Good job with the data sample, just a quick favour, could you post the expected result set?

😎

jkalmar 43328 Say Hey Kid Points: 693 More actions · Answer 9

rxm119528 - no, all the rows where BatteryId = 'T1878' would be excluded because there is another BatteryId (T1954) where LBSTAT <> 'NOT DONE'.

jkalmar 43328 Say Hey Kid Points: 693 More actions · Answer 10

Eirikur, with this test data the expected result set would be:

C1473045850 T1954 3730 ''

C1473045850 T1954 3731 ''

C1473045850 T1954 3732 ''

C1473045850 T1954 3733 ''

(not sure how to indicate the blanks so I used empty quotes, hope that's OK).

mickyT SSChampion Points: 10360 More actions · Answer 11

What would you expect for the following data?

INSERT INTO PRE_LOAD

(KitID, BatteryID, TestID, LBSTAT)

SELECT 'C1473045850','T1878','3730','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3731','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3732','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3733','NOT DONE' UNION ALL

SELECT 'C1473045850','T1954','3730','' UNION ALL

SELECT 'C1473045850','T1954','3731',''

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

jkalmar 43328 (8/25/2014)
Eirikur, with this test data the expected result set would be:
C1473045850 T1954 3730 ''
C1473045850 T1954 3731 ''
C1473045850 T1954 3732 ''
C1473045850 T1954 3733 ''
(not sure how to indicate the blanks so I used empty quotes, hope that's OK).

Seems that what I posted should work for you then?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 13

jkalmar 43328 (8/25/2014)
Eirikur, with this test data the expected result set would be:
C1473045850 T1954 3730 ''
C1473045850 T1954 3731 ''
C1473045850 T1954 3732 ''
C1473045850 T1954 3733 ''
(not sure how to indicate the blanks so I used empty quotes, hope that's OK).

Good stuff, think I might be missing something though as the following query will produce this results but feels kind of "too simple" given the original requirements

😎

SELECT

*

FROM dbo.PRE_LOAD PL

WHERE PL.LBSTAT <> 'NOT DONE'

Sean Lange already posted a solution that produces the same results, is that what you need?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 14

Here's an extended set of sample data and the solutions posted. I guess Micky's got the correct formula.

CREATE TABLE PRE_LOAD(

KitID varchar(20) NULL,

BatteryID varchar(20) NULL,

TestID varchar(20) NULL,

LBSTAT varchar(8) NULL

)

INSERT INTO PRE_LOAD

(KitID, BatteryID, TestID, LBSTAT)

SELECT 'C1473999999','T5555','3730','NOT DONE' UNION ALL

SELECT 'C1473999999','T5555','3731','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3730','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3731','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3732','NOT DONE' UNION ALL

SELECT 'C1473045850','T1878','3733','NOT DONE' UNION ALL

SELECT 'C1473045850','T1954','3730','' UNION ALL

SELECT 'C1473045850','T1954','3731','' UNION ALL

SELECT 'C1473045850','T1954','3732','' UNION ALL

SELECT 'C1473045850','T1954','3733','' UNION ALL

SELECT 'C1111111111','T1234','3737','' UNION ALL

SELECT 'C1111111111','T4444','3738',''

select *

from PRE_LOAD

where BatteryID not in

(

select BatteryID

from PRE_LOAD

where LBSTAT = 'NOT DONE'

)

select *

from PRE_LOAD ex

where EXISTS

(

select 1

from PRE_LOAD i

where LBSTAT <> 'NOT DONE'

AND ex.kitID = i.KitID

)

SELECT p.KitID,

p.BatteryID,

p.TestID,

p.LBSTAT

FROM PRE_LOAD p

CROSS APPLY (

SELECT COUNT(*) chk

FROM PRE_LOAD c

WHERE p.KitID = c.KitID AND

p.TestID = c.TestID AND

c.LBSTAT <> 'NOT DONE'

) x

WHERE LBSTAT <> 'NOT DONE' OR

x.chk = 0

GO

DROP TABLE PRE_LOAD

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2