asked on

Calculating differences

I have a table for which I need to calculate the total differences.

eg:
Table: Accounting

user status time date
bob start 01:56:03 12 Jul 2001
anne start 02:03:25 12 Jul 2001
bob stop 02:04:23 12 Jul 2001
bob start 02:05:06 12 Jul 2001
bob stop 02:20:11 12 Jul 2001

Basically, this is a RADIUS log, and between the START and STOP entries, the specified user is on line.

I need to calculate the total time spent on line for a given user using an SQL statement, but I'm way out of my depth! Any suggestions?

nigelrivett

I take it these are character entries.

select convert(datetime,date + ' ' + time) will give the datetime of the entry.

select date - (select min(date) from tbl t2 where t1.user = t2.user and t2.status = 'end' and t2.date > t1.date)
from tbl t1
where status = 'start'

will give the end to go with the start. So something like

select sum(convert(datetime,date + ' ' + time) - (select min(convert(datetime,date + ' ' + time)) from tbl t2 where t1.user = t2.user and t2.status = 'end' and t2.convert(datetime,date + ' ' + time) > t1.convert(datetime,date + ' ' + time))
from tbl t1
where status = 'start'
group by user

ASKER CERTIFIED SOLUTION

amp072397

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amp072397

jezzar:
I am sure you did not intend to award to me. Thanks for cleaning up your questions!!

nigelrivett:

Please see this new question for your points:
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=mssql&qid=20264726

thanks!
amp
community support moderator