By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,341 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

How to calculate duration in a query

P: 93
Hi

How do I clculate time duration if I have a column like this

2008-04-19 15:00:55.000
2008-04-19 15:01:07.000
2008-04-19 16:33:55.000
2008-04-19 16:35:07.000
2008-04-19 16:37:00.000
2008-04-19 16:37:01.000
2008-04-19 16:37:13.000
2008-04-19 16:37:14.000
2008-04-19 16:39:35.000
2008-04-19 16:49:05.000

to know the session user login/logout?
How is the query in SQL?

tq
nma
Jun 11 '08 #1
Share this Question
Share on Google+
13 Replies


Delerna
Expert 100+
P: 1,134
Expand|Select|Wrap|Line Numbers
  1. select Dte,NextDte,datediff(ss,Dte,NextDte) as Seconds
  2. from
  3. (    select DateTime as Dte,(select min(Datetime) 
  4.                             from YourTable b 
  5.                             where b.Datetime>c.Datetime)as NextDte 
  6.     from YourTable c
  7. )a
  8.  
Jun 12 '08 #2

P: 93
nma
Expand|Select|Wrap|Line Numbers
  1. select Dte,NextDte,datediff(ss,Dte,NextDte) as Seconds
  2. from
  3. (    select DateTime as Dte,(select min(Datetime) 
  4.                             from YourTable b 
  5.                             where b.Datetime>c.Datetime)as NextDte 
  6.     from YourTable c
  7. )a
  8.  
Hi

I'm using data from one table name LogCombined2008 only
and the colum name is date
I also have colum name userID

Thanks
Jun 13 '08 #3

Delerna
Expert 100+
P: 1,134
substitite with your field and table names
Expand|Select|Wrap|Line Numbers
  1. select userID,Date,NextDate,datediff(ss,Date,NextDate) as Seconds
  2. from
  3. (   select UserID,Date,(select min(Datetime) 
  4.                             from LogCombined2008 b 
  5.                             where b.userID=c.userID and b.date>c.date
  6.                         ) as NextDate 
  7.     from LogCombined2008 c
  8. )a
  9.  
Jun 13 '08 #4

P: 93
nma
substitite with your field and table names
Expand|Select|Wrap|Line Numbers
  1. select userID,Date,NextDate,datediff(ss,Date,NextDate) as Seconds
  2. from
  3. (   select UserID,Date,(select min(Datetime) 
  4.                             from LogCombined2008 b 
  5.                             where b.userID=c.userID and b.date>c.date
  6.                         ) as NextDate 
  7.     from LogCombined2008 c
  8. )a
  9.  

thanks Delerna

it works, I can get duration is seconds, then I will calculate the total seconds for specific user login (say userID= 12, total seconds is (sum all the row). maybe I can do manually, export the table in excel and then filter it to get the sum of the second

or is there is a faster way to calculate total second in that row?
nma
Jun 19 '08 #5

Delerna
Expert 100+
P: 1,134
Do you mean something like this
Expand|Select|Wrap|Line Numbers
  1. select userID,sum(datediff(ss,Date,NextDate)) as TotalSeconds
  2. from
  3. (   select UserID,Date,(select min(Datetime) 
  4.                             from LogCombined2008 b 
  5.                             where b.userID=c.userID and b.date>c.date
  6.                         ) as NextDate 
  7.     from LogCombined2008 c
  8. )a
  9. group by userID
  10.  
Jun 20 '08 #6

P: 93
nma
Hi Delerna,

this is the query and the results generated....which is works fine.

[HTML]select userID,date,NextDate,datediff(ss,date,NextDate) as Seconds
from
( select userID,date,(select min(date)
from LogCombined2008 b
where b.userID=c.userID and b.date>c.date
) as NextDate
from LogCombined2008 c
)a[/HTML]



[HTML][userID] [date] [NextDate] [Seconds]
1 2007-10-31 13:18:49.897 2007-10-31 13:19:32.117 43
1 2007-10-31 13:19:32.117 2007-10-31 13:58:14.947 2322
12 2007-10-31 13:20:10.100 2007-10-31 13:24:46.650 276
12 2007-10-31 13:24:46.650 2007-10-31 13:25:03.210 17
12 2007-10-31 13:43:29.513 2007-10-31 13:43:40.047 11[/HTML]

but can I get total in seconds automatically calculated say userID=1 is equal 43+2322 and display the outputs in seconds and hour? Because after that I need to plot a graph for each user/total user in seconds/hour

But when I add the "group by userID" at the last line it shows error
"Msg 8120, Level 16, State 1, Line 1
Column 'a.date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Jun 20 '08 #7

Delerna
Expert 100+
P: 1,134
Basically, the error means that you are selecting the field [date] but you are not aggregating it.
For example
Expand|Select|Wrap|Line Numbers
  1. SELECT userID,date,sum(secs) as secs
  2. FROM sourcetable
  3. group by userid
  4.  
would generate the same error because there are 3 fields being selected but only 2 of them are being aggregated.
secs is aggregated by the sum()
user id is being aggregated by the group by
date is not being aggregated by anything
Expand|Select|Wrap|Line Numbers
  1. SELECT userID,date,sum(secs) as secs
  2. FROM sourcetable
  3. group by userid,[Date]
  4.  
Now date is being aggregated by group by. No error will occurr
alternatively.
Expand|Select|Wrap|Line Numbers
  1. SELECT userID,max(date) as Dte,sum(secs) as secs
  2. FROM sourcetable
  3. group by userid
  4.  
now Date is being aggregated by max(). No error will occur.

Every field in your select must be aggregated in some way
Jun 22 '08 #8

Delerna
Expert 100+
P: 1,134
You seem to be wanting to sum all the logged in times for a particular user on a particular date, so try this
Expand|Select|Wrap|Line Numbers
  1. SELECT convert(datetime,left(LogCombined2008,11)) as Date
  2.  
Try that as a test because i am not sure whether it is 10 or 11 characters for the left function to get just the date part of the datetime.

That should return the date only (time will always be midnight) for all dates in the table.
With the above info you can now group by the date like this

Expand|Select|Wrap|Line Numbers
  1. select userID,convert(datetime,left(LogCombined2008,11)) as Date,NextDate,sum(datediff(ss,date,NextDate)) as Seconds
  2.  from
  3.     (   select userID,date,(select min(date)
  4.     from LogCombined2008 b
  5.    where b.userID=c.userID and b.date>c.date
  6.                   ) as NextDate
  7.           from LogCombined2008 c
  8.       )a
  9. group by userid,convert(datetime,left(LogCombined2008,11)) 
  10.  
Jun 22 '08 #9

P: 93
nma
[quote=Delerna]You seem to be wanting to sum all the logged in times for a particular user on a particular date, so try this
Expand|Select|Wrap|Line Numbers
  1. SELECT convert(datetime,left(LogCombined2008,11)) as Date
  2.  
Hi Delerna,

The code above produce error message like below:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LogCombined2008'.
Jun 23 '08 #10

Delerna
Expert 100+
P: 1,134
oops my bad I used the table name instead of the field
try this
Expand|Select|Wrap|Line Numbers
  1. SELECT convert(datetime,left(Date,11)) as Date
  2.  
which means the rest of the code should be
Expand|Select|Wrap|Line Numbers
  1. select userID,convert(datetime,left(Date,11)) as Date,NextDate,sum(datediff(ss,date,NextDate)) as Seconds
  2.  from
  3.     (   select userID,date,(select min(date)
  4.     from LogCombined2008 b
  5.    where b.userID=c.userID and b.date>c.date
  6.                   ) as NextDate
  7.           from LogCombined2008 c
  8.       )a
  9. group by userid,convert(datetime,left(Date,11))
  10.  
Jun 24 '08 #11

P: 93
nma
oops my bad I used the table name instead of the field
try this
Expand|Select|Wrap|Line Numbers
  1. SELECT convert(datetime,left(Date,11)) as Date
  2.  
which means the rest of the code should be
Expand|Select|Wrap|Line Numbers
  1. select userID,convert(datetime,left(Date,11)) as Date,NextDate,sum(datediff(ss,date,NextDate)) as Seconds
  2.  from
  3.     (   select userID,date,(select min(date)
  4.     from LogCombined2008 b
  5.    where b.userID=c.userID and b.date>c.date
  6.                   ) as NextDate
  7.           from LogCombined2008 c
  8.       )a
  9. group by userid,convert(datetime,left(Date,11))
  10.  
Hi Delerna,

It works...I just change the column name typing error only...it give total seconds the particular user has accesing/logging the system

Thanks a mills :)

I will post another problem solution as now almost drowning with my data analysis and will do part by part :)
Jun 24 '08 #12

P: 93
nma
Hi Delerna,

it possible right to convert the seconds into another column for minutes/hour from these query?

nma
Jun 24 '08 #13

Delerna
Expert 100+
P: 1,134
hours=convert(int,seconds/60)

this will get the whole hours

you may not need to convert to int as both numbers are integers and the answer may come out as an integer?

Now to find the left over seconds

Sec=seconds-(hours*60)

since hours=convert(int,seconds/60)
therefore

Sec=seconds-(convert(int,seconds/60)*60)
Jun 24 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.