Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

advanced subquery Expand / Collapse
Author
Message
Posted Thursday, April 4, 2013 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 10:33 AM
Points: 4, Visits: 2
I would like to get a distinct RefNumber only if this RefNumber has EntryType 0 and 2 and 11. All 3 EntryTypes must be present and no other EntryTypes must be.

RefNumber 239000 must be returned as it contains 0, 2 and 11
RefNumber 236777 must not be returnedsince it also contains a EntryType 7
RefNumber 238888 must not be returned since it lacks the EntryType 2



RefNumber EntryType EntryAmount
----------....---------....-----------
239000............0.............200
239000............0............-200
239000............2............-200
239000............2.............200
239000............11..........-145
239000............11...........145

236777............0.............162
236777............0............-162
236777............2..............-45
236777............2...............45
236777.............7..............62
236777............7..............-62
236777............11...........-162
236777............11............162

238888............0.............345
238888............0............-345
238888............11..........-245
238888............11...........245


Hope somebody can help me, and I'm very thankfull if - thanks in advance!

Benny
Post #1438875
Posted Thursday, April 4, 2013 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 13,324, Visits: 12,809
Like this:

select RefNumber
from YourTable
where RefNumber not in
(
Select RefNumber
from YourTable
where EntryType not in (0, 2, 11)
)



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438878
Posted Thursday, April 4, 2013 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 10:33 AM
Points: 4, Visits: 2

Hi SSCrazy Eights!

THanks for your fast reply :)

I have just edited my post as I didn't mentioned that RefNumber must not be returned if one of the 3 EntryTypes is missing.

You query returns also RefNumber where e.g only 0 and 11 are present, and e.g only 0 and 2 is precent

Benny
Post #1438889
Posted Thursday, April 4, 2013 10:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 13,324, Visits: 12,809
It would much better if you could post your ddl and sample data in a consumable format. (create table and insert statements)

Also, are there any more rules or is this it?


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438892
Posted Thursday, April 4, 2013 10:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 10:33 AM
Points: 4, Visits: 2
There is no more rules than this
Post #1438894
Posted Thursday, April 4, 2013 10:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 13,324, Visits: 12,809
Since you are brand new around here I create the ddl and sample data for you.

if OBJECT_ID('tempdb..#Data') is not null
drop table #Data

create table #Data
(
RefNumber int,
EntryType tinyint,
EntryAmount int
)

insert #Data
select 239000,0,200 union all
select 239000,0,-200 union all
select 239000,2,-200 union all
select 239000,2,200 union all
select 239000,11,-145 union all
select 239000,11,145 union all
select 236777,0,162 union all
select 236777,0,-162 union all
select 236777,2,-45 union all
select 236777,2,45 union all
select 236777,7,62 union all
select 236777,7,-62 union all
select 236777,11,-162 union all
select 236777,11,162 union all
select 238888,0,345 union all
select 238888,0,-345 union all
select 238888,11,-245 union all
select 238888,11,245

select * from #Data

There are a number of ways this could be accomplished. Here is one of those ways.

;with HasAllThree as
(
Select RefNumber, COUNT(distinct EntryType) as MyCount
from #Data
where EntryType in (0, 2, 11)
group by RefNumber
having COUNT(distinct EntryType) = 3
)

select d.RefNumber
from #Data d
join HasAllThree h on h.RefNumber = d.RefNumber
where d.RefNumber not in
(
Select RefNumber
from #Data
where EntryType not in (0, 2, 11)
)
group by d.RefNumber



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438898
Posted Thursday, April 4, 2013 10:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 4, 2013 10:33 AM
Points: 4, Visits: 2

Hi SSCrazy Eights

Thank you so much! It is exactly what I was looking for. It is bit more advanced than I'm used to when it comes to sql

I will remember to provide create tale + data next time

Best regards
Benny
Post #1438906
Posted Thursday, April 4, 2013 10:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 2,325, Visits: 3,502
SELECT RefNumber
FROM #Data d
GROUP BY RefNumber
HAVING
COUNT(DISTINCT EntryType) = 3 AND
MAX(CASE WHEN EntryType NOT IN ( 0, 2, 11 ) THEN 1 ELSE 0 END) = 0



SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1438907
Posted Thursday, April 4, 2013 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 13,324, Visits: 12,809
ScottPletcher (4/4/2013)
SELECT RefNumber
FROM #Data d
GROUP BY RefNumber
HAVING
COUNT(DISTINCT EntryType) = 3 AND
MAX(CASE WHEN EntryType NOT IN ( 0, 2, 11 ) THEN 1 ELSE 0 END) = 0



Nice one Scott. I knew something along these lines but it just wasn't coming to me.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1438970
Posted Thursday, April 4, 2013 12:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 2,325, Visits: 3,502
Sean Lange (4/4/2013)
ScottPletcher (4/4/2013)
SELECT RefNumber
FROM #Data d
GROUP BY RefNumber
HAVING
COUNT(DISTINCT EntryType) = 3 AND
MAX(CASE WHEN EntryType NOT IN ( 0, 2, 11 ) THEN 1 ELSE 0 END) = 0



Nice one Scott. I knew something along these lines but it just wasn't coming to me.


Yeah, the query plan is rather ugly, but to me that's just a weakness of the optimizer: it should be able o better optimize that code, and resolve the query in a single pass of the table.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1438984
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse