Scheduled Tasks can be used to execute SQL statements to accomplish a variety of tasks. 

Notes:

• Total length of command cannot exceed 2000 characters
• You can separate commands using double semicolons, i.e. ";;"

Example 1.  Update facility lock date thru the end of last month every 20th

1. Go to Scheduled Tasks.
2. Enter a Task Description (i.e. "Update Lock Date") and click on schedule tab.
   
   
3. Set Perform this task to monthly and set the Run ON to Day 20.  (Run AT can be set to anytime in the day).  Click on the What to Do? tab.
   
4. Set to Execute SQL and set SQL Statement to:
   UPDATE SETTINGS SET CURVALUE = CONVERT(VARCHAR(10), EOMONTH(DATEADD(MONTH,-1,GETDATE())), 101) WHERE SETTING = 'LockDate'
   
5. Click OK. 

Notes on SQL statement - The CURVALUE field is text, therefore the lock date must be formatted in mm/dd/yyyy.  The convert(varchar(1),...,101) performs this conversion.

 

Example 2: Update QC Limits for all variables that have QC limits based on the last 20 values

1. Name task and set schedule by to MONTHLY, Day 31 (Last day of month)
2. The first statement makes sure there are no QC Limits starting on the current date (GETDATE() returns the current date).  This ensures that we do not duplicate the record if the task gets run twice in one day.  Double semicolons separates commands.
   The query calculates plus and minus 2 and 3 standard deviations for each parameter that already has at least 1 QC record  (VARID IN (SELECT VARID FROM VARQC).  It looks at the last 20 records (RANK <=20) for each VARID (GROUP BY VARID).    

DELETE VARQC WHERE STARTDATE = CAST(GETDATE() AS DATE);;
INSERT INTO VARQC
SELECT 'SYS',GETDATE(),VARID, CAST(GETDATE() as DATE),
ROUND(AVG(CURVALUE) + 3 * STDEV(CURVALUE),4) UCL,ROUND(AVG(CURVALUE) + 2 * STDEV(CURVALUE),4) UWL, ROUND(AVG(CURVALUE),4) QCMEAN,ROUND(AVG(CURVALUE) - 2 * STDEV(CURVALUE),4) LWL, ROUND(AVG(CURVALUE) - 3 * STDEV(CURVALUE),4) LCL, NULL FROM (
SELECT DATESTAMP,VARID,CURVALUE,ROW_NUMBER() OVER(PARTITION BY VARID ORDER BY DATESTAMP DESC) as RANK FROM [DATATBL] WHERE DATESTAMP < GETDATE()) AS rs
WHERE RANK <=20 AND VARID IN (SELECT VARID FROM VARQC)
GROUP BY VARID

EXAMPLE 3: Execute a stored procedure to update QC Limits based on data for the last 60 days.

NOTE: Using a stored procedure allows you to build complex statements while staying under the 2000 character limit. 

1. Create the stored procedure using MS SQL Server Management Studio.

Note:  Database is OPSNUC23, you would need to adjust for your database

USE [OPSNUC23]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UPQCBYLASTDAYS] @nod int
AS
BEGIN

SET NOCOUNT ON;
DELETE VARQC WHERE STARTDATE = CAST(GETDATE() AS DATE);
INSERT INTO VARQC
SELECT 'SYS',GETDATE(),VARID, CAST(GETDATE() as DATE),
ROUND(AVG(CURVALUE) + 3 * STDEV(CURVALUE),4) UCL,ROUND(AVG(CURVALUE) + 2 * STDEV(CURVALUE),4) UWL,
ROUND(AVG(CURVALUE),4) QCMEAN,ROUND(AVG(CURVALUE) - 2 * STDEV(CURVALUE),4) LWL, ROUND(AVG(CURVALUE) - 3 * STDEV(CURVALUE),4) LCL, NULL
FROM [DATATBL] WHERE DATESTAMP > DATEADD(d,-@nod,GETDATE()) AND DATESTAMP < GETDATE()
AND VARID IN (SELECT VARID FROM VARQC)
GROUP BY VARID
END
GO

Note:  Database is OPSNUC23, you would need to adjust for your database\

2. Create a scheduled task.  For What to do, call the stored procedure with the number of days you want to go back to calculate. 

EXEC UPQCBYLASTDAYS 60

Example 4: Update a table in OPSROOT

1. Add the scheduled task to any database.  When referring to tables in opsroot use syntax "OPSROOT.dbo.tablename"

Example: This updates the facility notes field to have the date/time of the last login.