Bidrag 2

create function dbo.GetNums(@N as bigint) returns table
as
return
  with
  L0 as (select 1 as C union all select 1),
  L1 as (select 1 as C from l0 as A cross join l0 as B),
  L2 as (select 1 as C from l1 as A cross join l1 as B),
  L3 as (select 1 as C from l2 as A cross join l2 as B),
  L4 as (select 1 as C from l3 as A cross join l3 as B),
  L5 as (select 1 as C from l4 as A cross join l4 as B)
  select top (@N) row_number() over(order by (select null)) as N
  from (
       select 1 as C
       from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as T10(C)
       union all
       select 1 as C
       from L5
       ) as N
  order by C;

go

create table dbo.CS(CS bit not null, index IX_CS clustered columnstore);

go

declare @N int = 2147483647;


with P as
(
  select V.AssemblyLine,
         V.MinLogTime,
         P.Interval
  from (
       select V.AssemblyLine,
              cast(cast(min(V.LogTime) as date) as datetime2(0)) as MinLogTime,
              max(V.LogTime) as MaxLogTime,
              count_big(*) as LogItemCount
       from dbo.[Values] as V
       group by V.AssemblyLine
       )
       as V
    inner join dbo.Parameters as P
      on V.AssemblyLine = P.AssemblyLine
    where V.LogItemCount < 1 + (datediff_big(minute, V.MinLogTime, V.MaxLogTime) / P.Interval)
),
C1 as
(
  select V.AssemblyLine,
         datediff_big(minute, P.MinLogTime, V.LogTime) / P.Interval as LogTimeSlot,
         P.MinLogTime,
         P.Interval
  from dbo.[Values] as V
    inner join P
      on V.AssemblyLine = P.AssemblyLine
),
C2 as
(
  select C1.AssemblyLine,
         C1.Interval,
         C1.LogTimeSlot,
         lead(C1.LogTimeSlot) over(partition by C1.AssemblyLine order by C1.LogTimeSlot) as LeadLogTimeSlot,
         C1.MinLogTime
  from C1
)
insert into dbo.[Values] with(tablock) (AssemblyLine, LogTime)
select C2.AssemblyLine,
       dateadd(day, N.MinToAdd / 1440, dateadd(minute, N.MinToAdd % 1440, C2.MinLogTime))
from C2
  cross apply (
              select top(@N) C2.Interval * (N.N + C2.LogTimeSlot) as MinToAdd
              from dbo.GetNums(C2.LeadLogTimeSlot - C2.LogTimeSlot - 1) as N
                    ) as N
  left outer join dbo.CS on 0 = 1
where C2.LeadLogTimeSlot - C2.LogTimeSlot > 1
option(recompile, optimize for (@N = 1));