SyteLine Background Task History Query Generator for TaskMan

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN (N'BGTaskHistory', N'ActiveBGTasks')
ORDER BY TABLE_NAME, ORDINAL_POSITION;

SELECT TOP (100)
    h.[TaskNumber],
    h.[TaskName],
    h.[RequestingUser],
    h.[SubmissionDate],
    h.[StartDate],
    h.[CompletionDate],
    h.[ReturnStatus],
    h.[TaskErrorMsg]
FROM dbo.BGTaskHistory h
WHERE h.[CompletionDate] IS NOT NULL
  AND ISNULL(h.[TaskErrorMsg], N'') <> N''
  AND h.[SubmissionDate] >= DATEADD(day, -7, GETDATE())
  AND h.[TaskName] LIKE N'%'
ORDER BY h.[CompletionDate] DESC;

SELECT
    h.[TaskName] AS task_name,
    COUNT(*) AS runs,
    SUM(CASE WHEN h.[CompletionDate] IS NOT NULL AND ISNULL(h.[TaskErrorMsg], N'') = N'' THEN 1 ELSE 0 END) AS successful_runs,
    AVG(CAST(DATEDIFF(second, h.[StartDate], h.[CompletionDate]) AS bigint)) AS avg_duration_s,
    MAX(DATEDIFF(second, h.[StartDate], h.[CompletionDate])) AS max_duration_s,
    AVG(CAST(DATEDIFF(second, h.[SubmissionDate], h.[StartDate]) AS bigint)) AS avg_queue_delay_s,
    SUM(CASE WHEN DATEDIFF(second, h.[StartDate], h.[CompletionDate]) >= 600 THEN 1 ELSE 0 END) AS slow_runs
FROM dbo.BGTaskHistory h
WHERE h.[SubmissionDate] >= DATEADD(day, -7, GETDATE())
  AND h.[TaskName] LIKE N'%'
GROUP BY h.[TaskName]
ORDER BY avg_duration_s DESC;

SELECT a.*
FROM dbo.ActiveBGTasks a
WHERE a.[SubmissionDate] < DATEADD(minute, -60, GETDATE())
ORDER BY a.[SubmissionDate];

SELECT a.*
FROM dbo.ActiveBGTasks a
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.BGTaskHistory h
    WHERE h.[TaskNumber] = a.[TaskNumber]
)
ORDER BY a.[SubmissionDate];