oracle SQL查询减去多个日期

xt0899hw  于 2023-11-17  发布在  Oracle
关注(0)|答案(1)|浏览(135)

我需要写一个报告/查询“机械表”与输入参数-网站,机器_部分,从_日期和到_日期(日/月/年)
本表中的数据为-
网站|机器零件|旧状态|新状态|日记账日期

London, Ship1, OOP, IO, 04/06/2023 
London, Ship1, IO, OOP, 03/05/2023
London, Ship1, OOP, IO, 04/04/2023 
London, Ship1, IO, OOP, 03/03/2023

字符串
我需要这样写它,当它是“OOP”(运行外)和“IO”(运行中)的日期之间的差异是派生的所有这些天内的日期输入参数和“运行外小时数”是加起来的参数范围内的所有行。
我尝试了自定义函数,但我不知道如何为所有行添加它们

xghobddn

xghobddn1#

试试这个查询

SELECT 
    Site, 
    Machine_part, 
    SUM(OutageHours) AS Total_Outage_Hours
FROM (
    SELECT 
        Site, 
        Machine_part, 
        Journal_date, 
        LAG(Journal_date) OVER (PARTITION BY Site, Machine_part ORDER BY Journal_date) AS Prev_Journal_date, 
        New_status, 
        CASE 
            WHEN New_status = 'OOP' THEN 
                (Journal_date - LAG(Journal_date) OVER (PARTITION BY Site, Machine_part ORDER BY Journal_date)) * 24
            ELSE 0
        END AS OutageHours
    FROM machinery_table
    WHERE 
        Site = :site
        AND Machine_part = :machine_part
        AND Journal_date BETWEEN TO_DATE(:from_date, 'DD/MM/YYYY') AND TO_DATE(:to_date, 'DD/MM/YYYY')
)
GROUP BY Site, Machine_part;

字符串

相关问题