SELECT phpbb_users.username AS username, 
       phpbb_users.user_id  AS user_id, 
       ems_in_out.timestamp AS timestamp, 
       status 
FROM   (SELECT user_id, 
               Max(timestamp) AS timestamp 
        FROM   ems_in_out 
        GROUP  BY user_id) recent, 
       ems_in_out, 
       phpbb_users 
WHERE  recent.user_id = ems_in_out.user_id 
       AND recent.user_id = phpbb_users.user_id 
       AND phpbb_users.username != 'Guest' 
       AND recent.timestamp = ems_in_out.timestamp 
       AND status = 'IN' 
       AND Now() - ems_in_out.timestamp <= '12 hours' 
ORDER  BY ems_in_out.timestamp 

SELECT phpbb_users.username AS username, 
       phpbb_users.user_id  AS user_id, 
       ems_in_out.time_in   AS time_in, 
       'IN'                 AS status 
FROM   ems_in_out, 
       phpbb_users 
WHERE  phpbb_users.username != 'Guest' 
       AND phpbb_users.user_id = ems_in_out.user_id 
       AND ems_in_out.time_out > now() 
ORDER  BY ems_in_out.time_in ASC; 

--check if currently in 
SELECT 1 
FROM   ems_in_out 
WHERE  ems_in_out.user_id = {$userID} 
       AND ems_in_out.time_out > now(); 

--checking in 
INSERT INTO ems_in_out 
            (user_id, 
             time_in, 
             time_out, 
             app_name, 
             ip) 
VALUES      ({$userID}, 
             Now(), 
             Now() + interval '12 hours', 
             {$appName}, 
             {$ip}); 

--checking out 
UPDATE ems_in_out 
SET    ems_in_out.time_out = now() 
WHERE  ems_in_out.user_id = {$userID} 
       AND ems_in_out.time_out > now(); 

Bad Munki fucked around with this message at 05:48 on Dec 3, 2012

SELECT count(DISTINCT( user_id )) as usage, max(s.start_time) as time_in
  FROM   ems_in_out_test, 
         ( SELECT start_time, 
                 start_time + interval '1 hour' as end_time 
           FROM generate_series((to_timestamp(to_char(now(), 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24') - interval '1 month')::timestamp, now(), '1 hour') start_time) s
  WHERE  time_in BETWEEN s.start_time AND s.end_time
          OR time_out BETWEEN s.start_time AND s.end_time
          OR s.start_time BETWEEN time_in AND time_out
          OR s.end_time BETWEEN time_in AND time_out
  GROUP BY s.start_time

SELECT a.start_time AS time_period, coalesce(b.usage, 0) AS usage
FROM 
( SELECT start_time, 
         start_time + interval '1 hour' AS end_time 
  FROM generate_series((to_timestamp(to_char(now(), 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24') - INTERVAL '1 month')::timestamp, now(), '1 hour') start_time) a
LEFT OUTER JOIN
( SELECT count(DISTINCT( user_id )) AS usage, max(s.start_time) AS time_in
  FROM   ems_in_out_test, 
         ( SELECT start_time, 
                  start_time + interval '1 hour' AS end_time 
           FROM generate_series((to_timestamp(to_char(now(), 'YYYY-MM-DD HH24'), 'YYYY-MM-DD HH24') - INTERVAL '1 month')::timestamp, now(), '1 hour') start_time) s
  WHERE  time_in BETWEEN s.start_time AND s.end_time
          OR time_out BETWEEN s.start_time AND s.end_time
          OR s.start_time BETWEEN time_in AND time_out
          OR s.end_time BETWEEN time_in AND time_out
  GROUP BY s.start_time) b
ON (a.start_time = b.time_in)
ORDER BY a.start_time ASC

Bad Munki fucked around with this message at 07:08 on Dec 3, 2012

select
   b.start_time,
   count(distinct e.user_id) as users
from generate_series(now()::date::timestamp - '1 month'::interval, now(), '1 hour'::interval') as b(start_time)
   left join ems_in_out_test e
      on (e.time_in <= b.start_time and e.time_out >= b.start_time)                      -- cases C and F
      or (e.time_in >= b.start_time and e.time_in  <= b.start_time + '1 hour'::interval) -- cases D and E     
group by b.start_time
order by b.start_time

Aredna fucked around with this message at 08:11 on Dec 3, 2012

technic fucked around with this message at 16:04 on Dec 3, 2012

select 
   b.start_time, 
   count(distinct e.user_id) as users 
from generate_series(now() - interval '1 month', now(), '1 hour') as b(start_time) 
   left join ems_in_out_test e 
      on (e.time_in <= b.start_time and e.time_out >= b.start_time) 
      or (e.time_in >= b.start_time and e.time_in  <= b.start_time + '1 hour'::interval) 
group by b.start_time 
order by b.start_time 

Bad Munki fucked around with this message at 17:33 on Dec 3, 2012

    SELECT b.start_time AS time, 
           count(distinct e.user_id) AS users 
    FROM   (select start_time, start_time + interval '1 hour' as end_time from generate_series(now() - INTERVAL '1 month', now(), '1 hour') as start_time) b
           LEFT JOIN ems_in_out_test e 
             ON (e.time_in BETWEEN b.start_time AND b.end_time
               OR e.time_out BETWEEN b.start_time AND b.end_time
               OR b.start_time BETWEEN e.time_in AND e.time_out
               OR b.end_time BETWEEN e.time_in AND e.time_out)
    group by b.start_time 
    order by b.start_time 

Bad Munki fucked around with this message at 17:14 on Dec 3, 2012

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> CREATE OR REPLACE
  2  PACKAGE MEET_SCORING_PACKAGE IS

...

 72  END MEET_SCORING_PACKAGE;
 73  /

Warning: Package created with compilation errors.

SQL> sho err
Errors for PACKAGE MEET_SCORING_PACKAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3      PL/SQL: Declaration ignored
3/3      PLS-00360: cursor declaration without body needs return type
4/3      PL/SQL: Declaration ignored
4/3      PLS-00360: cursor declaration without body needs return type
5/3      PL/SQL: Declaration ignored
5/3      PLS-00360: cursor declaration without body needs return type

Goat Bastard fucked around with this message at 07:49 on Dec 7, 2012

Suran37 fucked around with this message at 18:25 on Dec 7, 2012

SELECT ROWNUM, 
       xx.* 
FROM   (SELECT id, 
               Count(*) AS count 
        FROM   (SELECT fromid AS id 
                FROM   messages 
                WHERE  msgdate > '08-nov-12' 
                UNION ALL 
                SELECT touserid AS id 
                FROM   messages 
                WHERE  msgdate > '08-nov-12') 
        GROUP  BY id 
        ORDER  BY count DESC) xx 
WHERE  id IS NOT NULL 
       AND ROWNUM < 3; 

select * from messages where fromid = 1 or touserid=1;

Sockser fucked around with this message at 02:18 on Dec 9, 2012

Amarkov fucked around with this message at 05:14 on Dec 9, 2012

Sockser fucked around with this message at 06:06 on Dec 9, 2012

Amarkov fucked around with this message at 06:30 on Dec 9, 2012