set row number to various actions

• Ceevei

  Valued Member

  Points: 51

  More actions

  January 13, 2013 at 10:40 pm

  #272736

  I'm trying to set row numbers to a table's output.

  Table looks like

  AccountNumDataActionActionTypeActionStartCounter

  123 11/01/2013HELLO 1

  123 12/01/2013NONO NULL

  123 16/01/2013YESYES NULL

  123 1/02/2013HELLO 2

  123 4/02/2013YESYES NULL

  456 10/01/2013HELLO 1

  456 13/01/2013NONO NULL

  456 14/01/2013WHYWHY NULL

  456 15/01/2013YESYES NULL

  456 20/03/2013HELLO 2

  456 31/03/2013YESYES NULL

  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

  AccountNumDataActionActionTypeActionStartCounter ActionCounter

  123 11/01/2013HELLO 1 11

  123 12/01/2013NONO NULL 12

  123 16/01/2013YESYES NULL 13

  123 1/02/2013HELLO 2 21

  123 4/02/2013YESYES NULL 22

  456 10/01/2013HELLO 1 11

  456 13/01/2013NONO NULL 12

  456 14/01/2013WHYWHY NULL 13

  456 15/01/2013YESYES NULL 14

  456 20/03/2013HELLO 2 21

  456 31/03/2013YESYES NULL 22

  Where new field ActionCounter will essentially be concatenation of

  `ActionStartCounter` and rownumber within the `accountNum` and `ActionStartCounter`

  meaning

  the 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 2005

  Here's DDL

  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

• Sean Lange

  SSC Guru

  Points: 286573

  More actions

  January 14, 2013 at 8:28 am

  #1576924

  Excellent job posting ddl and sample data. Now what is the question?

  _______________________________________________________________

  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/

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 14, 2013 at 9:14 am

  #1576956

  Is this what you mean?

  WITH pass1

  AS (

  -- 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

  )

  , pass2

  AS (

  -- 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 ActionCounter

  FROM pass2

  ORDER BY AccountNum

  , DataAction

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]