January 13, 2013 at 10:40 pm
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
January 14, 2013 at 8:28 am
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/
January 14, 2013 at 9:14 am
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);
January 14, 2013 at 2:53 pm
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.
January 14, 2013 at 2:54 pm
mister.magoo (1/14/2013)
Is this what you mean?
Prceisely.
Thanks a lot. that works fine as I want it to.
Thanks again.
January 14, 2013 at 5:46 pm
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
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply