﻿
CREATE PROCEDURE [dbo].[CRT_UpdateTasks_LastRun]

	
 @i_CRTT_ID int = null

AS

--- exec CRT_UpdateTasks_LastRun 2

set nocount ON



declare @i_Year			int,
		@i_Month		int,
		@i_Week			int,
		@i_Day			int,
		@i_Hour			int,
		@i_Minute		int,

		@CRTT_ID			int,
		@TableName			sysname,
		@Source_TableName	sysname,
		@UnitDefinition		varchar(15),
		@LastRun			datetime,
		@EndOfLastRun		datetime,
		@AffectedRows		int,
		@sql				varchar(max)


declare cTasks cursor local static for
	select CRTT_ID, CRTT_TableName, CRTT_Source_TableName, CRTT_UnitDefinition, CRTT_LastRun
	from CRT_tasks with (nolock)
	where 1=1 
	and CRTT_Name <> '1Minute'
	and CRTT_IsActive = 1 and CRTT_Source_TableName <> '' and (CRTT_ID = @i_CRTT_ID or @i_CRTT_ID is null)
	order by 1

open cTasks

fetch next from cTasks into @CRTT_ID, @TableName, @Source_TableName, @UnitDefinition, @LastRun

while @@fetch_status = 0
begin
	--raiserror('Update Tasks from table %s...', 0, 1, @TableName) WITH NOWAIT

	--SELECT @TableName TableName, @LastRun LastRun, @1Min_MinDate [1Min_MinDate], @1Min_MaxDate [1Min_MaxDate], @1Min_Period [1Min_Period]


		set @SQL = 'Update [dbo].[CRT_Tasks] set [CRTT_LastModifiedDate] = GetDate(), [CRTT_LastRun] = (select Max(CRTD_Period) from ' 
		+ quotename(@TableName) + ' with (nolock)) where [CRTT_ID] = ' + cast(@CRTT_ID as varchar(10)) 
		

		--select @SQL
		exec (@SQL)


	fetch next from cTasks into @CRTT_ID, @TableName, @Source_TableName, @UnitDefinition, @LastRun
end
close cTasks
deallocate cTasks