Scheduled Task Stored Proc

by lokointhehouse2

HTML

USE [OpsCall]
GO
/****** Object:  StoredProcedure [dbo].[callQuality_Template]    Script Date: 5/3/2017 10:26:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--> CREATED BY: Dennis Chica
--> CREATED ON: May 3rd, 2017
--> REASON:     Pulls Data for Call Quality

--> REPORT TYPE: 1. EVAL 2. QUESTIONS 3. TRENDS

ALTER PROCEDURE [dbo].[callQuality_Template]
AS
BEGIN

	DECLARE 
	@reportTypeID int
	,@callQualityReportTypeID int
	,@today datetime2
	,@lastRanID int
	,@lastsuccessfulrun datetime2
	,@RUNDATE datetime2

	SET @reportTypeID = 1
	SET @callQualityReportTypeID = 1
	SET @today = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)

	/* check last successful run */
	select @lastsuccessfulrun = max(lastRanDate)
	from specialty_Call_Quality_Report_LastRan 
	where reportTypeID = @reportTypeID
		AND callQualityReportTypeID = @callQualityReportTypeID
		AND lastRanStatus = 2



	BEGIN
		IF  DATEADD(dd, DATEDIFF(dd, 0, @lastsuccessfulrun),0) = @today
			BEGIN 
				select 'NO NEED TO RUN'
			END
		ELSE
			BEGIN

				/* capture date to run */
				SET @RUNDATE = DATEADD(dd, DATEDIFF(dd, 0, @lastsuccessfulrun)+1,0)
				
				/* insert into scheduled task */
					INSERT INTO specialty_Call_Quality_Report_LastRan (reportTypeID, callQualityReportTypeID, lastRanDate)
					VALUES (@reportTypeID, @callQualityReportTypeID, @RUNDATE)
					SET @lastRanID = SCOPE_IDENTITY()


				select @RUNDATE

				/* update scheduled task */
					UPDATE specialty_Call_Quality_Report_LastRan 
					SET lastRanEnd = getdate(), lastRanStatus = 2
					WHERE callQualityLastRanID = @lastRanID
			END; 

	END;

	


END

JavaScript

USE [OpsCall]
GO

/****** Object:  Table [dbo].[specialty_Call_Quality_Report_LastRan]    Script Date: 5/3/2017 11:03:32 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[specialty_Call_Quality_Report_LastRan](
	[callQualityLastRanID] [int] IDENTITY(1,1) NOT NULL,
	[reportTypeID] [int] NULL,
	[callQualityReportTypeID] [int] NULL,
	[lastRanStart] [datetime2](7) NOT NULL,
	[lastRanEnd] [datetime2](7) NULL,
	[lastRanStatus] [int] NOT NULL,
	[lastRanDate] [datetime2](7) NULL,
	[dateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_specialty_Call_Quality_Report_LastRan] PRIMARY KEY CLUSTERED 
(
	[callQualityLastRanID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[specialty_Call_Quality_Report_LastRan] ADD  CONSTRAINT [DF_specialty_Call_Quality_Report_LastRan_lastRanStart]  DEFAULT (getdate()) FOR [lastRanStart]
GO

ALTER TABLE [dbo].[specialty_Call_Quality_Report_LastRan] ADD  CONSTRAINT [DF_specialty_Call_Quality_Report_LastRan_lastRanStatus]  DEFAULT ((1)) FOR [lastRanStatus]
GO

ALTER TABLE [dbo].[specialty_Call_Quality_Report_LastRan] ADD  CONSTRAINT [DF_specialty_Call_Quality_Report_LastRan_dateCreated]  DEFAULT (getdate()) FOR [dateCreated]
GO