Queries 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

Queries Help

Author

Topic

hek78
Starting Member

10 Posts

Posted - 2003-12-05 : 04:09:28

This is a sample Access 97 table and I would like to extract Data into a webpage using ODBC.

Table Name: IPSUB1

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

IPAddress is in Text format
Time is in Text 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 address 10.65.30.85 and there is no session number for each login.

Thats what I write but it just cannot work since it all group by IPAddress.. Do help.

Select
IPAddress,
min(format(right(Time, 8), 'hh:mm:ss')) AS StartTime,
max(format(right(Time, 8), 'hh:mm:ss')) As EndTime,
DateDiff('s', StartTime, EndTime) As TimeDiff

FROM
IPSUB1

GROUP BY IPADDRESS;

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-12-05 : 05:11:33

If you can take it out of Access, you can use this : http://www.sqlteam.com/item.asp?ItemID=15044

Damian

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-05 : 09:14:30

I think the problem is not parsing the IP address but distinguishing which rows represent the start from which represent the finish and then cross-tabbing the results, and based on the table structure given I have not yet thought of a solution for this.

hek78 is there any more to the data structure?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-12-05 : 09:29:43

Did this query not work for you?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31007

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-05 : 15:37:52

I totally agree with drymchaser - it looks anti-relational.
Though it works (field tim is a 'real' DateTime field):


ip	tim	        byt
-----------------------------
a	10:10:15	8
a	10:12:15	7
a	14:20:00	88
a	16:20:00	77
b	00:00:20	1
b	00:33:20	1
-----------------------------

select ip,

tim as TimeStart,

(select min(tim) from e as ee where ee.ip=e.ip and ee.tim>e.tim)
as TimeEnd,

cdate((select min(tim) from e as ee where ee.ip=e.ip and ee.tim>e.tim)-tim)
as TimeDifference,

byt+(select top 1 byt from e as ee where ee.ip=e.ip and ee.tim>e.tim order by ee.tim)
as SumOfBytes

from e where (select count(*) from e as ee where ee.ip=e.ip and ee.tim>e.tim) mod 2=1

Result:


ip    TimeStart   TimeEnd      TimeDifference	 SumOfBytes
-----------------------------------------------------------
a     10:10:15    10:12:15     00:02:00	         15
a     14:20:00    16:20:00     02:00:00	         165
b     00:00:20    00:33:20     00:33:00	         2
-----------------------------------------------------------

PS Jay, sure your solution is fine (as ever) but it's Access 97.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-12-05 : 16:33:05

However your query from the link produced this:


IP   TimeStart	  TimeEnd       TimeDifference	  SumOfByte
------------------------------------------------------------
b    00:00:20	  00:33:20	1980              2
a    10:10:15	  16:20:00	22185             180
------------------------------------------------------------

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-12-05 : 17:01:37

That's what I thought it would do. I am going to try your solution Stoad it looks pretty tight.

hek78
Starting Member

10 Posts

Posted - 2003-12-06 : 00:05:25

Quote Ehorn
"Did this query not work for you?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31007"

Yup, that message is posted my me as well.
This query works fine using Access 2002 query but seems to have some problems when I use this query in php to extract the data from Access 97.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources