How to calculate duration in a query

[nma]

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

[Delerna]

[code=sql]
select Dte,NextDte,dat ediff(ss,Dte,Ne xtDte) as Seconds
from
( select DateTime as Dte,(select min(Datetime)
from YourTable b
where b.Datetime>c.Da tetime)as NextDte
from YourTable c
)a
[/code]

[nma]

[code=sql]
select Dte,NextDte,dat ediff(ss,Dte,Ne xtDte) as Seconds
from
( select DateTime as Dte,(select min(Datetime)
from YourTable b
where b.Datetime>c.Da tetime)as NextDte
from YourTable c
)a
[/code]

Hi

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

Thanks

[Delerna]

substitite with your field and table names
[code=sql]
select userID,Date,Nex tDate,datediff( ss,Date,NextDat e) as Seconds
from
( select UserID,Date,(se lect min(Datetime)
from LogCombined2008 b
where b.userID=c.user ID and b.date>c.date
) as NextDate
from LogCombined2008 c
)a
[/code]

[nma]

substitite with your field and table names
[code=sql]
select userID,Date,Nex tDate,datediff( ss,Date,NextDat e) as Seconds
from
( select UserID,Date,(se lect min(Datetime)
from LogCombined2008 b
where b.userID=c.user ID and b.date>c.date
) as NextDate
from LogCombined2008 c
)a
[/code]

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

[Delerna]

Do you mean something like this
[code=sql]
select userID,sum(date diff(ss,Date,Ne xtDate)) as TotalSeconds
from
( select UserID,Date,(se lect min(Datetime)
from LogCombined2008 b
where b.userID=c.user ID and b.date>c.date
) as NextDate
from LogCombined2008 c
)a
group by userID
[/code]

[nma]

Hi Delerna,

this is the query and the results generated....wh ich is works fine.

[HTML]select userID,date,Nex tDate,datediff( ss,date,NextDat e) as Seconds
from
( select userID,date,(se lect min(date)
from LogCombined2008 b
where b.userID=c.user ID 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."

[Delerna]

Basically, the error means that you are selecting the field [date] but you are not aggregating it.
For example

SELECT userID,date,sum(secs) as secs
FROM sourcetable
group by userid

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

SELECT userID,date,sum(secs) as secs
FROM sourcetable
group by userid,[Date]

Now date is being aggregated by group by. No error will occurr
alternatively.

SELECT userID,max(date) as Dte,sum(secs) as secs
FROM sourcetable
group by userid

now Date is being aggregated by max(). No error will occur.

Every field in your select must be aggregated in some way

[Delerna]

You seem to be wanting to sum all the logged in times for a particular user on a particular date, so try this

SELECT convert(datetime,left(LogCombined2008,11)) as Date

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

select userID,convert(datetime,left(LogCombined2008,11)) as Date,NextDate,sum(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
group by userid,convert(datetime,left(LogCombined2008,11))

[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

SELECT convert(datetime,left(LogCombined2008,11)) as Date

Hi Delerna,

The code above produce error message like below:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LogCombined200 8'.

[Delerna]

oops my bad I used the table name instead of the field
try this
[code=sql]
SELECT convert(datetim e,left(Date,11) ) as Date
[/code]

which means the rest of the code should be
[code=sql]
select userID,convert( datetime,left(D ate,11)) as Date,NextDate,s um(datediff(ss, date,NextDate)) as Seconds
from
( select userID,date,(se lect min(date)
from LogCombined2008 b
where b.userID=c.user ID and b.date>c.date
) as NextDate
from LogCombined2008 c
)a
group by userid,convert( datetime,left(D ate,11))
[/code]

[nma]

oops my bad I used the table name instead of the field
try this
[code=sql]
SELECT convert(datetim e,left(Date,11) ) as Date
[/code]

which means the rest of the code should be
[code=sql]
select userID,convert( datetime,left(D ate,11)) as Date,NextDate,s um(datediff(ss, date,NextDate)) as Seconds
from
( select userID,date,(se lect min(date)
from LogCombined2008 b
where b.userID=c.user ID and b.date>c.date
) as NextDate
from LogCombined2008 c
)a
group by userid,convert( datetime,left(D ate,11))
[/code]

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 :)

[nma]

Hi Delerna,

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

nma

[Delerna]

hours=convert(i nt,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(i nt,seconds/60)
therefore

Sec=seconds-(convert(int,se conds/60)*60)