Find out records which are separated by a minute 1

[barnard90]

I have an ITEMS table. It maintains the record of all items which are in inventory.

The columns in the table are

Item_No, Time_Processed, Item_status.

All items come in a queue and get processed within a minute
So the Item_Status of items change from "Processing"
to "Completed"
Both these records are separated exactly by one minute of Time_Processed
That is , if for example an item with Item_No 100
is having Item_Status of "Processing" at 10:30:00AM (Time_Processed) , It would have an Item_status of "Completed" at 10:31:00AM .

The records would be like this in ITEMS table


Item_No Time_Processed Item_status
------------------------------------------------------
100 10:30:00AM Processing
100 10:31:00AM Completed

200 11:30:00AM Processing
200 11:31:00AM Completed


I would like to find out all the records( Same Item_No) which are separated by one minute of Time_Processed ,
and whose status changed from Processing to Completed

Could some one please suggest me a Query

Thanks

 

[SantaMufasa]

First, Barnard, what is the datatype of "Time_processed"? (Varchar2, Date)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[barnard90]

SantaMufasa

Time_processed is Date Datatype

Thanks

 

[Dagon]

Do you always have exactly two records ? If so, you could use a self-join:

select a.item, a.time_processed as proc_time,
b.time_processed as completed_time
from items a, items b
where a.item_no= b.item_no
and a.item_status = 'Processing'
and b.item_status = 'Completed'
and b.time_processed - a.time_processed < 1/1440

You could also consider using the LAG analytic function. Something like:

select * from
(select item_no, time_processed,
lag(time_processed) over (partition by item_No order by time_processed) as prev_time_processed
from items)
where time_processed - prev_time_processed < 1/1440
and  item_status = 'Completed'

 

[SantaMufasa]

Here are some test data:

ITEM_NO TIME       ITEM_STATUS
------- ---------- -----------
    100 10:30:00AM Processing
    100 10:31:00AM Completed
    110 10:35:00AM Processing
    120 11:30:00AM Processing
    120 11:32:00AM Completed
    200 11:30:00AM Processing
    200 11:31:00AM Completed

Here is alternative code you can use:

select a.item_no, to_char(a.time_processed,'hh:mi:ss PM') time
      ,a.item_status||' -> '||b.item_status status
      ,to_char(b.time_processed,'hh:mi:ss PM') time
from  items a, items b
where a.item_no = b.item_no
  and a.item_status = 'Processing'
  and b.item_status = 'Completed'
  and round((b.time_processed-a.time_processed)*(24*60)) = 1
/

ITEM_NO TIME        STATUS                   TIME
------- ----------- ------------------------ -----------
    100 10:30:00 AM Processing -> Completed  10:31:00 AM
    200 11:30:00 AM Processing -> Completed  11:31:00 AM

Let us know if Dagon's or my code is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[barnard90]

Dagon

Thanks for the code
It works

Thanks