Need conditional sequence number

Question

Post reply

Need conditional sequence number

Bhuvnesh

SSC Guru

Points: 59351
More actions
June 29, 2013 at 3:10 am

#274620

declare @t table
( id int, PayCode char(2) null, Amount decimal(15,2) null, CDate date, TranSeq int null ) declare @C int,@max int set @C = 1 insert into @t select 1, 'IR' , 1000.00 ,null, null union select 2, 'IP' , 300.00 ,null, null union select 3, 'IP' , 400.00 ,null,null union select 4, 'IR' , -1000.00 ,'2013-02-02', null union select 5, 'IR' , 200.00 ,null, null union select 6, 'IP' , 100.00 ,null, null union select 7, 'IR' , 200.00 ,null, null union select 8, 'IP' , 400.00 ,null, null select * from @t

Required Output is below

id PayCode Amount CDate TranSeq

1 IR 1000.00 NULL 1

2 IP 300.00 NULL 2

3 IP 400.00 NULL 3

4 IR -100.00 20130202 1

5 IR 200.00 NULL 1

6 IP 100.00 NULL 2

7 IR 200.00 NULL 1

8 IP 400.00 NULL 2

For every IR sequence will start and get increment with coming IP but when next IR comes in , new sequence will start.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Bhuvnesh

SSC Guru

Points: 59351

June 30, 2013 at 10:36 am

#1628550

Need help ..please .......

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 2

First, we have to somehow group together rows that belong to the same group. So we will add a new column, GroupId, that has ID of the first preceding IR row:

select *,



	GroupId = (SELECT TOP 1 tt.id FROM @t tt WHERE tt.id<=t.id and tt.PayCode='IR' ORDER BY tt.id desc)

from @t t

Result:

id PayCode Amount CDate TranSeq GroupId



1	IR	1000.00	NULL	NULL	1

2	IP	300.00	NULL	NULL	1

3	IP	400.00	NULL	NULL	1

4	IR	-1000.00	2013-02-02	NULL	4

5	IR	200.00	NULL	NULL	5

6	IP	100.00	NULL	NULL	5

7	IR	200.00	NULL	NULL	7

8	IP	400.00	NULL	NULL	7

Next step is trivial, assign a sequence number using ROW_NUMBER function, partitioned by that GroupId.

I used outer apply instead of inline view to make it a bit shorter:

select t.*,



	Rnr = ROW_NUMBER() OVER(PARTITION BY GroupId ORDER BY id)

from @t t

	outer apply (SELECT TOP 1 GroupId = tt.id FROM @t tt WHERE tt.id<=t.id and tt.PayCode='IR' ORDER BY tt.id desc) g

Result:

id PayCode Amount CDate TranSeq Rnr



1	IR	1000.00	NULL	NULL	1

2	IP	300.00	NULL	NULL	2

3	IP	400.00	NULL	NULL	3

4	IR	-1000.00	2013-02-02	NULL	1

5	IR	200.00	NULL	NULL	1

6	IP	100.00	NULL	NULL	2

7	IR	200.00	NULL	NULL	1

8	IP	400.00	NULL	NULL	2

HTH

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Dwain Camps SSC Guru Points: 86913 More actions · Answer 3

Perhaps you'd rather use a Quirky Update (QU)?



declare @t table

(

	id int PRIMARY KEY,

	PayCode char(2) null,

	Amount decimal(15,2) null,

	CDate date,

	TranSeq int  null

)

 insert into @t

 select 1, 'IR' , 1000.00 ,null, null union

 select 2, 'IP' , 300.00 ,null, null union

 select 3, 'IP' , 400.00 ,null,null union

 select 4, 'IR' , -1000.00 ,'2013-02-02', null union

 select 5, 'IR' , 200.00 ,null, null union

 select 6, 'IP' , 100.00 ,null, null union

 select 7, 'IR' , 200.00 ,null, null union

 select 8, 'IP' , 400.00 ,null, null 

DECLARE @TranSeq INT = 1

UPDATE @t

SET @TranSeq = CASE WHEN PayCode = 'IR' THEN 1 ELSE @TranSeq + 1 END

    ,TranSeq = @TranSeq 

select * from @t

Note that I added a clustered index (PRIMARY KEY) to your table.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Bala' Ten Centuries Points: 1156 More actions · Answer 4

here was one of the way

CREATE table #temptable



(

	id int,

	PayCode char(2) null,

	Amount decimal(15,2) null,

	CDate date,

	TranSeq int  null

)

declare @C int,@max int

set @C = 1 

 insert into #temptable

 select 1, 'IR' , 1000.00 ,null, null union

 select 2, 'IP' , 300.00 ,null, null union

 select 3, 'IP' , 400.00 ,null,null union

 select 4, 'IR' , -1000.00 ,'2013-02-02', null union

 select 5, 'IR' , 200.00 ,null, null union

 select 6, 'IP' , 100.00 ,null, null union

 select 7, 'IR' , 200.00 ,null, null union

 select 8, 'IP' , 400.00 ,null, null

 WITH CTE

 AS

 (

 SELECT id,PayCode,Amount,CDate,TranSeq,

 GRP = CASE PayCode WHEN  'IR'

 THEN  ROW_NUMBER () OVER(PARTITION BY PayCode ORDER BY ID)

   ELSE    ID - ROW_NUMBER () OVER(PARTITION BY PayCode ORDER BY ID )

  END

  from #temptable 

 )

 SELECT  id,PayCode,Amount,CDate,TranSeq,

 ROW_NUMBER () OVER(PARTITION BY GRP ORDER BY ID)

 FROM CTE

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Bhuvnesh

SSC Guru

Points: 59351

July 1, 2013 at 3:02 am

#1628640

Thanks a ton for help ..to all guys here 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

dwain.c (6/30/2013)

Perhaps you'd rather use a Quirky Update (QU)?

declare @t table ( id int PRIMARY KEY, PayCode char(2) null, Amount decimal(15,2) null, CDate date, TranSeq int null ) insert into @t select 1, 'IR' , 1000.00 ,null, null union select 2, 'IP' , 300.00 ,null, null union select 3, 'IP' , 400.00 ,null,null union select 4, 'IR' , -1000.00 ,'2013-02-02', null union select 5, 'IR' , 200.00 ,null, null union select 6, 'IP' , 100.00 ,null, null union select 7, 'IR' , 200.00 ,null, null union select 8, 'IP' , 400.00 ,null, null DECLARE @TranSeq INT = 1 UPDATE @t SET @TranSeq = CASE WHEN PayCode = 'IR' THEN 1 ELSE @TranSeq + 1 END ,TranSeq = @TranSeq select * from @t

Note that I added a clustered index (PRIMARY KEY) to your table.

You're missing several of the rules to make it safe Dwain.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Dwain Camps SSC Guru Points: 86913 More actions · Answer 7

Jeff Moden (7/1/2013)

dwain.c (6/30/2013)

Perhaps you'd rather use a Quirky Update (QU)?

declare @t table ( id int PRIMARY KEY, PayCode char(2) null, Amount decimal(15,2) null, CDate date, TranSeq int null ) insert into @t select 1, 'IR' , 1000.00 ,null, null union select 2, 'IP' , 300.00 ,null, null union select 3, 'IP' , 400.00 ,null,null union select 4, 'IR' , -1000.00 ,'2013-02-02', null union select 5, 'IR' , 200.00 ,null, null union select 6, 'IP' , 100.00 ,null, null union select 7, 'IR' , 200.00 ,null, null union select 8, 'IP' , 400.00 ,null, null DECLARE @TranSeq INT = 1 UPDATE @t SET @TranSeq = CASE WHEN PayCode = 'IR' THEN 1 ELSE @TranSeq + 1 END ,TranSeq = @TranSeq select * from @t

Note that I added a clustered index (PRIMARY KEY) to your table.

You're missing several of the rules to make it safe Dwain.

Indeed I omitted them but not because I'm out of practice or nothing. I was hoping the OP would ask!

But since you mentioned it, here's a link to your definitive treatise on the subject: http://qa.sqlservercentral.com/articles/T-SQL/68467/

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St