Calculating time and group by

[jonmccon]

I am trying to calculate the total time a user has been logged on to an appilcation for the day. The beginnings of my query looks like this:

SELECT T.UserTimeLogIn,T.UserTimeLogOut, C.ContactFirstName, C.ContactLastName, T.ContactID
FROM T INNER JOIN C ON T.ContactID = C.ContactID
WHERE T.UserTimeLogIn >= @dtmStart AND T.UserTimeLogIn <= @dtmEnd

I want to display the user, date, and the total time the user was logged in that day. Everything I have tried falls short. Please help.

 

[foxdev]

Have you tried the DATEDIFF() function? -----------------
Robert Bradley
use coupon code &quot;TEKTIPS&quot; for 15% off at:

http://www.lowcostprints.com

http://www.foxdev.com

 

[tlbroadbent]

If the columns are datetime data type and the elapsed time is less 24 hours you can do the following.

SELECT
T.UserTimeLogIn,
T.UserTimeLogOut,
convert(char(8), T.UserTimeLogOut-T.UserTimeLogIn, 8) As ElapsedTime
C.ContactFirstName,
C.ContactLastName,
T.ContactID

FROM T INNER JOIN C ON T.ContactID = C.ContactID
WHERE T.UserTimeLogIn >= @dtmStart
AND T.UserTimeLogIn <= @dtmEnd

If the elapsed time can exceed 24 hours you'll need a more complex query. You can use the DATEDIFF function to calculate the difference in minutes or seconds and then derive the elapsed time from that the value returned by DATEDIFF.

Select
DATEDIFF(s,T.UserTimeLogIn,T.UserTimeLogOut) As ElapseSecs
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jonmccon]

Thanks for the help. I have tried the DATEDIFF function as shown below. It doesn't roll up different sessions. If I logged on 5 times in a day, it shows me the total time of each session. I want to see the total time for all sessions for a user/day.

SELECT T.UserTimeLogIn, DATEDIFF(mi,T.UserTimeLogIn, T.UserTimeLogOut) AS ElpasedMins, T.UserTimeBrowserInfo,
C.ContactFirstName, C.ContactLastName, T.ContactID
FROM T INNER JOIN C ON T.ContactID = C.ContactID

 

[tlbroadbent]

You need to summarize using a GROUP BY query.

SELECT
Min(T.UserTimeLogIn) As UserTimeLogIn,
Sum(DATEDIFF(mi,T.UserTimeLogIn, T.UserTimeLogOut)) AS ElpasedMins,
T.UserTimeBrowserInfo,
C.ContactFirstName,
C.ContactLastName,
T.ContactID

FROM T INNER JOIN C
ON T.ContactID = C.ContactID

GROUP BY
T.UserTimeBrowserInfo,
C.ContactFirstName,
C.ContactLastName,
T.ContactID
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jonmccon]

Can't thank you enough for the help Terry. The following query misses many of the sessions. Some dates are missed altogether. What I really need is a DISTINCT function on
T.UserTimeLogIn, but then I have to include it in the GROUP BY statement which screws the results up. Any ideas? Going from Oracle to SQL is no fun for me. Thanks for your patience.

SELECT MIN(convert (varchar,T.UserTimeLogIn,1))AS LoginDate,
SUM(DATEDIFF(mi,T.UserTimeLogIn, T.UserTimeLogOut)) AS ElpasedMins,
T.UserTimeBrowserInfo,
C.ContactFirstName,
C.ContactLastName,
T.ContactID

FROM T
INNER JOIN C
ON T.ContactID = C.ContactID

GROUP BY
T.ContactID,
C.ContactFirstName,
C.ContactLastName,
T.UserTimeBrowserInfo

 

[tlbroadbent]

You reallym want a summary by day - correct? Try the following.

SELECT
Convert(varchar(8),T.UserTimeLogIn,1) AS LoginDate,
SUM(DATEDIFF(mi,T.UserTimeLogIn, T.UserTimeLogOut)) AS ElpasedMins,
T.UserTimeBrowserInfo,
C.ContactFirstName,
C.ContactLastName,
T.ContactID

FROM T
INNER JOIN C
ON T.ContactID = C.ContactID

GROUP BY
Convert(varchar(8),T.UserTimeLogIn,1),
T.ContactID,
C.ContactFirstName,
C.ContactLastName,
T.UserTimeBrowserInfo


Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jonmccon]

Terry,
You my dear are a godsend. THANK YOU, THANK YOU, THANK YOU!!!
Jonna