split batch to get time

Question

split batch to get time

karthik M

One Orange Chip

Points: 29139
More actions
July 15, 2009 at 3:24 am

#139821

Hi All,
I have one scenario.
create table #temp
(
ref_id int,
ref_text varchar(255),
tr_id int
)
go
insert into #temp
select 0,'DealView',4003
union
select 6732,'RelTransactionCompany',4003
union all
select 732,'Timing',4003
union all
select 4003,'TransactionMaster',4003
union all
select 0,'DealView',4003
union all
select 6732,'RelTransactionCompany',4003
union all
select 6731,'RelTransactionCompany',4003
union all
select 713,'Timing',4003
union all
select 4003,'TransactionMaster',4003
union all
select 3,'Timing',4003
union all
select 8,'Timing',4003
select * from #temp
I want to retrieve 'Timing' where it falls between 'TransactionMaster' and 'DealView'.
Here we have two batches which falls between 'TransactionMaster' and 'DealView'.
I don't want to fetch 'Timing' if it doesn't comes between 'TransactionMaster' and 'DealView'.
i.e last two rows.
karthik

Viewing 4 posts - 1 through 3 (of 3 total)

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

VaibhavChoubey@SQL Ten Centuries Points: 1033 More actions · Answer 1

Firstly Use some mechanism to traverse through each row one by one you may need to order your records while inserting (add a identity column) or use a cursor, now take a variable @x and set it to 0 while traversing if u get Dealview set @x=1 , also make a temp table where u will insert your record if @x=1 and text= Timing when u get Transaction master set @x=0 this should solve your problem.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Karthik,

There is nothing in that data nor in the table to establish the order of the rows. You know you cannot rely on such a thing as the "natural order" because it can change in a New York minute.

--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)

GSquared SSC Guru Points: 260824 More actions · Answer 3

Define what you mean by "between". I don't see anything in the table that indicates the row sequence. Is there supposed to be an identity value or a time stamp (datetime), to indicate sequence?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon