April 4, 2013 at 9:47 am
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
April 4, 2013 at 9:50 am
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 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/
April 4, 2013 at 10:07 am
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
April 4, 2013 at 10:12 am
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 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/
April 4, 2013 at 10:16 am
There is no more rules than this 😀
April 4, 2013 at 10:24 am
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 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/
April 4, 2013 at 10:49 am
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
April 4, 2013 at 10:49 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
April 4, 2013 at 12:33 pm
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 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/
April 4, 2013 at 12:50 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply