MAX & AVG from 1 record, not the whole table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

MAX & AVG from 1 record, not the whole table

Author

Topic

peterq
Starting Member

3 Posts

Posted - 2004-08-03 : 02:28:44

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. Am not a SQL wizard like many of you, so if anyone could share me some tips...

TIA!

Peter

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-08-03 : 03:35:30

Post your table definition (in the form of a CREATE TABLE statement) and some sample data (in the form of INSERT statements). This will help someone come up with an answer more quickly.

peterq
Starting Member

3 Posts

Posted - 2004-08-03 : 03:59:17

You're absolute right:

CREATE TABLE [dbo].[geb_LoggedOn] (
[LoggedOnID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[LogonDate] [varchar] (14) NULL ,
[LogoffDate] [varchar] (14) NULL ,
[DateLastActivity] [varchar] (14) NULL
)

INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (76,'20040724203500','20040724220000','20040724211700')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (55,'20040724223600','20040725005900','20040725005800')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (77,'20040726164800','20040726173000','20040726164800')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (44,'20040726214800','20040726231900','20040726231400')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (19,'20040726231900','20040726234700','20040726234600')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (42,'20040726234700','20040726235000','20040726234900')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (51,'20040727092300','20040727092400','20040727092300')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (76,'20040727141900','20040727142000','20040727142000')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (51,'20040728102100','20040728102800','20040728102700')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (55,'20040728210600','20040728230300','20040728230300')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (44,'20040802115000','20040802123000','20040802115300')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (42,'20040802132200','20040802132700','20040802132200')
INSERT INTO geb_loggedOn (UserID,LogonDate,LogOffDate,DateLastActivity) VALUES (44,'20040802235400','20040803014800','20040803014200')

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-08-03 : 04:19:40

quote:
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.

No, that bit's working fine. The problem is that you're doing integer arithmetic on something that you don't want to behave like an integer.
(Unless your minutes have 100 seconds, hours have 100 minutes, etc.

)
The comparisons should work because the strings you have collate in chronological order, but the arithmetic won't.
Convert those values into datetimes and use DATEDIFF.


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 & "'

peterq
Starting Member

3 Posts

Posted - 2004-08-03 : 04:29:10

Thank u, thank u, thank u...indeed that was it, it's working perfectly now!

Greetz,

Peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources