June 9, 2011 at 11:20 am
I am trying to extract data from a source table where groups of fields in one table can relate to only one field in the same table. I have created some test data to give an example
CREATE TABLE #Cartesis_to_hfm
(
CartesisRU varchar(8)
,CartesisBU varchar(8)
,HFMEntity varchar(10)
)
INSERT INTO #Cartesis_to_hfm
SELECT 'RU303100', 'BU220405', 'NVR_N01_E' UNION ALL
SELECT 'RU303100', 'BU220406', 'NVR_N01_E' UNION ALL
SELECT 'RU303100', 'BU220407', 'NVR_N01_E' UNION ALL
SELECT 'RU303100', 'BU220408', 'NVR_N01_E' UNION ALL
SELECT 'RU303102', 'BU220406', 'NVR_N03_E' UNION ALL
SELECT 'RU303402', 'BU250406', 'NVR_N10_E' UNION ALL
SELECT 'RU305102', 'BU220471', 'NVR_N09_E'
Based on this data applied i would like to only extract data where the combination of :
CartesisRU with any CartesisBU gives the same HFMEntity
In the example above I would only like to output to be
RU303100
this is because only regardless of what CartesisBU is present it there is only one HFMEntity Asscoiated with it. I.E a one to one.
Any ideas?
June 9, 2011 at 11:54 am
How abt this:
select CartesisRU
from #Cartesis_to_hfm
group by CartesisRU
HAVING MAX(HFMEntity) = MIN(HFMEntity)
June 9, 2011 at 11:54 am
June 9, 2011 at 11:56 am
June 9, 2011 at 12:22 pm
Using max and min seems to work. However I must admit I am a little unclear with the logic. Could you explain please.
June 9, 2011 at 12:28 pm
Took me a second to understand it too.
I believe the logic is, if there is one and only one entity for the RU, then show it. (adhering to your 1 to 1 requirement)
so if you had
values
('RU3001','bu001','nn001'),
('RU3001','bu002','nn001'),
('RU3001','bu003','nn002'),
('RU3002','bu010','nn005')
you'd only see RU3002.
This is because the max(entity) for ru3001 = 'nn002' and min(entity) = 'nn001' which are not equal and will not be returned.
June 9, 2011 at 12:33 pm
calvo (6/9/2011)
Took me a second to understand it too.I believe the logic is, if there is one and only one entity for the RU, then show it. (adhering to your 1 to 1 requirement)
so if you had
values
('RU3001','bu001','nn001'),
('RU3001','bu002','nn001'),
('RU3001','bu003','nn002'),
('RU3002','bu010','nn005')
you'd only see RU3002.
This is because the max(entity) for ru3001 = 'nn002' and min(entity) = 'nn001' which are not equal and will not be returned.
but in your exampl, how is max(entity) for ru3001 = 'nn002'.
June 9, 2011 at 12:44 pm
June 9, 2011 at 12:45 pm
calvo (6/9/2011)
Took me a second to understand it too.I believe the logic is, if there is one and only one entity for the RU, then show it. (adhering to your 1 to 1 requirement).
Exactly! If there is only one HFMEntity assignemtn to an RU , then MAX(HFMEntity) will be equal to MIN(HFMENtitiy). You clear on the logic?
June 9, 2011 at 12:57 pm
ok, but in my real example the entities are not in alphabetical order.
June 9, 2011 at 1:09 pm
min() and max() don't care if they are in order or not. The functions do some fancy behind the scenes tempdb sorting stuff which isn't super important (at this point). They can find the min/max value alphabetically or numerically based on the data type.
Here's an example.
create table #temp (col1 varchar(2))
insert into #temp (col1)
values ('a'),('c'),('d'),('e'),('f'),('z'),('j')
select max(col1),min(col1) from #temp
June 9, 2011 at 1:12 pm
Oh ok that makes now. cheers
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply