Hoping that someone can offer some assistance, I am currently working on a piece of code which seems to be having issues. Unfortunately I cant post the underlying data but will try and explain.
The code currently cycles through a selection of kpi's and calculates if something needs to be run or not based on the current time and whether the individual kpi's need to be run. At the moment if I hard code a number of kpi's the code works fine but if I let it cycle through all the kpi's, ones that should not run are being told to run, also in the select that defines things like @Period, when I let it run through all KPI's its setting the @Period incorrectly when it shouldn't run but when its meant to run it set it fine.
Overall I have 2 issues which I think are related KPI's run when they are not meant to and when they are running when they are not meant to they pick up the wrong Period either way which is odd. I think what is happening is when I let it run all the way through the KPI's are getting mixed up but I cant see how this is happening, any help would be great before I go mad.
As you can see from my code there are 2 @current_timestamps, when its set to 23.45.18.873 it should not run some of the kpi's but it runs them all anyway and the 21.45.18.873 means that they can run.
declare @job_current_time_minute float,
@job_current_date_time_minute as datetime,
@current_timestamp as datetime
SET @job_current_time_minute = 52--cast(datepart(minute, getdate()) as float)
SET @job_current_date_time_minute = getdate()
--SET @current_timestamp = cast('2017-04-02 23:45:18.873' as datetime)
SET @current_timestamp = cast('2017-04-02 21:45:18.873' as datetime)
--Run: 88 6 0 52 5 5 5
--Run: 88 1 10 52 5 5 5
declare
@kpi_id int,
@kpi_parent_id int,
@sql nvarchar(max),
@kpi_last_result nvarchar(100),
@kpi_last_runtime datetime,
@kpi_params nvarchar(max),
@kpi_current_time_minute float,
@schedulue_minute float,
@reoccurrence float,
@result float,
@kpi2 int,
-- Email Variables
@kpi_name nvarchar(150),
@kpi_desc nvarchar(150),
@kpi_report_link nvarchar(150),
@kpi_email_subject nvarchar(150),
@kpi_email_body nvarchar(300),
@kpi_email_query nvarchar(max),
@kpi_sms_msg nvarchar(300);
SET @kpi_params = N'@retvalOUT varchar(max) OUTPUT';
select @kpi_id = min(kpi_id) from KPI where KPI_Active = 1; --and kpi_id in (86, 88)
while @kpi_id is not null
begin
select
@kpi_parent_id = kpi_parent_id,
@sql = kpi_Script,
@schedulue_minute = datepart(minute,s.Schedule_Start),
@reoccurrence = s.Reoccurrence,
@result = cast((@job_current_time_minute - datepart(minute,s.Schedule_Start)) / s.Reoccurrence as decimal(18,2)),
@kpi_name = kpi_name,
@kpi_desc = kpi_desc,
@kpi_report_link = kpi_report_link,
@kpi_email_subject = kpi_email_subject,
@kpi_email_body = kpi_email_body,
@kpi_email_query = kpi_email_query,
@kpi_sms_msg = kpi_sms_msg,
@kpi_current_time_minute = 60*DATEPART(HOUR, GETDATE())+DATEPART(MINUTE,GETDATE()),
@kpi2 = KPI_ID
from EWI..KPI
inner join EWI..Schedule s on KPI.Schedule_ID = s.Schedule_ID where kpi_id = @kpi_id--order by kpi_id asc
--set @sql = 'select @retvalOUT= (' + @sql + ')'
if floor(@result) <> ceiling(@result)
begin
PRINT 'Not Time to Run: ' + ' ' + cast(@kpi_id as varchar(11)) + ' ' + cast(@result as varchar(11))
end
else
begin
declare
@ThresholdID int,
@PeriodID int,
@Threshold_value int,
@Threshold_PosNeg nvarchar(5)
select @ThresholdID = Threshold_ID, @PeriodID = Period_ID, @Threshold_value = Threshold_value, @Threshold_PosNeg = Threshold_PosNeg from (
-- select * from (
select * from (
select KPI_ID, t.Threshold_ID, p.Period_ID, t.Threshold_Value, t.Threshold_PosNeg, p.DayStartTime, p.DayEndTime, Period_desc, [Day]
from EWI..Threshold t join
EWI..Period p on t.Period_ID = p.Period_ID where KPI_ID = @kpi_id
) x where (case when [Day] = datename(dw,getdate()) then Period_id
when [Day] = choose(datepart(dw, getdate()), 'WEEKEND','Weekday','Weekday','Weekday','Weekday','Weekday','WEEKEND') then Period_ID
when [Day] = 'All' then Period_ID end) is not null
) y where case when datediff(second,DayStartTime,DayEndTime) > 0 then
case when convert(time(0), @current_timestamp) >= DayStartTime and convert(time(0), @current_timestamp) < DayEndTime then 1 else 0 end
else case when convert(time(0), @current_timestamp) >= DayStartTime or convert(time(0), @current_timestamp) < DayEndTime then 1 else 0 end
end = 1
select 'Run: ' + cast(@kpi_id as varchar(11)) + ' ' + cast(@PeriodID as varchar(11)) + ' ' +
cast(@Threshold_value as varchar(11)) + ' ' + cast(@job_current_time_minute as varchar(11)) + ' ' +
cast(@result as varchar(11)) + ' ' +
cast(ceiling(@result) as varchar(11))+ ' ' +cast(floor(@result) as varchar(11))
end
select @kpi_id = min(kpi_id) from KPI where KPI_Active = 1 and kpi_id > @kpi_id;
end
