-- Clear out the unbilled jobs table delete from Argos7Alerts.dbo.UnbilledJobs; go -- Populate the unbilled jobs table declare @CHECKDATE datetime; declare @WAITDAYS int; set @WAITDAYS = 4; set @CHECKDATE = CAST(CONVERT(varchar(11),DATEADD(dd,-(@WAITDAYS),GETDATE()),101) AS datetime); insert into Argos7Alerts.dbo.UnbilledJobs (Username,OldestJob, NumJobs) ( select * from ( select username, MIN(TrackedDateLocal) as 'OldestJob', Count(TrackedDateLocal) as 'NumPending' from ( select UserName, TrackedDateLocal from Argos7.dbo.queuetable )SubQueryA WHERE UserName IS NOT NULL group by UserName )SubQueryB where OldestJob < @CHECKDATE ) go -- Mail the pending jobs if there are any declare @NUMPENDING int; declare @MAILTO varchar(255); select @NUMPENDING = COUNT(*) from Argos7Alerts.dbo.UnbilledJobs; select @MAILTO = Value from Argos7.dbo.argGlobalSettings where Name = 'SiteInfoAdminEmail'; IF (@NUMPENDING > 0) BEGIN PRINT 'Condition Met' DECLARE @TABLEHTML NVARCHAR(MAX); SET @TABLEHTML = N'
User Name | Oldest Job | Number of Jobs Unbilled |
---|