I need to capture downtime/breakdown time in IFS10 maintenance for previous 7 days

Maybe use something like below in an IAL and use the value of avg_down_time to show up on a counter element?

SELECT w.wo_no,
       MAX(CASE
             WHEN w.new_value = 'WORKDONE' THEN
              w.dt_cre
           END) - MAX(CASE
                        WHEN w.new_value = 'FAULTREPORT' THEN
                         w.dt_cre
                      END) down_time,
       AVG(MAX(CASE
                 WHEN w.new_value = 'WORKDONE' THEN
                  w.dt_cre
               END) - MAX(CASE
                            WHEN w.new_value = 'FAULTREPORT' THEN
                             w.dt_cre
                          END)) over() avg_down_time

  FROM work_order_journal w
 WHERE w.value_source = 'ActiveSeparate'
   AND w.new_value IN ('FAULTREPORT', 'WORKDONE') HAVING
 MAX(CASE
             WHEN w.new_value = 'FAULTREPORT' THEN
              w.dt_cre
           END) >= SYSDATE - 7
   AND MAX(CASE
             WHEN w.new_value = 'WORKDONE' THEN
              w.dt_cre
           END) - MAX(CASE
                        WHEN w.new_value = 'FAULTREPORT' THEN
                         w.dt_cre
                      END) IS NOT NULL
 GROUP BY w.wo_no
 

Can you add a parameter in your lobby (with default value 7) and then add the same in your data source e.g. $days$, to limit the result by the number of days you specify in the parameter?

Would be helpful if you could post the query. 

Thanks for this response; however, if i’m honest, it was a bit too complicated for me to follow. I will certainly keep a copy of this as i am continually gaining knowledge of IFS/SQL.  

DaveNorman,

Is it not an option to retrieve the dates from both statuses from the WO Journal? Subtract these two and you will have the downtime.

Regards,
Steve