Creating Number column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating Number column

Author

Topic

s_k_sameer
Starting Member

9 Posts

Posted - 2008-05-14 : 04:39:10

I have a ACTION column. i.e Its only disply SELL/BUY.

ACTION

BUY
BUY
BUY
SELL
SELL
SELL
SELL
BUY
BUY
SELL
SELL
SELL
BUY

How to create another column display like this

ACTION

BUY 1
BUY 2
BUY 3
SELL 1
SELL 2
SELL 3
SELL 4
BUY 1
BUY 2
SELL 1
SELL 2
SELL 3
BUY 1

Help me anyone...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 04:46:49

DO you have any other columns in your table? Its probably better if you can give some data from the other columns too.

s_k_sameer
Starting Member

9 Posts

Posted - 2008-05-14 : 04:55:48

I have TIME Column also..

Assume my table like this..

TIME ACTION
12:40:01 BUY
12:40:31 BUY
12:40:51 BUY
12:41:01 SELL
12:41:21 SELL
12:41:31 SELL
12:41:41 SELL
12:41:51 BUY
12:42:01 BUY
12:42:29 SELL
12:42:31 SELL
12:42:41 SELL
12:42:51 BUY

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 05:03:38

Anything else?
How come the three last Actions are earlier than the previous action?

E 12°55'05.25"
N 56°04'39.16"

s_k_sameer
Starting Member

9 Posts

Posted - 2008-05-14 : 05:09:33

quote:
Originally posted by Peso

Anything else?
How come the three last Actions are earlier than the previous action?

E 12°55'05.25"
N 56°04'39.16"

SOrry...I changed now..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 05:15:56

quote:
Originally posted by s_k_sameer

I have TIME Column also..

Assume my table like this..

TIME ACTION
12:40:01 BUY
12:40:31 BUY
12:40:51 BUY
12:41:01 SELL
12:41:21 SELL
12:41:31 SELL
12:41:41 SELL
12:41:51 BUY
12:42:01 BUY
12:42:29 SELL
12:42:31 SELL
12:42:41 SELL
12:42:51 BUY

SELECT ROW_NUMBER() OVER (PARTITION BY LEFT(TIME,5),ACTION ORDER BY TIME) AS Seg,
TIME,
ACTION
FROM YOURTABLE

s_k_sameer
Starting Member

9 Posts

Posted - 2008-05-14 : 06:39:42

Hi visakh,

Thnks for ur response. Its not giving proper output.It wrong in my requirement.

eralper
Yak Posting Veteran

66 Posts

Posted - 2008-05-14 : 06:43:27

Hey,
That is same topic with the one at MSDN forums :) [url]http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3339807&SiteID=1[/url]

I had also posted the following solution to that post :

Add an Id identity column to the Actions table then run the following code black

with cte (id, current_code, prev_code, rn) as

(

select id, current_code, prev_code, rn--, 0

from (

select

a.id,

a.code current_code,

b.code prev_code,

case

when a.code <> isnull(b.code,'') then 1

else null

end as rn

from EY_Action a

left join EY_Action b on (a.id ) = (b.id + 1)

) a

where id = 1

union all

select a.id, a.current_code, a.prev_code, isnull(a.rn, cte.rn + 1)--, cte.rn

from (

select

a.id,

a.code current_code,

b.code prev_code,

case

when a.code <> isnull(b.code,'') then 1

else null

end as rn

from EY_Action a

inner join EY_Action b on (a.id ) = (b.id + 1)

) a

inner join cte on (a.id) = (cte.id + 1)

)

select c.id, c.current_code, c.rn from cte c

Eralper
http://www.kodyaz.com/content/SQLServerArticles.aspx

-------------
Eralper
http://www.kodyaz.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 06:50:14

Which certainly is so much more efficient than Visakh16's suggestion?

E 12°55'05.25"
N 56°04'39.16"

s_k_sameer
Starting Member

9 Posts

Posted - 2008-05-14 : 07:34:23

hi eralper,

what is current_code, prev_code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources