Cross over records - 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 2000 Forums

Transact-SQL (2000)

Cross over records

Author

Topic

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-09-02 : 19:00:44

Ok ... I have been at work for 20 hours and now my mind has almost stopped working ...but before I go .. i need to complete one small problem ...

I have a Table in which i have amount , date and a integer identity column and it is sorted by date. what i need to do is find those consecutive records where the amount goes from positive to negative or from negative to positive.

Hope i have put this good enough for you all to understand

Enigma

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-09-02 : 19:33:16

Create Table MyTable99
(
MTId int identity(1,1),
TimeStp datetime,
Amount int
)

declare @var int
select @var = 100
while @Var > -100
begin
insert into Mytable99 (Amount, TimeStp)
select @var, getdate()

waitfor delay '00:00:01.000'
set @var = @var - 23
end

select * from Mytable99

-- Insert your code here , I need MTId 5,6 to be returned

drop table Mytable99

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-02 : 19:44:39

1. Create a view like this:

select t.intcol, t.amountCol, t.dateCol, (select top 1 amountCol from yourTable where intCol > t.intCol order by intCol asc) as nextAmountCol
from yourTable t

2. Do a query like:

SELECT * From yourView WHERE (AmountCol / Abs(AmountCol)) <> (nextAmountCol / Abs(nextAmountCol))

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-09-02 : 20:17:48

select a.MTId,b.MTId from Mytable99 a, MyTable99 b where a.Amount >0 and B.Amount<0 and a.MTId = B.MTId-1

I worked out this ...

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-02 : 20:43:37

Different ways to skin every cat....

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-09-02 : 22:19:01

anyways .. thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources