SQL do help! - 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

Other Forums

MS Access

SQL do help!

Author

Topic

hek78
Starting Member

10 Posts

Posted - 2003-12-03 : 10:16:41

This is a sample Access table

IPAddress Time Byte
10.65.30.85 8:25:14 24
10.65.30.85 8:25:14 0
10.65.30.85 8:25:17 10
10.65.30.86 8:26:18 33
10.65.30.86 8:26:18 10
10.65.30.86 8:27:33 20
10.65.30.87 8:33:33 33
10.65.30.87 8:33:40 24
10.65.30.85 8:36:33 0
10.65.30.85 8:36:55 28

IPAddress is in Text format
Time is in Date/Time Format
Bytes is in Number Format

Imagine the senario this way. User of one IP address log in at a particular time and download files. Another user of another IP log in. This particular user may download files again later so IP address may repeat.

I would like to write a Query with a final table shown

IPAddress TimeStart TimeEnd TimeDifference SumofByte
10.65.30.85 8:25:14 8:25:17 0:00:03 34
10.65.30.86 8:26:18 8:27:33 0:01:15 63
10.65.30.87 8:33:33 8:33:40 0:00:07 57
10.65.30.85 8:36:33 8:36:55 0:00:22 28

Note the repeat of IP adress 10.65.30.85 and there is no session number for each login.

How can I do it? Thanks! Need any Visual Basic Programming? Real headache

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-03 : 10:44:54

[code]
select IPAddress,min(time) as TimeStart, max(time) as TimeEnd, datediff("s",min(time),max(time)) as TimeDifference, sum(byte) as SumOfByte
from
(
select *, (select count(*) from ip b where b.IPAddress<>a.IPAddress and b.time >= a.time) as id
from ip a
) a
group by id,IPAddress
order by min(time)

[/code]

hek78
Starting Member

10 Posts

Posted - 2003-12-03 : 11:39:48

Problem Solve
Works perfectly on my sample! Gonna try it on my system after a good night sleep.
Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources