Oracle SQL Developer time difference between records, drop any over 60 minutes

user_18986788 0
Viewed 39

hi experts looking for some advice on a problem im stuck on.

im running Oracle Database 18c Enterprise Edition Release 18.0.0.0.0

I have a query where i get all the records for the the max "order_number" per line.

I now need to only pull in the data records for each "order_number" if they were saved to the db within 60 minutes of the previous record.

e.g: i had 8 records for my order_number, the first 3 were all within 60 mins of the previous, but then there was a longer time for +60mins for the next 3 records, then the final 2 records were within 60 mins of their previous. given the above scenario, i would only need to pull in the final 2 records, as there was records longer than 60mins before that. also to note, i always need to bring in the max record per line, even if that record is +60mins than previous record

my current code:

select * from (
select
ID,
Create_Date_Time_Unix_TimeStamp,
Line,
Order_Number,
to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(Create_Date_Time_Unix_TimeStamp,'SECOND')+ 1/24  as " Date_Time",
max(Order_Number) over (partition by Line) max_Order
from...
join.
where..
)
Order_Number = max_Order

sample output data of query if i filter to just one Line:

ID Create_Date_Time_Unix_TimeStamp Line Order_Number Date_Time
100 1661170026 Line1 5000 22-AUG-2022 13:07:06
100 1661170026 Line1 5000 22-AUG-2022 13:07:06
100 1661170026 Line1 5000 22-AUG-2022 13:07:06
172 1661170872 Line1 5000 22-AUG-2022 13:21:12
172 1661170872 Line1 5000 22-AUG-2022 13:21:12
172 1661170872 Line1 5000 22-AUG-2022 13:21:12
180 1661170872 Line1 5000 22-AUG-2022 13:51:12
185 1661170872 Line1 5000 22-AUG-2022 15:00:00
185 1661170872 Line1 5000 22-AUG-2022 15:00:00
185 1661170872 Line1 5000 22-AUG-2022 15:00:00
186 1661170872 Line1 5000 22-AUG-2022 15:10:00
191 1661180040 Line1 5000 22-AUG-2022 15:18:00

so i need to get only the records that are within 60mins of the previous record taking into consideration that i will always need the most recent record on the line, even if it was +60mins

any ideas would be great thank you

difference oracle-sqldeveloper unix-timestamp time
1 Answers

Try

WITH prod_prev AS (
  SELECT p.*, 
         COALESCE(lag("Date_Time") OVER (PARTITION BY "Order_Number" ORDER BY "Date_Time" DESC), NULL) AS next_date,
         COALESCE(lag("Date_Time") OVER (PARTITION BY "Order_Number" ORDER BY "Date_Time" DESC), "Date_Time") - "Date_Time" AS diff
    FROM products p    
), 
run_sum AS (
  SELECT p.*,
         SUM(COALESCE(CASE WHEN diff > 1/24 THEN 10000 ELSE diff END,0))
             OVER (PARTITION BY "Order_Number" ORDER BY "Date_Time" DESC) AS run_diff 
    FROM prod_prev p    
)
SELECT * FROM run_sum WHERE run_diff < 10000
ORDER By "Order_Number", "Date_Time" DESC

Here ist a fiddle.

user_196571830
Related