Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


set row number to various actions


set row number to various actions

Author
Message
Ceevei
Ceevei
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16594 Visits: 17024
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)
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2272 Visits: 7827
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
  • 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

  • Ceevei
    Ceevei
    Forum Newbie
    Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

    Group: General Forum Members
    Points: 7 Visits: 53
    Sean Lange (1/14/2013)
    Excellent job posting ddl and sample data. Now what is the question?


    Smile Nice one.

    I believe I did mention before the second set of table/columns that this is the output I want.
    Ceevei
    Ceevei
    Forum Newbie
    Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

    Group: General Forum Members
    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.
    mister.magoo
    mister.magoo
    SSCrazy
    SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

    Group: General Forum Members
    Points: 2272 Visits: 7827
    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 :-D

    MM


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




  • 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

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search