I would like to calculate how many minutes the type is running. The process will calculate every hour. But if on that hour got many types running, I want to know how many minutes for that process, when to start, and when the type is the end.

Based on the table below, if I got a different type within that hour, I want to know what time type A start and stops, then type B what time start and Ends. After type A end, what time can type A continue to start back :

Final table will show like this:

Does anyone know how to get do it using SQL query? Thanks in advance.

CodePudding user response：

select   "Type" 
        ,"Start"
        ,case when lead("Start") over(order by "Start") is null then concat(running_min, '-', 'end') else concat(running_min, '-', lead("Start") over(order by "Start")) end as Result
from    (
         select   "Type"    
                 ,"Start"
                 ,min("Start") over(partition by change_count order by "Start") as running_min
         from    (
                  select  "Type"    
                         ,"Start"
                         ,count(change) over(order by "Start") as change_count
                  from   (
                          select *
                                 ,case when "Type" <> lag("Type") over(order by "Start") then 1 end as change 
                          from t
                         ) t
                  ) t
         ) t

Fiddle