CODEWITHSUNDEEP
sqlsql-serverdatabase
Terry Chan
Terry Chan

Reputation: 25

SQL: How to get the correct resource time for each time record after removed overlap

I have set of machines used record, and there is more than one work piece used on a machine and sometime each record time period are overlapped. Right now I would get the actual time used on each record after removed overlapped time....but that is difficult to me as a SQL beginner....Hope anyone can give me help...thx

The total used time for CW01 should be 22 hrs and EN01 is 8 hrs after removed ovelap time

Orignal TABLE

   operid    machine   itemid          start_time                  end_time               time_used  
   --------------------------------------------------------------------------------------------------      
    454       CW01       31     2017-10-16 08:30:00.000      2017-10-16 16:30:00.000           8
    456       CW01       33     2017-10-16 13:30:00.000      2017-10-16 18:30:00.000           5
    457       CW01       35     2017-10-16 21:30:00.000      2017-10-17 06:30:00.000           9
    458       CW01       36     2017-10-16 15:30:00.000      2017-10-16 23:30:00.000           8
    460       EN01       70     2017-10-16 08:30:00.000      2017-10-16 10:30:00.000           2
    462       EN01       71     2017-10-16 09:30:00.000      2017-10-16 16:30:00.000           7

Desired TABLE

   operid    machine   itemid          start_time                  end_time               time_used  
   --------------------------------------------------------------------------------------------------      
    454       CW01       31     2017-10-16 08:30:00.000      2017-10-16 16:30:00.000           6.33333
    456       CW01       33     2017-10-16 13:30:00.000      2017-10-16 18:30:00.000           2.33333
    457       CW01       35     2017-10-16 21:30:00.000      2017-10-17 06:30:00.000           8
    458       CW01       36     2017-10-16 15:30:00.000      2017-10-16 23:30:00.000           5.33333
    460       EN01       70     2017-10-16 08:30:00.000      2017-10-16 10:30:00.000           1.5
    462       EN01       71     2017-10-16 09:30:00.000      2017-10-16 16:30:00.000           6.5

Upvotes: 2

Views: 81

Answers (1)

Mike
Mike

Reputation: 2005

with Q(operid, machine, tm) as(
   -- split rows to time points (start(3), end(4), intersect time (1,2))
   select distinct A.operid,A.machine,
          case N when 1 then (case when A.start_time>B.start_time
                              then A.start_time else B.start_time end)
                 when 2 then (case when A.end_time < B.end_time
                               then A.end_time else B.end_time end)
                 when 3 then A.start_time
                 else A.end_time
          end
     from TabD A
    cross join (select 1 N union all select 2 union all select 3 union all select 4) N
     left join TabD B
       on B.machine=A.machine and B.operid!=A.operid
      and B.start_time<A.end_time and B.end_time>A.start_time and N.N in(1,2)
)
select operid, machine, sum(time_len)
  from (
   select X.operid, A.machine, s_tm, e_tm,
          datediff(second, s_tm, e_tm)/3600.0/count(1)  time_len
     from (
      -- join time points to intervals
      select operid, machine, tm e_tm,
             lag(tm) over(partition by machine,operid order by tm) s_tm
        from Q
       where Q.tm is not null
     ) X,
       TabD A -- join source rows for interval of time for count it
    where s_tm is not null
      and A.start_time<X.e_tm and A.end_time>X.s_tm
      and A.machine=X.machine
    group by X.operid, A.machine, s_tm, e_tm
  ) Y
group by operid, machine

Example on sqlfiddle.com

Upvotes: 1

Related Questions