This discussion is archived
2 Replies Latest reply: Feb 8, 2013 12:13 PM by Joe C. RSS

Count number of active logons at given time

Joe C. Newbie
Currently Being Moderated
I have this data:
WITH t AS
  (SELECT to_date('01/31/2013 08:31:00','MM/DD/YYYY HH24:MI:SS') logon_time,
    to_date('01/31/2013 08:39:00','MM/DD/YYYY HH24:MI:SS') logoff_time
  FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:31:00','MM/DD/YYYY HH24:MI:SS') logon_time,
    to_date('01/31/2013 08:33:00','MM/DD/YYYY HH24:MI:SS') logoff_time
  FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:32:00','MM/DD/YYYY HH24:MI:SS') logon_time,
    to_date('01/31/2013 08:32:00','MM/DD/YYYY HH24:MI:SS') logoff_time
  FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:32:00','MM/DD/YYYY HH24:MI:SS') logon_time,
    to_date('01/31/2013 08:34:00','MM/DD/YYYY HH24:MI:SS') logoff_time
  FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:39:00','MM/DD/YYYY HH24:MI:SS') logon_time,
    to_date('01/31/2013 08:41:00','MM/DD/YYYY HH24:MI:SS') logoff_time
  FROM dual
  ) ,
  u AS
  ( SELECT to_date('01/31/2013 08:31:00','MM/DD/YYYY HH24:MI:SS') ztime FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:32:00','MM/DD/YYYY HH24:MI:SS') ztime FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:33:00','MM/DD/YYYY HH24:MI:SS') ztime FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:34:00','MM/DD/YYYY HH24:MI:SS') ztime FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:35:00','MM/DD/YYYY HH24:MI:SS') ztime FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:36:00','MM/DD/YYYY HH24:MI:SS') ztime FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:37:00','MM/DD/YYYY HH24:MI:SS') ztime FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:38:00','MM/DD/YYYY HH24:MI:SS') ztime FROM dual
  UNION ALL
  SELECT to_date('01/31/2013 08:39:00','MM/DD/YYYY HH24:MI:SS') ztime FROM dual
  )
  SELECT * FROM t RIGHT JOIN u ON t.logon_time = u.ztime ORDER BY ztime;
And I would like to know how many logons are active for any given ztime. The psuedo code I was thinking was:
ztime, count where logon_time = ztime or ( logon_time < ztime and logoff_time >= ztime )
The results for the above woudl look like:
zTime               Logon_Count
01/31/2013 08:31:00 2
01/31/2013 08:32:00 4
01/31/2013 08:33:00 3
01/31/2013 08:34:00 2
01/31/2013 08:35:00 1
01/31/2013 08:36:00 1
01/31/2013 08:37:00 1
01/31/2013 08:38:00 1
01/31/2013 08:39:00 2
  • 1. Re: Count number of active logons at given time
    €$ħ₪ Expert
    Currently Being Moderated
    Try this ...
    WITH t AS
         (SELECT TO_DATE ('01/31/2013 08:31:00','MM/DD/YYYY HH24:MI:SS') logon_time,
                 TO_DATE ('01/31/2013 08:39:00', 'MM/DD/YYYY HH24:MI:SS') logoff_time
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:31:00','MM/DD/YYYY HH24:MI:SS') logon_time,
                 TO_DATE ('01/31/2013 08:33:00','MM/DD/YYYY HH24:MI:SS') logoff_time
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:32:00','MM/DD/YYYY HH24:MI:SS' ) logon_time,
                 TO_DATE ('01/31/2013 08:32:00','MM/DD/YYYY HH24:MI:SS') logoff_time
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:32:00','MM/DD/YYYY HH24:MI:SS') logon_time,
                 TO_DATE ('01/31/2013 08:34:00','MM/DD/YYYY HH24:MI:SS') logoff_time
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:39:00','MM/DD/YYYY HH24:MI:SS') logon_time,
                 TO_DATE ('01/31/2013 08:41:00','MM/DD/YYYY HH24:MI:SS') logoff_time
            FROM DUAL),
         u AS
         (SELECT TO_DATE ('01/31/2013 08:31:00', 'MM/DD/YYYY HH24:MI:SS') ztime
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:32:00', 'MM/DD/YYYY HH24:MI:SS') ztime
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:33:00', 'MM/DD/YYYY HH24:MI:SS') ztime
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:34:00', 'MM/DD/YYYY HH24:MI:SS') ztime
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:35:00', 'MM/DD/YYYY HH24:MI:SS') ztime
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:36:00', 'MM/DD/YYYY HH24:MI:SS') ztime
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:37:00', 'MM/DD/YYYY HH24:MI:SS') ztime
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:38:00', 'MM/DD/YYYY HH24:MI:SS') ztime
            FROM DUAL
          UNION ALL
          SELECT TO_DATE ('01/31/2013 08:39:00', 'MM/DD/YYYY HH24:MI:SS') ztime
            FROM DUAL)
    SELECT   u.ztime, COUNT (*) logon_count
        FROM t, u
       WHERE u.ztime BETWEEN logon_time AND logoff_time
    GROUP BY u.ztime
    ORDER BY u.ztime
  • 2. Re: Count number of active logons at given time
    Joe C. Newbie
    Currently Being Moderated
    Wow... I was over thinking that one!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points