Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

set row number to various actions Expand / Collapse
Author
Message
Posted Sunday, January 13, 2013 10:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 29, 2013 4:11 AM
Points: 7, Visits: 53
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
Posted Monday, January 14, 2013 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1406760
Posted Monday, January 14, 2013 9:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:17 AM
Points: 1,816, Visits: 5,913
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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1406797
    Posted Monday, January 14, 2013 2:53 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Saturday, June 29, 2013 4:11 AM
    Points: 7, Visits: 53
    Sean Lange (1/14/2013)
    Excellent job posting ddl and sample data. Now what is the question?


    :) Nice one.

    I believe I did mention before the second set of table/columns that this is the output I want.
    Post #1406952
    Posted Monday, January 14, 2013 2:54 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Saturday, June 29, 2013 4:11 AM
    Points: 7, Visits: 53
    mister.magoo (1/14/2013)
    Is this what you mean?



    Prceisely.

    Thanks a lot. that works fine as I want it to.

    Thanks again.
    Post #1406953
    Posted Monday, January 14, 2013 5:46 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Yesterday @ 6:17 AM
    Points: 1,816, Visits: 5,913
    Ceevei (1/14/2013)
    mister.magoo (1/14/2013)
    Is this what you mean?



    Prceisely.

    Thanks a lot. that works fine as I want it to.

    Thanks again.


    You are welcome. Thanks for the feedback


    MM


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1406997
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse