CODEWITHSUNDEEP
sqloracle-databaseplsql
user2607028
user2607028

Reputation: 345

SQL QUERY Oracle log analysis

I have a log in table that have something like this:

tablelog

date      |  time   | event      | program   | ordendate
20130722    070000    executing    program1    20130722
20130722    070040    end ok       program1    20130722
20130722    070100    executing    program1    20130722
20130722    070140    end ok       program1    20130722

I have a query

 select a.date || a.time as datetimeStart, 
        b.date || b.time as datetimeStop, 
        a.program, a.ordendate  
   from tablelog a, 
        tablelog b
  where a.date || a.time < b.date || b.time
    and a.event = "executing"
    and b.event = "end ok"

This return 3 executions but only I have 2... How can I fix this query???

Thank you!!

Upvotes: 0

Views: 161

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

As far as I understand it, you want to list sequential start/stops by program(?) This uses LEAD to do the work in a CTE, then just filters and orders using an outer query;

WITH cte AS (
  SELECT 
    CASE WHEN "event"='executing' THEN "date" || "time" END "datetimeStart",
    LEAD(CASE WHEN "event"='end ok' THEN "date" || "time" END) 
    OVER(PARTITION BY "program" ORDER BY "date","time") "datetimeStop",
    "program", "ordendate"
  FROM tablelog
)
SELECT * FROM cte
WHERE "datetimeStart" IS NOT NULL 
  AND "datetimeStop"  IS NOT NULL
ORDER BY "datetimeStart"

An SQLfiddle to test with.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The query that you are trying to do is best done using the analytic functions lag() or lead():

select dateTimeStart, dateTimeStop, program, orderdate
from (select tl.date || tl.time as datetimeStart,
             lead(tl.date || tl.time) over (partition by program order by date, time) as dateTimeStop,
             tl.*
      from tablelog tl
     ) tl
where tl.event = 'Executing';

Upvotes: 1

Max
Max

Reputation: 4077

I think, the above will return the following 3 records :-

  1. first record matched with second record
  2. first record matched with fourth record
  3. third record matched with fourth record.

To resolve that, try the following query (untested) :-

select * from
(select a.date || a.time as datetimeStart, 
        b.date || b.time as datetimeStop, 
        a.program, a.ordendate, row_number() over (partition by (a.date || a.time) order by (a.date || a.time)) rn  
   from tablelog a, 
        tablelog b
  where a.date || a.time < b.date || b.time
    and a.event = "executing"
    and b.event = "end ok")
where rn =1

Upvotes: 0

Related Questions