Oracle SQL: Counting in 4h?

Question

I have oracle 10g database with table of many records. Table has data for employees receiving some kind of orders. Most usable columns are employee, dt and orderId (I've attached example). I want to construct select that will select records of an employee that had 6 or more orders in any 4h. I used regular join, various functions but I still didn't made usable select. I couldn't make select that will for each record search in past(4h) and see if there are 6 orders for same employee.

Any help or solution is appreciated.

BR,

IJ

http://sqlfiddle.com/#!2/77418/1

Output of select should be:

Joe 19.2.2013 13:28:09 36589589

Joe 19.2.2013 13:50:59 36589593

Matt 19.2.2013 13:57:02 36589594

Joe 19.2.2013 14:19:46 36589598

Benny 19.2.2013 14:50:28 36589601

Benny 19.2.2013 14:54:58 36589602

Benny 19.2.2013 14:56:35 36589603

Ray 19.2.2013 15:26:39 36589607

Ray 19.2.2013 15:26:41 36589608

Ray 19.2.2013 15:26:44 36589609

Ray 19.2.2013 15:48:11 36589611

Benny 19.2.2013 16:48:52 36589614

Benny 19.2.2013 16:49:40 36589615

Benny 19.2.2013 16:50:38 36589616

Johnny 19.2.2013 17:37:33 36589620

Johnny 19.2.2013 17:51:41 36589621

Joe 19.2.2013 18:16:55 36589625

Johnny 19.2.2013 18:34:14 36589626

Matt 19.2.2013 21:13:50 36589632

Answer 1

something like this should do it:

select distinct employee
  from (select employee, dt, orderid,
               (select count(*)
                    from emp_data ed2
                   where ed2.employee = ed.employee
                     and ed2.dt >= ed.dt
                     and ed2.dt <= ed.dt + (4/24)) cnt
            from emp_data ed)
 where cnt >= 6;

or replace and ed2.dt <= ed.dt + (4/24) with and ed2.dt < ed.dt + (4/24) if you didn't want to count say from 09:00:00 - 13:00:00 but only to 12:59:59.

Answer 2

You can do this with the lag() function:

select t.*, 
from (select t.*, lag(dt, 6) over (partition by employeeId order by dt) as ord6dt
      from t
     ) t
where dt - ord6dt < 4.0/24

This just looks at order 6 before the current and compares the dates. Only rows where the difference in time is less than 4 hours are kept.