-- 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'Unbilled Jobs
' +
N''+
N'| User Name | Oldest Job | Number of Jobs Unbilled |
|---|
'+
CAST((
SELECT
td = Username, '',
td = CONVERT(varchar,OldestJob,100), '',
td = NumJobs, ''
FROM Argos7Alerts.dbo.UnbilledJobs
ORDER BY Username
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+
N'
'
EXEC msdb.dbo.sp_send_dbmail
--@recipients = N'youremailaddress@yourdomain.com',
@recipients = @MAILTO,
@subject = 'ALERT: Argos users have unbilled jobs',
@profile_name = 'ArgosAlerts',
@body = @TABLEHTML,
@body_format = 'HTML'
END