SPLIT DATA SET IN TWO
-
I am trying to split a data set into 2 sets using t-sql. I want on data set to have values based on certain criteria and the other side to contain whats left. I have created some sample data to explain my situation.
CREATE TABLE #Cartesis_to_hfm
(
d_ru varchar(8)
d_ot varchar(8)
d_te varchar(8)
)
INSERT INTO #Cartesis_to_hfm
SELECT 'RU303100', 'IM06', 'NULL' UNION ALL
SELECT 'RU303100', 'DE55', 'TM06' UNION ALL
SELECT 'RU303100', 'NULL', 'NULL' UNION ALL
SELECT 'RU303100', 'DE10', 'NULL' UNION ALL
SELECT 'RU303100', 'NULL', 'TM05' UNION ALL
SELECT 'RU303100', 'DE20', 'TM06' UNION ALL
SELECT 'RU303100', 'DE20', 'TM05'
in the case above I would like to select data where d_ot = im06 or d_te = tm06. The other data dataset should could contain the remainder. The query I use to to select the the first dataset is as follows:
SELECT * FROM #Cartesis_to_hfm
WHERE (D_OT = 'IM06' OR D_TE = 'TM06')
This works fine the result set is:
RU303100 IM06 NULL
RU303100 DE55 TM06
RU303100 DE20 TM06
For the other dataset what is left over, should be 4 rows left
In my real data I am dealing with 10 thousand rows. I need a query that will pull all the data where d_ot doesn't equal 'im06' or d_ot doesn't equal 'tm06'. This will include nulls also.The sum of the rows in the 2 queries should equal the total number of rows in the original dataset.
I cannot get both sets of data to reconcile. The first query works fine but I cannot produce the correct amount from the second query.
The query I am using for the second set is as follows.
SELECT * FROM #Cartesis_to_hfm
WHERE
(D_OT <> 'IM06' and D_TE is null)
or
(d_te <> 'tm06' and d_ot is null)
or
(d_te is null and d_ot is null)
Any ideas on how I can do this
-
not sure if I am missing something - have you tried
SELECT * FROM #Cartesis_to_hfmWHERE (D_OT <> 'IM06' and D_TE <> 'TM06')
?
-
You can use EXCEPT
SELECT * FROM #Cartesis_to_hfmEXCEPT
SELECT * FROM #Cartesis_to_hfm
WHERE (D_OT = 'IM06' OR D_TE = 'TM06')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Was missing the nulls previously...slight revision
SELECT * FROM #Cartesis_to_hfmWHERE (D_OT <> 'IM06' and D_TE <> 'TM06')
or (D_oT is null And d_te is null)
or (D_oT <> 'IM06' And d_te is null)
or (D_oT is null and D_TE <> 'TM06')
-
Dave provided the simplest (and best) answer for question asked, however if you are interested in exploring alternative methods of arriving at the same answer you could try:
with cte1 as (
SELECT * FROM #Cartesis_to_hfm
),
cte2 as (
SELECT * FROM #Cartesis_to_hfm
WHERE (D_OT = 'IM06' OR D_TE = 'TM06')
)
select * from cte1
except
select * from cte2;
The above might provide some ideas in the event your select/where statements need to get more complicated. This demonstrates the use of the "except" operator for set based operations.
Just a thought.
-
Thanks a lot. I totally forgot about the EXCEPT statement. That worked fine. much appreciated
-
Just curious:
Is the except statement just syntactic sugar for "Where Not IN" or is there some additional optimization magic that goes on behind the scenes?
-
Jeremy-475548 (6/28/2011)
Just curious:Is the except statement just syntactic sugar for "Where Not IN" or is there some additional optimization magic that goes on behind the scenes?
It's quite different from WHERE NOT IN because it evaluates and excludes rows based all columns in the sets being compared.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply