Calculating with subquerys - 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)

Calculating with subquerys

Author

Topic

basti
Starting Member

4 Posts

Posted - 2004-04-16 : 04:18:36

Table:
CREATE TABLE Syslogd (ura DATETIME,status BIT,channel VARCHAR(15),user_ip VARCHAR(20))

select s.channel, s.ura,
(select min(ura)
from LogD s1
where s1.user_ip = s.user_ip and s1.channel = s.channel and s1.ura > s.ura) as ura2
from LogD s

logid ura status channel user_ip ura2
------------------------------------------------------------------------------------------------
478532 2004-04-13 16:00:01.000 1 239.255.0.50 10.31.15.43 2004-04-13 16:20:04.000
************************************************************************************************
Basically i got ura2 from the record below!
478533 2004-04-13 16:20:04.000 0 239.255.0.50 10.31.15.43

At this point i grab ura from other record where status is (0) but i would like to calculate the
difference between these two fields (time), something like (ura - ura2), into new column.
I dont know how to use the result from subquery and use it in calculation, should i put it into temp
table!? ...im lost and also new at this.

please help! :)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-16 : 05:26:59

Dunno what you're on about with the status as you haven't got it anywhere in the code you've posted, but this should give you a difference in minutes between the two dates...


CREATE TABLE Syslogd (ura DATETIME,status BIT,channel VARCHAR(15),user_ip VARCHAR(20))

select s.channel, DATEDIFF(mi, s.ura, (select min(ura) 
from LogD s1 
where s1.user_ip = s.user_ip and s1.channel = s.channel and s1.ura > s.ura)) as difference
from LogD s

basti
Starting Member

4 Posts

Posted - 2004-04-19 : 03:01:58

Thank you!

basti

basti
Starting Member

4 Posts

Posted - 2004-04-20 : 04:35:34

I modified the query but dont know how to use the "difference" in WHERE clause.

This is what i have:
select *, DATEDIFF(ss,(select max(ura)
from Log s1
where s1.user_ip = s.user_ip and s1.channel = s.channel and s1.status = '1' and s1.ura < s.ura), s.ura) as difference
from Log s where status = '0'

and would like to add at the end something like WHERE status = '0' and difference < 180. Basically i have a result from subquery as an alias, but dont know how to use this result in the main query. Should i declare difference or put it into temp table!?.
Thank you !

basti

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-04-20 : 04:44:11

[code]
select *, DATEDIFF(ss,(select max(ura) from Log s1
where s1.user_ip = s.user_ip
and s1.channel = s.channel
and s1.status = '1'
and s1.ura < s.ura), s.ura) as difference
from Log s
where status = '0'
and DATEDIFF(ss,(select max(ura)
from Log s1
where s1.user_ip = s.user_ip
and s1.channel = s.channel
and s1.status = '1'
and s1.ura < s.ura), s.ura) > 180
[/code]
Try that...

basti
Starting Member

4 Posts

Posted - 2004-04-20 : 06:20:32

works like a charm.

thank you Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources