MAX & AVG using 2 columns in 1 record and not whole table
-
Hi,
I am trying to do some calculations on records in a table that records the time users have been logged on to a website. The table has columns like LogonDate (e.g. 20040802091555 that stands 9:15:55 on Aug 2nd 2004, so it doens't store it in a Date format, but as a varchar instead) and LogOffDate in a similar way.
What I would like to get from it is, within a given timespan (start date and end date), the average time users were logged on and the maximum time a user was logged on.
Trying to use the following statements:
Select Avg(CAST(
LogOffDate as Numeric)-CAST(LogonDate as Numeric)) as AvgLoggedOn FROM geb_LoggedOn WHERE LogonDate >= '" & ayear & amonth & aday & "' AND LogonDate <= '" & pjaar & pmaand & pdag & "'
Select Max(CAST(LogOffDate as Numeric)-CAST(LogonDate as Numeric)) as MaxLoggedOn FROM geb_LoggedOn WHERE LogonDate >= '" & ayear & amonth & aday & "' AND LogonDate <= '" & pjaar & pmaand & pdag & "'
However they don't give the correct results because both LogOffDate and LogOnDate are not taken from the same record whereas for the AVG and MAX calculations they should. Anybody any ideas ?
TIA!
Peter
-
I understand what you are looking for here is something like
Average logged in Time : x Hrs xx Mins Max logged in Time: x Hrs xx Mins
for all users/sessions between the data range specified by you...If that is the case then you can always get this data from the same query rather than using 2 different queries like ...
Try this...i think it will work....I didnt get the time to verify the query tho...
select Avg( DATEDIFF( ss, dateadd(ss, left(right(LogOffDate , 6), 2) * 3600
+ substring(right(LogOffDate , 6), 3, 2) * 60
+ right(LogOffDate , 2), convert(datetime, Left(LogOffDate, 8) )), dateadd(ss, left(right(LogonDate, 6), 2) * 3600
+ substring(right(LogonDate, 6), 3, 2) * 60
+ right(LogonDate, 2), convert(datetime, Left(LogonDate, 8) )) ) )
FROM geb_LoggedOn WHERE LogonDate >= '" & ayear & amonth & aday & "' AND LogonDate <= '" & pjaar & pmaand & pdag & "'
Just replace the AVG with MAX and that will give you the Max logged in Secs. Everything is in seconds...You can hack into and change it to suit ur requirements.
Cheers!
Arvind -
I understand what you are looking for here is something like
Average logged in Time : x Hrs xx Mins Max logged in Time: x Hrs xx Mins
for all users/sessions between the data range specified by you...If that is the case then you can always get this data from the same query rather than using 2 different queries like ...
Try this...i think it will work....I didnt get the time to verify the query tho...
select Avg( DATEDIFF( ss, dateadd(ss, left(right(LogOffDate , 6), 2) * 3600
+ substring(right(LogOffDate , 6), 3, 2) * 60
+ right(LogOffDate , 2), convert(datetime, Left(LogOffDate, 8) )), dateadd(ss, left(right(LogonDate, 6), 2) * 3600
+ substring(right(LogonDate, 6), 3, 2) * 60
+ right(LogonDate, 2), convert(datetime, Left(LogonDate, 8) )) ) )
FROM geb_LoggedOn WHERE LogonDate >= '" & ayear & amonth & aday & "' AND LogonDate <= '" & pjaar & pmaand & pdag & "'
Just replace the AVG with MAX and that will give you the Max logged in Secs. Everything is in seconds...You can hack into and change it to suit ur requirements.
Cheers!
Arvind -
Ooops ....Sorry abt that!!
Arvind -
Hi,
Not exactly, I 'simply' need the average time that a user was logged on (so e.g. 50 users did log on, the average time that they where logged on was 28.5 minutes), and the max time one user was logged on (the one that spent the most time, e.g. 3 hrs and 25 min)
-
I think that is exactly what this will return ....But it will take each login attempt as an user.
Cheers!
Arvind -
Hi,
Thanx for you comments, indeed the main problem was the fact that I was trying to calculate hrs/mins/secs as integers whereas of course there are no 100 seconds in a minute ;-)....
Via another forum I also got this:
SELECT
AVG(
DATEDIFF(second,
CONVERT(datetime, STUFF(STUFF(STUFF(LogonDate,
9, 0, ' '), 12, 0, ':'), 15, 0, ':'), 112),
CONVERT(datetime, STUFF(STUFF(STUFF(LogoffDate,
9, 0, ' '), 12, 0, ':'), 15, 0, ':'), 112)
)
) as AvgLoggedOn
FROM geb_LoggedOn
WHERE LogonDate >= '" & ayear & amonth & aday & "'
AND LogonDate <= '" & pjaar & pmaand & pdag & "'
SELECT
MAX(
DATEDIFF(second,
CONVERT(datetime, STUFF(STUFF(STUFF(LogonDate,
9, 0, ' '), 12, 0, ':'), 15, 0, ':'), 112),
CONVERT(datetime, STUFF(STUFF(STUFF(LogoffDate,
9, 0, ' '), 12, 0, ':'), 15, 0, ':'), 112)
)
) as AvgLoggedOn
FROM geb_LoggedOn
WHERE LogonDate >= '" & ayear & amonth & aday & "'
AND LogonDate <= '" & pjaar & pmaand & pdag & "'
And yep, it's working correctly now. Thanx!
Greetz,
Peter
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply