June 24, 2010 at 1:57 pm
declare @test-2 table (RowID int IDENTITY, [new proj id] char(5), [old proj id] char(5))
insert into @test-2
SELECT '1150b', '1150/' UNION ALL
SELECT '1150c', '1150a' UNION ALL
SELECT '1150d', '1150b' UNION ALL
SELECT '1150c', '1150e' UNION ALL
SELECT '2134c', '2134/' UNION ALL
SELECT '2134d', '2134b' UNION ALL
SELECT '2134e', '2134c'
and the result i got from code is
1150D,1150/
1150D,1150A---(error)
1150D,1150B
1150E,1150C
1150D,1150D
1150E,1150E
2134D,2134/---(error)
2134D,2134B
2134E,2134C
2134D,2134D
2134E,2134E
where the result set shud be
1150D,1150/
1150E,1150A---shud be
1150D,1150B
1150E,1150C
1150D,1150D
1150E,1150E
2134E,2134/---shud be
2134D,2134B
2134E,2134C
2134D,2134D
2134E,2134E
thankyou
June 24, 2010 at 2:01 pm
hi Falgunoza
all these are the sample data i have like 300 rows like this mixed cases 🙁
June 24, 2010 at 2:17 pm
rahulsony111 (6/24/2010)
declare @test-2 table (RowID int IDENTITY, [new proj id] char(5), [old proj id] char(5))insert into @test-2
SELECT '1150b', '1150/' UNION ALL
SELECT '1150c', '1150a' UNION ALL
SELECT '1150d', '1150b' UNION ALL
SELECT '1150c', '1150e' UNION ALL
SELECT '2134c', '2134/' UNION ALL
SELECT '2134d', '2134b' UNION ALL
SELECT '2134e', '2134c'
and the result i got from code is
1150D,1150/
1150D,1150A---(error)
1150D,1150B
1150E,1150C
1150D,1150D
1150E,1150E
2134D,2134/---(error)
2134D,2134B
2134E,2134C
2134D,2134D
2134E,2134E
where the result set shud be
1150D,1150/
1150E,1150A---shud be
1150D,1150B
1150E,1150C
1150D,1150D
1150E,1150E
2134E,2134/---shud be
2134D,2134B
2134E,2134C
2134D,2134D
2134E,2134E
thankyou
In the test data you supplied, you do not have a 1150e/1150c (new/old) combination. You do have a 1150c/1150e. I believe that this is backwards; if you fix this then it works right.
Oh, here's some nifty new code to do some recursive magic. How does this look?
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
declare @test-2 table (RowID int IDENTITY, [new proj id] varchar(25), [old proj id] varchar(25))
insert into @test-2
SELECT '1150b', '1150/' UNION ALL
SELECT '1150c', '1150a' UNION ALL
SELECT '1150d', '1150b' UNION ALL
SELECT '1150c', '1150e' UNION ALL
SELECT '2134c', '2134/' UNION ALL
SELECT '2134d', '2134b' UNION ALL
SELECT '2134e', '2134c' UNION ALL
SELECT 'SCAS1156C' ,'SCAS1156/' UNION ALL
SELECT 'SCAS1156D' ,'SCAS1156B' UNION ALL
SELECT 'SCAS1156E' ,'SCAS1156C'
;WITH rCTE (Parent, Child) AS
(
SELECT IsNull(t2.[new proj id], t1.[new proj id]), t1.[old proj id]
FROM @test-2 t1
LEFT JOIN @test-2 t2
ON t2.[old proj id] = t1.[new proj id]
UNION ALL
SELECT rCTE.Parent, t1.[old proj id]
FROM @test-2 t1
JOIN rCTE
ON rCTE.Child = t1.[new proj id]
),CTE AS
(
SELECT Parent = MAX(Parent), Child
FROM rCTE
GROUP BY Child
)
SELECT *
FROM CTE
UNION
SELECT t1.Parent, t1.Parent
FROM CTE t1
LEFT JOIN CTE t2
ON t1.Parent = t2.Child
WHERE t2.Child IS NULL
ORDER BY Child
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 24, 2010 at 3:00 pm
Hi Wayne thanks a lot the code is working gud
but i am really new to CTE`s can you please explain the code wat is doing
Thankyou
June 24, 2010 at 3:01 pm
Since you have only 300 rows I don't see any disadvantage of using loop....Try below one...
CREATE TABLE #FinalDest (NewProjectID varchar (25), OldProjectID varchar (25))
DECLARE @TABLE table (NewPID varchar(50), OldPID varchar(50),RowID int)
INSERT INTO @TABLE
select 'SCAS1156C','SCAS1156/',39
UNION ALL
select'SCAS1156D' ,'SCAS1156B',40
UNION ALL
select 'SCAS1156E','SCAS1156C',41
select * from @table
DECLARE @ROWID int, @NewProjectID varchar (max), @OldProjectID varchar (max),@Identifier char(1)
declare Test cursor for
select distinct newpid,oldpid,rowid from @table where right(newpid,1) !='C'
open test
fetch next from test into @NewProjectid,@OldProjectID,@RowID
while @@Fetch_Status = 0
BEGIN
if not exists (select * From #FinalDest where newprojectid = @NewProjectID and OldProjectID = @OldProjectID)
begin
if right (@NewProjectID,1) = 'D'
begin
INSERT INTO #FinalDest (NewProjectID,OldProjectID) select @NewProjectID,@OldProjectID
UNION ALL select @NewProjectID,@NewProjectID
end
else
begin
INSERT INTO #FinalDest (NewProjectID,OldProjectID) select @NewProjectID,@OldProjectID
UNION ALL select @NewProjectID,@NewProjectID
INSERT INTO #FinalDest (NewProjectID,OldProjectID)
select @NewProjectID,OldPID
from @table
where right(Oldpid,1) != 'B'
EXCEPT select newprojectid,oldprojectid from #FinalDest
end
end
else
begin
print '**********Row already exists in Destination Table.**********'
end
fetch next from test into @NewProjectID,@OldProjectID,@RowID
END
CLOSE TEST
DEALLOCATE TEST
select * from #FinalDest ORDER BY NewProjectID,OldProjectID
drop table #FinalDest
FO
June 24, 2010 at 3:53 pm
Thanks Falgunoza,
but the table has hierarchy till E like 2134E and may be the table gets updated timely
do this code works?
can you please explain the Waynes CTE code
Thanks
June 24, 2010 at 6:49 pm
rahulsony111 (6/24/2010)
Hi Wayne thanks a lot the code is working gudbut i am really new to CTE`s can you please explain the code wat is doing
Thankyou
Sigh.
Rahul, up above Falgunoza showed you how to take the code apart to see what it's doing.
For a CTE, all you need to do is interrupt it and put in a select statement to see what's going on.
For example,
;WITH rCTE (Parent, Child) AS
(
SELECT IsNull(t2.[new proj id], t1.[new proj id]), t1.[old proj id]
FROM @test-2 t1
LEFT JOIN @test-2 t2
ON t2.[old proj id] = t1.[new proj id]
)
SELECT * FROM rCTE
Just repeat that to see what's going on at each step, putting pieces back in. It's really pretty easy, and it's faster than expecting others to explain everything to you. Work with it and see how it works yourself. You'll learn more that way.
I will give you one hint: it's a recursive CTE.
Does it work right for you?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 24, 2010 at 10:02 pm
Thanks Wayne its working gud
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply