2 Replies Latest reply: Feb 8, 2013 2:13 PM by Joe C. RSS

    Count number of active logons at given time

    Joe C.
      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
          €$ħ₪
          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.
            Wow... I was over thinking that one!