InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 05:56:17 GMT20http://www.sqlservercentral.com/Forums/Topic1406571-338-1.aspx[quote][b]Ceevei (1/14/2013)[/b][hr][quote][b]mister.magoo (1/14/2013)[/b][hr]Is this what you mean?[/quote]Prceisely.Thanks a lot. that works fine as I want it to.Thanks again.[/quote]You are welcome. Thanks for the feedback :-DMon, 14 Jan 2013 17:46:46 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1406571-338-1.aspx[quote][b]mister.magoo (1/14/2013)[/b][hr]Is this what you mean?[/quote]Prceisely.Thanks a lot. that works fine as I want it to.Thanks again.Mon, 14 Jan 2013 14:54:57 GMTCeeveihttp://www.sqlservercentral.com/Forums/Topic1406571-338-1.aspx[quote][b]Sean Lange (1/14/2013)[/b][hr]Excellent job posting ddl and sample data. Now what is the question?[/quote]:) Nice one. I believe I did mention before the second set of table/columns that this is the output I want.Mon, 14 Jan 2013 14:53:12 GMTCeeveihttp://www.sqlservercentral.com/Forums/Topic1406571-338-1.aspxIs this what you mean?[code="sql"]WITH pass1AS ( -- First number the ActionTypes giving priority to HELLO lines SELECT * , ROW_NUMBER() OVER (PARTITION BY AccountNum, ActionType ORDER BY ISNULL(NULLIF(ActionType,'HELLO'),'') ,DataAction) AS n1 FROM ActionDetails ) , pass2AS ( -- Next number the intermediate actions using the newly numbered HELLO actions to partition the data SELECT * , ROW_NUMBER() OVER ( PARTITION BY AccountNum , n1 ORDER BY DataAction ) AS n2 FROM pass1 )SELECT AccountNum , DataAction , ActionType , CASE ActionType WHEN 'HELLO' THEN n1 ELSE NULL END AS ActionStart , convert(VARCHAR, n1) + CONVERT(VARCHAR, n2) AS ActionCounterFROM pass2ORDER BY AccountNum , DataAction[/code]Mon, 14 Jan 2013 09:14:57 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1406571-338-1.aspxExcellent job posting ddl and sample data. Now what is the question?Mon, 14 Jan 2013 08:28:23 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1406571-338-1.aspxI'm trying to set row numbers to a table's output.Table looks like[code="other"] AccountNum DataAction ActionType ActionStartCounter 123 11/01/2013 HELLO 1 123 12/01/2013 NONO NULL 123 16/01/2013 YESYES NULL 123 1/02/2013 HELLO 2 123 4/02/2013 YESYES NULL 456 10/01/2013 HELLO 1 456 13/01/2013 NONO NULL 456 14/01/2013 WHYWHY NULL 456 15/01/2013 YESYES NULL 456 20/03/2013 HELLO 2 456 31/03/2013 YESYES NULL[/code]What this is trying to do is 1) Every time there is Hello for an account it sequences the table based on DateAction under the column `ActionStartCounter`2) the `NULLs` signify these actions are not HELLO and are part of Previous Numbered row i.e. for AccountNum 123 NONO on 12/01/2013 is linked to HELLO on 11/01/2013.3) YESYES is the last action for any Account for any start by HELLO.I want the output to be[code="other"] AccountNum DataAction ActionType ActionStartCounter ActionCounter 123 11/01/2013 HELLO 1 11 123 12/01/2013 NONO NULL 12 123 16/01/2013 YESYES NULL 13 123 1/02/2013 HELLO 2 21 123 4/02/2013 YESYES NULL 22 456 10/01/2013 HELLO 1 11 456 13/01/2013 NONO NULL 12 456 14/01/2013 WHYWHY NULL 13 456 15/01/2013 YESYES NULL 14 456 20/03/2013 HELLO 2 21 456 31/03/2013 YESYES NULL 22[/code]Where new field ActionCounter will essentially be concatenation of`ActionStartCounter` and rownumber within the `accountNum` and `ActionStartCounter`meaningthe second piece in the concatenation is about the row_number within the start of the HELLO, as soon as new HELLO comes in the counter resets.ActionStartCounter also is based on case when `ActionType ='Hello' then row_number()` over(partition by AccountNum order by DateAction)If you guys reckon that should be changed we can get that done. If you think the AccountNum or Date can be part of the new column to make it unique we can do that. There is no limitation on number of fields we need to transform to get to last column.Thanks for your help.**PS:** Platform SQL Server 2005Here's DDL [code="other"] Create Table ActionDetails ( AccountNum Int, DataAction datetime, ActionType Varchar(25), ActionStart int ) Insert into ActionDetails Select 123,CONVERT(datetime,'20130111' ,112),'HELLO',1 UNION Select 123,CONVERT(datetime,'20130112' ,112),'NONO',NULL UNION Select 123,CONVERT(datetime,'20130116' ,112),'YESYES',NULL UNION Select 123,CONVERT(datetime,'20130201' ,112),'HELLO',2 UNION Select 123,CONVERT(datetime,'20130204' ,112),'YESYES',NULL UNION Select 456,CONVERT(datetime,'20130110' ,112),'HELLO',1 UNION Select 456,CONVERT(datetime,'20130113' ,112),'NONO',NULL UNION Select 456,CONVERT(datetime,'20130114' ,112),'WHYWHY',NULL UNION Select 456,CONVERT(datetime,'20130115' ,112),'YESYES',NULL UNION Select 456,CONVERT(datetime,'20130320' ,112),'HELLO',2 UNION Select 456,CONVERT(datetime,'20130331' ,112),'YESYES',NULL [/code]Sun, 13 Jan 2013 22:40:49 GMTCeevei