CREATE TABLE [dbo].[process_log](
	[RunDateTime] [datetime] NOT NULL,
	[Process] [varchar](200) NOT NULL,
	[DisplayName] [varchar](200) NULL,
	[Schedule] [varchar](200) NULL,
	[AffectedTables] [varchar](4000) NULL,
	[AfftecedViews] [varchar](4000) NULL,
 CONSTRAINT [PK_process_log] PRIMARY KEY CLUSTERED 
(
	[RunDateTime] ASC,
	[Process] ASC
)
GO

CREATE TABLE [dbo].[adf_level](
	[ProcessName] [varchar](200) NOT NULL,
	[TableName] [varchar](200) NOT NULL,
	[RunDateTime] [datetime] NOT NULL,
	[DataDateTime] [datetime] NULL,
 CONSTRAINT [PK_process_level] PRIMARY KEY CLUSTERED 
(
	[ProcessName] ASC,
	[TableName] ASC,
	[RunDateTime] ASC
)
GO

ALTER TABLE [dbo].[adf_level] ADD  CONSTRAINT [DF_adf_level_RunDateTime]  DEFAULT (getdate()) FOR [RunDateTime]
GO

CREATE VIEW [dbo].[vScheduledProcesses] as
SELECT	DPL.DisplayName as [Display Name],
		DPL.[Process] as [Process],
		DPL.[Schedule],
		CAST(MD.MaxDateTime as Date) as [Last Run Date],
		CAST(MD.MaxDateTime as Time) as [Last Run Time],
		CASE 
			WHEN DPL.Schedule='Daily' AND DATEDIFF(DAY,MD.MaxDateTime,GETDATE())>1 THEN 'Issue'
			WHEN DPL.Schedule='Daily' AND DATEDIFF(DAY,MD.MaxDateTime,GETDATE())<=1 THEN 'OK'
			WHEN DPL.Schedule='Weekly' AND DATEDIFF(DAY,MD.MaxDateTime,GETDATE())>7 THEN 'Issue'
			WHEN DPL.Schedule='Weekly' AND DATEDIFF(DAY,MD.MaxDateTime,GETDATE())<=7 THEN 'OK'
			ELSE NULL
		END as [Status],
		DPL.AffectedTables as [Affected Tables],
		DPL.AfftecedViews as [Affected Views]
FROM [dbo].[process_log] DPL
INNER JOIN (SELECT Process, MAX([RunDateTime]) as MaxDateTime FROM dbo.process_log GROUP BY Process) MD ON DPL.Process=MD.Process AND DPL.RunDateTime=MD.MaxDateTime
GO

CREATE PROCEDURE [dbo].[DailyJobs]
AS
BEGIN
	-- This procedure is scheduled to run every day on the SQL Server Agent
	
	-- Execute other Stored Procedures here:
	
		-- Description of stored procedure
		BEGIN TRY
			{ sql_statement | statement_block };
		END TRY
		BEGIN CATCH
   		RAISERROR ('{SQL Statement Name} was unable to run',1,0);
		END CATCH
		
	-- Log Maintenance
	DELETE DPL
	FROM [dbo].[process_log] DPL
	INNER JOIN (SELECT Process, MAX(RunDateTime) MaxDateTime FROM [dbo].[process_log] GROUP BY Process) DD ON DPL.Process=DD.Process
	WHERE DPL.Schedule='Daily' 
		AND DPL.RunDateTime<DATEADD(MONTH,-1,GETDATE())
		AND DPL.RunDateTime<DD.MaxDateTime
	;

	DELETE APL
	FROM [dbo].[adf_level] APL
	INNER JOIN (SELECT ProcessName, MAX(RunDateTime) MaxDateTime FROM [dbo].[adf_level] GROUP BY ProcessName) DD ON APL.ProcessName=DD.ProcessName
	WHERE RunDateTime<DATEADD(MONTH,-1,GETDATE())
		AND RunDateTime<DD.MaxDateTime
	;

END
GO

CREATE PROCEDURE [dbo].[WeeklyJobs]
AS
BEGIN
	-- This procedure is scheduled to run every week on the SQL Server Agent
		
		-- Execute other Stored Procedures here: -- Description of stored procedure
		BEGIN TRY
			{ sql_statement | statement_block };
		END TRY
		BEGIN CATCH
  			RAISERROR('{SQL Statement Name} was unable to run',1,0);
		END CATCH

	-- Log Maintenance
	DELETE DPL
	FROM [dbo].[process_log] DPL
	INNER JOIN (SELECT Process, MAX(RunDateTime) MaxDateTime FROM [dbo].[process_log] GROUP BY Process) DD ON DPL.Process=DD.Process
	WHERE DPL.Schedule='Weekly' 
		AND DPL.RunDateTime<DATEADD(MONTH,-1,GETDATE())
		AND DPL.RunDateTime<DD.MaxDateTime
	;

END
GO