mardi 4 août 2015

filter data within fraction of minutes in oracle sql

I have some data coming from source. I am changing the scenario here. If a particular Office_ID has been deactivated and it has all three clients (A,B,C) for a particular day, then we have to check whether all clients have gone or not. If yes then time frame for all clients should be within 10 Minutes. And this should repeat three times in a day for a particular office. Then we declare the office as closed. Here is a sample data:

+-----------+---------+--------------+----------------+---------+---------------+
| Office_ID |  NAME   |  FAIL_TIME   |  ACTIVITY_DAY  | CLIENT  | VENTURE_COUNT |
+-----------+---------+--------------+----------------+---------+---------------+
|      1002 |     xyz |     5:39:00  |     23/01/2015 |       A |             3 |
|      1002 |     xyz |     17:49:00 | 23/12/2014     |       A |             3 |
|      1002 |     xyz |     18:41:57 | 1/5/2014       |       B |             3 |
|      1002 |     xyz |     10:32:00 | 1/7/2014       |       A |             3 |
|      1002 |     xyz |     10:34:23 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     10:35:03 | 1/7/2014       |       C |             3 |
|      1002 |     xyz |     12:08:52 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     12:09:00 | 1/7/2014       |       A |             3 |
|      1002 |     xyz |     12:26:10 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     13:31:32 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     15:24:06 | 1/7/2014       |       B |             3 |
|      1002 |     xyz |     15:55:06 | 1/7/2014       |       C |             3 |
+-----------+---------+--------------+----------------+---------+---------------+



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire