CODEWITHSUNDEEP
sqloracle-databaseoracle11g
tumchaaditya
tumchaaditya

Reputation: 1297

Getting records corresponding to last run of tasks

I am looking at a database table that contains records of run times of a bunch of tasks running between about 11PM to 8AM. Some tasks begin and end before 12AM. Some begin and end after 12AM. And the remaining begin before 12AM and end after 12AM So, its kinda scattered.

Everyday, records corresponding to that day's run are appended to the database table. The records are created after the particular task has finished running. So, 1 record = 1 run of 1 particular task.
Some of the fields in table are Task Name, Start Date-Time, End Date-Time etc.

Now, I want to fetch records corresponding to last day's run of every task. So, if a particular task did run say yesterday night, I don't want its record from the day before yesterday. Also, if I wake up at say 3AM when half of the tasks have finished running, I want records corresponding to current run only.

How do I go about it?

What I've tried so far:
A bunch of things. The closes I came was by checking if start date and end date are within 1 day of current date. But, that will also take into account the tasks that finished running yesterday.
Basically, I am not able to figure out the logic since it involves tasks with differing dates.

Here's some sample data:
Case 1: Tasks ran yesterday:

  TASK_NAME          START_DTTM             END_DTTM
    TASK1          10/21/2013 11:21 PM    10/21/2013 11:58 PM
    TASK2          10/21/2013 11:30 PM    10/22/2013 03:58 AM
    TASK3          10/22/2013 12:33 AM    10/22/2013 07:58 AM

Query run on 10/22 morning after all tasks have finished running. Expected results: All tasks.

Case 2: Tasks did not run yesterday(say table is sorted by END_DTTM DESC)

   TASK_NAME          START_DTTM             END_DTTM
    TASK1          10/21/2013 11:21 PM    10/21/2013 11:58 PM
    TASK2          10/21/2013 11:30 PM    10/22/2013 03:58 AM
    TASK3          10/22/2013 12:33 AM    10/22/2013 07:58 AM

Query being run on morning of 10/23. Expected results: All the tasks(because NO task ran on on 10/22 night & 10/23 early morning)

Case 3: Tasks are running(say table is sorted by END_DTTM DESC): 

   TASK_NAME          START_DTTM             END_DTTM
    TASK1          10/21/2013 11:21 PM    10/21/2013 11:58 PM
    TASK2          10/21/2013 11:30 PM    10/22/2013 03:58 AM
    TASK3          10/22/2013 12:33 AM    10/22/2013 07:58 AM

Query being run at 10/22 3:00AM. Expected results: TASK1
Query being run at 10/22 4:00AM. Expected results: TASK1, TASK2

PS: Performance is not a big issue since number of records isn't that big.

Upvotes: 0

Views: 128

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Ok, after you clarified what you need, it seems to be enough to fetch those records that have start_dttm equal to the latest start_date for each task, but only if a task has finished (I assume that task that is not finished has NULL in end_dttm).

Solution:

CREATE TABLE tasks (
  task_name VARCHAR2(20),
  start_dttm DATE,
  end_dttm DATE
);

INSERT INTO tasks VALUES ('TASK1', TO_DATE('10/21/2013 23:21', 'MM/DD/YYYY HH24:MI'), TO_DATE('10/21/2013 23:58', 'MM/DD/YYYY HH24:MI'));
INSERT INTO tasks VALUES ('TASK2', TO_DATE('10/21/2013 23:30', 'MM/DD/YYYY HH24:MI'), TO_DATE('10/22/2013 03:58', 'MM/DD/YYYY HH24:MI'));
INSERT INTO tasks VALUES ('TASK3', TO_DATE('10/22/2013 00:33', 'MM/DD/YYYY HH24:MI'), TO_DATE('10/22/2013 07:58', 'MM/DD/YYYY HH24:MI'));

COMMIT;

SELECT task_name, start_dttm, end_dttm
  FROM tasks
WHERE (task_name, start_dttm) IN (
                      SELECT task_name, MAX(start_dttm)
                        FROM tasks
                      WHERE end_dttm IS NOT NULL
                      GROUP BY task_name
  )
;

Check at SQLFiddle: SQLFiddle demo

Upvotes: 1

Related Questions