set row number to various actions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » set row number to various actions

set row number to various actions

Rate Topic

Display Mode

Topic Options

Author

Message

Ceevei

Posted Sunday, January 13, 2013 10:40 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, January 21, 2013 9:40 PM
Points: 7, Visits: 50

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

Table looks like

    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

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

    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

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

Post #1406571

Sean Lange

Posted Monday, January 14, 2013 8:28 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 8,620, Visits: 8,261

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1406760

mister.magoo

Posted Monday, January 14, 2013 9:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 1,298, Visits: 3,884

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