InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 16:06:11 GMT20http://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspxThanks everyone.Tue, 11 Sep 2012 10:31:03 GMTchrismathttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspx[quote][b]chrismat (9/11/2012)[/b][hr][quote][b]Kingston Dhasian (9/11/2012)[/b][hr]What are the expected results based on your data?[/quote]If you copy/paste my post into SSMS and run it, the last active result set contains some question marks. My expected results are the question marks need to be incrementing numbers; the number is based on MAX(Sibling)+1 for the group Sponsor=4; in this example they would start at 3, but in general it could start at any number depending on what the max(sibling) returned for a given group.[/quote]The question is, based on the sample data, what should the expected output look like. This means we would like you to play the computer and manually generate the result set you are expecting the code to actually generate.Tue, 11 Sep 2012 09:55:10 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspx[quote][b]Kingston Dhasian (9/11/2012)[/b][hr]What are the expected results based on your data?[/quote]If you copy/paste my post into SSMS and run it, the last active result set contains some question marks. My expected results are the question marks need to be incrementing numbers; the number is based on MAX(Sibling)+1 for the group Sponsor=4; in this example they would start at 3, but in general it could start at any number depending on what the max(sibling) returned for a given group.Tue, 11 Sep 2012 09:52:06 GMTchrismathttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspxAnother guess:[code="sql"]select activity, a.sponsor 'OldSponsor', sibling 'OldSibling', 4 'NewSponsor'--, '?' 'NewSibling' --first result should be 3, second 4, etc based on max(sibling)+x for group sponsor=4, ( SELECT MAX(sibling) FROM @activity WHERE sponsor = 4 GROUP BY sponsor) + ROW_NUMBER() OVER (ORDER BY activity) 'NewSibling'from ( SELECT activity, sponsor, sibling FROM @activity where sponsor <> 4 group by activity, sponsor, sibling) a[/code]Tue, 11 Sep 2012 02:37:06 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspxBit of a guess.[code="sql"]SELECT activity, oldSponser, oldSibling, newSponser, start+rn AS newSiblingFROM (SELECT activity, sponsor, sibling, 4, ROW_NUMBER() OVER(ORDER BY activity) FROM @activity WHERE sponsor <> 4 GROUP BY activity, sponsor, sibling) a(activity,oldSponser,oldSibling,newSponser,rn)CROSS APPLY (SELECT MAX(sibling) FROM @activity WHERE sponsor = 4) b(start);[/code]In case I'm wrong and this is a running total problem, [url=http://www.sqlservercentral.com/articles/T-SQL/68467/]check out this awesome article by Jeff Moden --> http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]Tue, 11 Sep 2012 01:35:11 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspxWhat are the expected results based on your data?Tue, 11 Sep 2012 01:12:08 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspxdeclare @activity table ( activity int, sponsor int, sibling int);insert into @activity (activity,sponsor,sibling)select 0,4,0 unionselect 1,4,1 unionselect 2,4,2 unionselect 3,2,0 unionselect 4,2,1 unionselect 5,6,0 ;select *from @activity;--notice for each group of sponsor, --the sibling increments 1 for each record; --ie max(sibling) for sponsor=4 would return 2--get the sponsor and sibling for activities not set to sponsor 4, --start the NewSibling at max(sibling)+1 for group sponsor=4,--and increment each one in the resultsetselect activity, sponsor 'OldSponsor', sibling 'OldSibling', 4 'NewSponsor', '?' 'NewSibling' --first result should be 3, second 4, etc based on max(sibling)+x for group sponsor=4from @activitywhere sponsor <> 4group by activity, sponsor, sibling--how do I increment NewSibling based at Max(Sibling)+1 for the group sponsor=4?Mon, 10 Sep 2012 23:27:27 GMTchrismathttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspxYou gave us no DDL, no specs and no desired results. You talk about a "current row", but there is no temporal or ordering in the data. Would you like to try again and follow the basic rules of netiquette?Mon, 10 Sep 2012 22:55:57 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspxI hope a better description is in my 3rd post below.Mon, 10 Sep 2012 22:14:00 GMTchrismathttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspx[quote][b]chrismat (9/10/2012)[/b][hr]I've always heard anything can be done in set-based operation without using a cursor, but I can't think of how for this scenario.A SELECT query returns four columns from tables, and the fifth column (NewSibling) needs calculated. Here is an example of the output desired:OldSponsor, OldSibling, NewSponsor, NewSibling177632, 0, 176896, 5177632, 1, 176896, 6177632, 2, 176896, 7171988, 0, 176896, 8171988, 1, 176896, 9179067, 0, 176897, 55179067, 1, 176897, 56179067, 2, 176897, 57179068, 0, 176897, 58179068, 1, 176897, 59The NewSponsor currently has some MAX(Sibling); 176896 is currently 4, 176897 is currently 54.Rows in same NewSponsor group must start current MAX(Sibling)+1 and increment to account for the others being selected.Thanks for any advice.[/quote]Based on the data above, what are the expected results.Mon, 10 Sep 2012 19:24:37 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357082-392-1.aspxI hope a better description is in my 3rd post below.Mon, 10 Sep 2012 19:10:57 GMTchrismat