Анализ ошибок в Tessa (Тесса)

-- Количество ошибок с группировкой по пользователям, категориям и дням
DECLARE @enddatetime DATETIME
DECLARE @startdatetime DATETIME
SET @enddatetime = DATEADD(HOUR, -3, GETDATE())
SET @startdatetime = DATEADD(DAY, -6, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 03:00:00.000'))

SELECT  @startdatetime AS StartDatetime, 
        @enddatetime AS EndDatetime;

-- Критические ошибки
DECLARE @critical_error_1 VARCHAR(100)
DECLARE @critical_error_2 VARCHAR(100)
DECLARE @critical_error_3 VARCHAR(100)
DECLARE @critical_error_4 VARCHAR(100)

SET @critical_error_1 = 'Exicution Timeout Expiried. %' --4
SET @critical_error_2 = 'Error converting file%' --8
SET @critical_error_3 = 'Не найдена карточка с идентификатором ''00000000-0000-0000-0000-000000000000''.' --14
SET @critical_error_4 = 'Нельзя загрузить карточку. Таймаут при ожидании, пока другой сотрудник изменяет карточку. %' --32

-- SELECT 1 (Errors types)

WITH
-- Условия для анализа ошибок
AnaliziedUsers
AS
(
    SELECT pr.ID
    FROM PersonalRoles pr (NOLOCK)
),

-- Ошибки
errors_select
AS
(
    SELECT  e.ID,
            e.Modified,
            e.ModofoedByName,
            CASE
                WHEN e.Text = LIKE @critical_error_1
                OR e.Text = LIKE @critical_error_2
                OR e.Text = LIKE @critical_error_3
                OR e.Text = LIKE @critical_error_4 THEN 1
                ELSE 0
            END AS RateError,
            CASE
                WHEN e.Text = LIKE @critical_error_1 THEN 'Exicution Timeout Expiried...'
                WHEN e.Text = LIKE @critical_error_2 THEN 'Error converting file...'
                WHEN e.Text = LIKE @critical_error_3 THEN @critical_error_3
                WHEN e.Text = LIKE @critical_error_4 THEN 'Нельзя загрузить карточку. Таймаут при ожидании, пока другой сотрудник изменяет карточку.'
            END AS TypeError,
            dbo.Localize(i.TypeCaption, 25) AS TypeCaption,
            e.Text,
            e.CardDigest,
            e.Category
    FROM Errors AS e (NOLOCK)
    INNER JOIN AnalizedUsers au ON au.ID = e.ModifiedByID
    LEFT JOIN WorkflowEngineErrors wee (NOLOCK) ON wee.ErrorCardID = e.ID
    LEFT JOIN WorkflowEngineProcesses wep (NOLOCK) ON wep.ID = wee.ID
    LEFT JOIN DocumentCommonInfo dci (NOLOCK) ON dci.ID = wep.CardID
    LEFT JOIN Instances i (NOLOCK) ON i.ID = dci.ID
    WHERE e.Modified > @startdatetime
)

SELECT  RateError AS [Рейтинг],
        CardType AS [Тип карточки документа],
        TypeError AS [Тип ошибки],
        CardDigest AS [Подкатегория],
        SUM(CASE WHEN Modified BETWEEN @startdatetime
                                   AND DATEADD(DAY, -5, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 02:59:59.999')) THEN 1 ELSE 0 END) AS [-6 дней],
        SUM(CASE WHEN Modified BETWEEN DATEADD(DAY, -5, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 03:00:00.000'))
                                   AND DATEADD(DAY, -4, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 02:59:59.999')) THEN 1 ELSE 0 END) AS [-5 дней],
        SUM(CASE WHEN Modified BETWEEN DATEADD(DAY, -4, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 03:00:00.000'))
                                   AND DATEADD(DAY, -3, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 02:59:59.999')) THEN 1 ELSE 0 END) AS [-4 дня],
        SUM(CASE WHEN Modified BETWEEN DATEADD(DAY, -3, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 03:00:00.000'))
                                   AND DATEADD(DAY, -2, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 02:59:59.999')) THEN 1 ELSE 0 END) AS [-3 дня],
        SUM(CASE WHEN Modified BETWEEN DATEADD(DAY, -2, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 03:00:00.000'))
                                   AND DATEADD(DAY, -1, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 02:59:59.999')) THEN 1 ELSE 0 END) AS [-2 дня],
        SUM(CASE WHEN Modified BETWEEN DATEADD(DAY, -1, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 03:00:00.000'))
                                   AND DATEADD(DAY, -0, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 02:59:59.999')) THEN 1 ELSE 0 END) AS [Вчера],
        SUM(CASE WHEN Modified BETWEEN DATEADD(DAY, -0, CONCAT(DATEPART(yyyy, @enddatetime), '-', DATEPART(mm, @enddatetime), '-', DATEPART(dd, @enddatetime), ' 03:00:00.000'))
                                   AND @enddatetime THEN 1 ELSE 0 END) AS [Сегодня],
        COUNT(ID) AS [ВСЕГО]
FROM error_select
GROUP BY RateError, CardType, TypeError, CardDigest, Category
ORDER BY RateError DESC, [Сегодня] DESC, [ВСЕГО] DESC;