June 10, 2011 at 3:05 am
This is essentially a follow up of my 2 previous posts. I am working on a migration project and keep finding out differenr scenarios with the migrating data.
I am now trying to extract 2 sets of data from my source table. Again I have added sample code for my clarity.
CREATE TABLE #Cartesis_to_hfm
(
CartesisRU varchar(8)
,CartesisBU varchar(8)
,HFMEntity varchar(10)
,WBU varchar(3)
)
INSERT INTO #Cartesis_to_hfm
SELECT 'RU303100', 'BU220405', 'NVR_N01_E','4S8' UNION ALL
SELECT 'RU303100', 'BU220405', 'NVR_N02_E','4S7' UNION ALL
SELECT 'RU303100', 'BU220405', 'NVR_N03_E','4SB' UNION ALL
SELECT 'RU303101', 'BU220405', 'NVR_N06_E','5S9' UNION ALL
SELECT 'RU303103', 'BU220406', 'NVR_N03_K','7RK' UNION ALL
SELECT 'RU303402', 'BU250406', 'NVR_N10_E','6SB' UNION ALL
SELECT 'RU305102', 'BU220471', 'NVR_N09_E','6KK'
Based on this data applied i would like to only extract data for one dataset where
the same combination of CartesisRU & CartesisBU provides a different HFMEntity
and for another dataset where the each unique combination of CartesisRU & CartesisBU provides a a unique HFMEntity
In the example above I would only like to output for the first data set
RU303100, BU220405, NVR_N01_E
RU303100, BU220405, NVR_N02_E
RU303100, BU220405, NVR_N03_E
And in the other data set
RU303103, BU220406, NVR_N03_K,
RU303402, BU250406, NVR_N10_E,
RU305102, BU220471, NVR_N09_E,
Any ideas?
June 10, 2011 at 3:47 am
Hi
-- for dataset 1
select * from #Cartesis_to_hfm a where exists(
select CartesisRU,CartesisBU,COUNT(*) from #Cartesis_to_hfm b
where a.CartesisBU = b.CartesisBU and a.CartesisRU = b.CartesisRU
group by CartesisRU,CartesisBU having COUNT(*)>1)
-- for dataset 2
select * from #Cartesis_to_hfm a where exists(
select CartesisRU,CartesisBU,COUNT(*) from #Cartesis_to_hfm b
where a.CartesisBU = b.CartesisBU and a.CartesisRU = b.CartesisRU
group by CartesisRU,CartesisBU having COUNT(*)=1)
Regards
Siva Kumar J.
June 10, 2011 at 8:34 am
Thanks a lot. That seems to have done trick.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply