USE [D10Task]
GO
/****** Object:  UserDefinedFunction [dbo].[tc_ExceptionLogStatistics_DailyTop3] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
Create FUNCTION [dbo].[tc_ExceptionLogStatistics_DailyTop3]
(
    @StartDate datetime = null,
    @EndDate datetime = null
)
RETURNS 
@Res TABLE 
(
    Exception varchar(max),
    ExcCount int
)
AS
BEGIN
    -- Количество первых символов ошибки, по которым идёт сравнение
    DECLARE @SymbolsCount int = 70

    -- Входные данные. Если явно не передано, то берем последние сутки
    if @StartDate is null
        set @StartDate = dateadd(dd, -1, getdate())
    if @EndDate is null
        set @EndDate  = getdate()

    -- Определяем минимальный и максимальный ИД лога
    declare @MinExceptionID int, @MaxExceptionID int

    select  @MinExceptionID = MIN(ID)
    from ExceptionsLog WITH (nolock)
    where Date between @StartDate and @EndDate

    select @MaxExceptionID = MAX(ID) 
    from ExceptionsLog WITH (nolock)
    where Date between @StartDate and @EndDate

    DECLARE @ExceptionsCount table (ExcType varchar(max), ExcCount int)

    INSERT INTO @ExceptionsCount
    SELECT DISTINCT TOP 3 substring(Exception, 0, @SymbolsCount) as ExcType, COUNT(*) as ExcCount
    FROM ExceptionsLog WITH (NOLOCK)
    where ID between @MinExceptionID and @MaxExceptionID
    GROUP BY substring(Exception, 0, @SymbolsCount)
    ORDER BY ExcCount DESC

    INSERT INTO @Res
    SELECT DISTINCT TOP 3 max(el.Exception), MAX(ec.ExcCount)
    FROM ExceptionsLog el WITH (NOLOCK)
    JOIN @ExceptionsCount ec ON substring(el.Exception, 0, @SymbolsCount) = ec.ExcType
    WHERE el.ID between @MinExceptionID and @MaxExceptionID
    GROUP BY substring(el.Exception, 0, @SymbolsCount)
    ORDER BY MAX(ec.ExcCount) DESC

    RETURN 
END