Thanks for the reply.
- What version of ThingWorx are you using?
8.5
- What version of SQL are you using?
SQL 2017
- Would you share the code of the Stored Proc/service that you are executing?
Code follows, sanitized, of course. It just calls some data from a linked server, then does a bit of math and returns results.
<code>
Create Procedure LineEff AS
SET NOCOUNT ON
--production efficiency
Declare @StartDate datetime, @EndDate datetime
Declare @NumShifts int=7
Set @EndDate=getdate()
Select @StartDate=DATEADD(hh,-1*8*@NumShifts,@EndDate)
Select Top 1 @StartDate=convert(datetime,StartDate) from Shifts where StartDate < @StartDate order by StartDate desc
--Select @StartDate, @EndDate
Declare @Counters table(ID int identity(1,1), Name varchar(50), TS datetime, Value int)
Declare @Down table(ID int identity(1,1), Name varchar(50), TS datetime, Value int)
Declare @s varchar(max)
Set @s= 'SELECT NAME, TS, VALUE FROM HISTORY WHERE NAME IN (''xxxxxxxxxx',''xxxxxxxxxx'',''xxxxxxxxxx'') AND TS BETWEEN '''
+ FORMAT( @StartDate, 'dd-MMM-yy HH:mm', 'en-US' ) + ''' AND ''' + FORMAT( @EndDate, 'dd-MMM-yy HH:mm', 'en-US' ) +''' AND PERIOD=''00:01:00'''
Insert @Counters(Name, TS, Value)
EXECUTE(@s) AT IP21
Declare @q varchar(max)
Set @q= 'SELECT NAME, TS, VALUE FROM HISTORY WHERE NAME IN (''xxxxxxxxxxx'') AND TS BETWEEN '''
+ FORMAT( @StartDate, 'dd-MMM-yy HH:mm', 'en-US' ) + ''' AND ''' + FORMAT( @EndDate, 'dd-MMM-yy HH:mm', 'en-US' ) +''' AND PERIOD=''00:01:00'''
Insert @Down(Name, TS, Value)
EXECUTE(@q) AT IP21
--get a table of the last N shifts
Declare @Results table(ID int identity(1,1),StartDate datetime, EndDate datetime, ShiftID int, Crew varchar(5), TotalBags int,BPH float, Eff float)
Insert @Results(StartDate, EndDate, ShiftID)
Select top (@NumShifts) StartDate, EndDate ,ShiftID from Shifts where convert(datetime,StartDate) < @EndDate
order by StartDate desc
--for each shift, compute metrics
Declare @i int=1
Declare @TotalBags int, @RunMins int, @ShiftEnd datetime, @ShiftNum int, @CountTagName varchar(50), @ShiftStart datetime, @DownMins int, @TotalMins int, @LineEff float
While @i <= (Select max(ID) from @Results)
BEGIN
--get total bags for shift
--value of counter at shift end time
Select @ShiftStart=StartDate, @ShiftEnd = EndDate, @ShiftNum=ShiftID from @Results where ID=@i
If @ShiftNum=1
Set @CountTagName='xxxxxxxxxx'
If @ShiftNum=2
Set @CountTagName='xxxxxxxxxx'
If @ShiftNum=3
Set @CountTagName='xxxxxxxxxx'
If @i=1
BEGIN
Select top 1 @TotalBags=Value from @Counters where Name=@CountTagName order by TS desc
END
ELSE
BEGIN
Select TOP 1 @TotalBags=Value from @Counters where TS=@ShiftEnd and Name=@CountTagName
END
--get the total down minutes, calculate effeciency
If @i=1
BEGIN
Select @TotalMins=datediff(mi,@ShiftStart,getdate())
Select @DownMins=sum(Value) from @Down where TS between @ShiftStart and getdate()
Set @RunMins=@TotalMins-@DownMins
Set @LineEff=100.*@RunMins/@TotalMins
END
ELSE
BEGIN
Select @TotalMins=datediff(mi,@ShiftStart,@ShiftEnd)
Select @DownMins=sum(Value) from @Down where TS between @ShiftStart and @ShiftEnd
Set @RunMins=@TotalMins-@DownMins
Set @LineEff=100.*@RunMins/@TotalMins
END
If @LineEff <0
Set @LineEff=0
Update @Results set Totalbags=@TotalBags, BPH=@TotalBags*1./DATEDIFF(hh,@ShiftStart,@ShiftEnd), Eff=@LineEff where ID=@i
set @i=@i+1
END
Select ID, StartDate, EndDate,ShiftID, Totalbags, BPH, Eff as Eff,
Case when ShiftID=1 then 'A' when ShiftID=2 then 'B' when ShiftID=3 then 'C' end as Crew
from @Results
</ code>
