5 Replies Latest reply on Aug 16, 2018 12:05 PM by swaroop.gantela

    How can I calculate the total downtime of my system, which consists of many components?

    Florian Raab

      Hello,

       

      I want to calculate the total downtime of my system. There are many components that can be down at the same time. Whether or not  there's one or more of my components down..the whole system is down.

      I can calculate the downtimes of every component separately without any problems, because there's no possibility of receiving two errors at the same time from the same component, but if I want to see how much time the system was down, I receive some problems.

      My problem is:

      let's assume I have a error of Sys.Component1 at 11:00:00 and the error is resolved at 12:00:00 --> 1 hour of downtime of my system, no problem

      Is there an error of Sys.Component2, maybe starting at 11:40:00 and ending at 12:00:00 --> my calculation has the solution of 1 hour and 20 minutes of downtime

      if there's an error of Sys component 3, starting at 11:35:00 and ending at 13:00:00, I have a total downtime of 3 hours and 45 minutes..but in reality the downtime was "just" 3 hours.

       

      I think you can get the idea..
      Do you have any recommendations for me, which will solve my problem and get me to the solution I want to have?

        • 2. Re: How can I calculate the total downtime of my system, which consists of many components?
          Florian Raab

          Thank you for your reply, but I don't think that really is what I'm searching for.

          I don't want to make a static calculation and do it again every time I need this, I need something dynamic, also my SQL database is very large..so I don't think the way to cross join my table with another one is really sufficient.

           

          Maybe I just don't get the idea of what you try to tell me..

           

          But what I found in the other thread is an idea to restructure my SQL table like in

           

          https://redheadedstepdata.io/lookup-vs-transactional/?content=Reference%20Post

          Then I'd have in the column datetype entries like

          start 12:35:21

          start 12:37:51

          end  12:40:35

          start 12:41:10

          start 12:50:25

          start 12:56:58

          end  14:21:20

          end  xxxxxxx

          end  xxxxxxx

          But I still don't know how to figure out my table calculation.

          At first I thought about going from the first start to the next end, but if there are 3 starts and one end, then this end is not the real end of my downtime..so by having three starts, I have to have the third end entry to have the total downtime. I don't know how to really describe what my problem then is, but maybe you get the idea.

           

          Although, thank you very much for spending your time helping me!

          • 3. Re: How can I calculate the total downtime of my system, which consists of many components?
            swaroop.gantela

            Florian,

             

            Apologies for my previous response.

            It is the thorough general solution for such problems,

            but it is not practical for large datasets.

             

            It think there may be a way with your original data setup,

            with each row having the ErrorStart and ErrorResolved Times.

            Please see if the below could give ideas.

             

            I think you were correctly describing it earlier.

            It is then a series of conditionals to compare the current row to previous row.

             

            IF FIRST()=0 THEN SUM([downtime straight])  //  first row, return downtime
            ELSEIF ATTR([Error Start])>LOOKUP(ATTR([Error Resolved]),-1) // starts after previous resolved, return full downtime
                THEN SUM([downtime straight])                           
            ELSEIF ATTR([Error Resolved])<LOOKUP(ATTR([Error Resolved]),-1)  // resolved before previous, return 0
                THEN 0                                                      
            ELSEIF ATTR([Error Start])<LOOKUP(ATTR([Error Resolved]),-1)   // starts before previous resolved but ends after, return overhang
                THEN DATEDIFF('minute',LOOKUP(ATTR([Error Resolved]),-1),ATTR([Error Resolved]))

            END

             

            This may not cover all possible situations, but I think it gives the idea.


            You will need to check your Table Calculation Settings to match your true setup.

            I think the key thing is to put ErrorStart at the top.

             

            Please see workbook v10.3 attached in the Forum Thread.

             

            277489down.png

            • 4. Re: How can I calculate the total downtime of my system, which consists of many components?
              Florian Raab

              Hey,

              I worked around a little bit and solved it with a SQL Query, which was not too easy to receive the correct results for every scenario.

              but there it is.

               

              SELECT deviceid, min(starttime) starttime, MAX(endtime) endtime from

                     (SELECT DEVICEID, starttime, endtime, max(grp) over(partition by deviceid order by starttime asc) grp2

                     FROM

                             (SELECT deviceid, starttime, endtime,

                             CASE WHEN (starttime not between

                             min(prev_starttime) over(partition by deviceid order by starttime) and

                             max(prev_endtime) over (partition by deviceid order by starttime))

                             or

                             min(prev_starttime) over(partition by deviceid order by starttime) is null

                             THEN row_number() over(partition by deviceid order by starttime)

                             end grp

                             FROM

                                 (SELECT Deviceid, starttime, endtime,

                                 lag(starttime) over(partition by deviceid order by starttime) prev_starttime,

                                 lag(endtime) over(partition by deviceid order by starttime) prev_endtime

                                 from TABLENAME

                                 WHERE  ALARMCODE NOT IN ('1130', '4106', '4136', '1018')

                                 AND NOT ENDTIME < STARTTIME

                                 )

                             a)

                       b)

              group by DEVICEID, grp2

              order by starttime

               

               

              Thank you for your help

              1 of 1 people found this helpful
              • 5. Re: How can I calculate the total downtime of my system, which consists of many components?
                swaroop.gantela

                Florian,

                 

                Glad you were able to find the solution.

                Looks good. All the best.