August 5, 2012 at 3:02 am
ChildidChildname Parentidpid
100 Bingo 2001
101 Pingo 2011
102 Zingo 2011
100 Bingo 2012
101 Pingo 2002
102 Zingo 2012
100 Bingo 2013
101 Pingo 2013
102 Zingo 2003
100 Bingo 2004
101 Pingo 2014
102 Zingo 2004
As we can see in this table that ‘100’ has multiple parents for different Pids.
100=200 and 100=201 , So I want to creating a query that will check in previous id whether childid is mapped with new parentid . if parent has changed then create a new childid with same childname.
What about doing same thing in a historical table where the parent of 100 has changed many times.
How to get a data where we can find when all the parent of child has changed from all past Pid’s.
August 6, 2012 at 7:29 pm
I think the reason no one has replied to you in 2 days is because you seem to have posted many questions, but none of them clearly enough for us poor readers (me at least) to understand.
Can you:
1. Provide DDL (table CREATE) statement.
2. Post your input data in a readily consumable format (e.g., SELECT/UNION ALL/SELECT) so that we don't need to do that.
3. Provide an example of what your expected results are.
With these things, you're more likely to get a tested, working solution.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 7, 2012 at 9:08 am
Something like this is what Dwain is talking about.
create table #Something
(
ChildID int,
ChildName varchar(20),
ParentID int,
PID int
)
insert #Something
select 100, 'Bingo', 200, 1 union all
select 101, 'Pingo', 201, 1 union all
select 102, 'Zingo', 201, 1 union all
select 100, 'Bingo', 201, 2 union all
select 101, 'Pingo', 200, 2 union all
select 102, 'Zingo', 201, 2 union all
select 100, 'Bingo', 201, 3 union all
select 101, 'Pingo', 201, 3 union all
select 102, 'Zingo', 200, 3 union all
select 100, 'Bingo', 200, 4 union all
select 101, 'Pingo', 201, 4 union all
select 102, 'Zingo', 200, 4
select * from #Something
drop table #Something
OK so we have a table. However I don't get the relationship here. None of these rows have a ParentID that exists. What is PID? Your explanation of what you want is incredibly unclear.
_______________________________________________________________
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/
August 7, 2012 at 9:48 am
Does PID somehow translated to "historical" versus "present" in your question?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 7, 2012 at 7:16 pm
I've reread the questions now 3 times and still can't figure them out without some expected output.
Nice of you to post DDL though Sean.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 8, 2012 at 7:58 am
Nice of you to post DDL though Sean.
Thanks. Now if we can just the OP to clarify the question we can knock this out in about 3-4 minutes. 😛
_______________________________________________________________
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/
August 8, 2012 at 12:57 pm
create table #abc(insertid int,cityid int,parentcityid int,cityname varchar(50),parentname varchar(50))
insert into #abc
select 1,10,11,'A','B'
union
select 1,11,12,'B','C'
union
select 1,12,13,'C','D'
union
select 2,10,11,'A','B'
union
select 2,11,13,'B','D'
union
select 2,12,11,'C','A'
Output:
Insertid Cityid Parentid cityname parentname
1 10 11 AB
1 11 12 BC
1 12 13 C D
2 10 11 A B
2 11 13 B D
2 12 11 C A
Where ever there is a change in parent for same child for different insertid then there should be a new id assign to old id. Eg: in table for insertid 1 cityid 11 had parent c(id=12) but for insertid 2 cityid 11 has parented as13 . So the desired output should be like:
Insertid Cityid Parentid cityname parentname
1 10 11 A B
1 11 12 B C
1 12 13 C D
2 10 11 A B
2 14 13 B D
2 15 11 C A
I think now the post will be clear.
August 8, 2012 at 1:06 pm
scottichrosaviakosmos (8/8/2012)
create table #abc(insertid int,cityid int,parentcityid int,cityname varchar(50),parentname varchar(50))insert into #abc
select 1,10,11,'A','B'
union
select 1,11,12,'B','C'
union
select 1,12,13,'C','D'
union
select 2,10,11,'A','B'
union
select 2,11,13,'B','D'
union
select 2,12,11,'C','A'
Output:
Insertid Cityid Parentid cityname parentname
1 10 11 AB
1 11 12 BC
1 12 13 C D
2 10 11 A B
2 11 13 B D
2 12 11 C A
Where ever there is a change in parent for same child for different insertid then there should be a new id assign to old id. Eg: in table for insertid 1 cityid 11 had parent c(id=12) but for insertid 2 cityid 11 has parented as13 . So the desired output should be like:
Insertid Cityid Parentid cityname parentname
1 10 11 A B
1 11 12 B C
1 12 13 C D
2 10 11 A B
2 14 13 B D
2 15 11 C A
I think now the post will be clear.
We now have a clean table of sample data. Your desired output looks exactly like select * from #abc so I assume that is what that is.
However, you tried to explain what you want and it doesn't make any sense.
Where ever there is a change in parent for same child for different insertid then there should be a new id assign to old id. Eg: in table for insertid 1 cityid 11 had parent c(id=12) but for insertid 2 cityid 11 has parented as13 .
What does that mean? And what do you want? Do you want a trigger on this table that an update based on some rules that are still unclear (at least to me)? What is cityid? And what is the rule here? Does this table even have a primary key?
_______________________________________________________________
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/
August 8, 2012 at 7:24 pm
Hmmm... Strange requirement indeed. If I'm understanding this correctly, a quirky update might do it.
Set up your table like this:
create table #abc
(insertid int, cityid int, parentcityid int, cityname varchar(50), parentname varchar(50)
,PRIMARY KEY CLUSTERED (insertid, cityid, parentcityid))
insert into #abc
select 1,10,11,'A','B'
union all select 1,11,12,'B','C'
union all select 1,12,13,'C','D'
union all select 2,10,11,'A','B'
union all select 2,11,13,'B','D'
union all select 2,12,11,'C','A'
Then try this, but test it thoroughly against deeper cases to be sure that it works. It seems to for this limited test data anyway.
DECLARE @NextParentID INT = 0, @CityID INT = 0
UPDATE a
SET @NextParentID = CASE WHEN parentcityid >= @NextParentID THEN parentcityid + 1
WHEN CityID >= @NextParentID THEN CityID + 1
ELSE @NextParentID END
,@CityID = CityID = CASE WHEN (
SELECT CityID
FROM #abc b
WHERE b.InsertID = a.InsertID - 1 AND b.cityid = a.cityid AND
b.parentcityid <> a.parentcityid) IS NULL
THEN CityID ELSE CASE WHEN @CityID >= @NextParentID THEN @CityID + 1 ELSE @NextParentID END END
FROM #abc a
OPTION (MAXDOP 1)
SELECT * FROM #abc
DROP TABLE #abc
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 10, 2012 at 11:51 pm
For those of you who have not understood my question. In one line , I have to do a data migration and have to find the child who have multiple parents. So wherever you have multiple parents then assign a new id to the child.
eg;
childid childname parentid
100 abc 200
100 abc 300
desired output
childid childname parentid
100 abc 200
101 abc 300
I hope now this will be clear. And I am doing this for historical data so query ll be quite complicated.
August 15, 2012 at 3:10 pm
scottichrosaviakosmos (8/10/2012)
For those of you who have not understood my question. In one line , I have to do a data migration and have to find the child who have multiple parents. So wherever you have multiple parents then assign a new id to the child.eg;
childid childname parentid
100 abc 200
100 abc 300
desired output
childid childname parentid
100 abc 200
101 abc 300
I hope now this will be clear. And I am doing this for historical data so query ll be quite complicated.
Well shoot. I just got back from a week vacation. I was really hoping my crystal would be back from the shop but alas it is still out for repair. That means I can't help you answer your question because you still have not provided anywhere near enough detail for somebody to help. Slow down, breathe, think about the fact that somebody else can't see your screen, we are not familiar with your project. If you can provide some ddl, sample data and desired output we can help. You might also try to VERY clearly explain your business rules. Otherwise you are on your own.
_______________________________________________________________
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/
August 15, 2012 at 4:05 pm
scottichrosaviakosmos (8/10/2012)
For those of you who have not understood my question. In one line , I have to do a data migration and have to find the child who have multiple parents. So wherever you have multiple parents then assign a new id to the child.eg;
childid childname parentid
100 abc 200
100 abc 300
desired output
childid childname parentid
100 abc 200
101 abc 300
I hope now this will be clear. And I am doing this for historical data so query ll be quite complicated.
I think I finally get this now... you don't actually care what the ChildID is so long as it's changed in such a fashion that each and every ChildID has one and only one ParentID. In this case, it would also be nice if each duplicated ChildID were incremented by 1 in ParentID order. Is that correct?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2012 at 5:22 pm
Scott,
I haven't done a deep dive on his code but it looks like Dwain's code does the trick. Did you test it?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2012 at 6:22 pm
Jeff Moden (8/15/2012)
Scott,I haven't done a deep dive on his code but it looks like Dwain's code does the trick. Did you test it?
I even remembered to use MAXDOP and a clustered index, like you instructed me Master. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 16, 2012 at 6:53 am
dwain.c (8/15/2012)
Jeff Moden (8/15/2012)
Scott,I haven't done a deep dive on his code but it looks like Dwain's code does the trick. Did you test it?
I even remembered to use MAXDOP and a clustered index, like you instructed me Master. 🙂
:-):blush:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply