USE [D10Task] GO /****** Object: UserDefinedFunction [dbo].[tc_EmailJobSessionLogStatistics_DailyTop3] Script Date: 12.03.2020 19:34:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= CREATE FUNCTION [dbo].[tc_EmailJobSessionLogStatistics_DailyTop3] ( @StartDate datetime = null, @EndDate datetime = null ) RETURNS @Res TABLE ( Log varchar(max), logCount int ) AS BEGIN -- Количество первых символов ошибки, по которым идёт сравнение DECLARE @SymbolsCount int = 100 -- Входные данны. Если явно не передано, то берем последние сутки if @StartDate is null set @StartDate = dateadd(dd, -1, getdate()) if @EndDate is null set @EndDate = getdate() -- Определяем минимальный и максимальный ИД лога declare @MinSessionId int, @MaxSessionId int select @MinSessionId = MIN(SessionId) from EmailJobSessionLog WITH (nolock) where DateStart between @StartDate and @EndDate select @MaxSessionId = MAX(SessionId) from EmailJobSessionLog WITH (nolock) where DateStart between @StartDate and @EndDate DECLARE @EmailJobSessionLogCount table (LogType varchar(max), logCount int) INSERT INTO @EmailJobSessionLogCount SELECT DISTINCT TOP 3 substring(case when CHARINDEX('mailbox:',log) <> 0 then SUBSTRING(log, CHARINDEX('mailbox:',log) , len(log)) else SUBSTRING(log, CHARINDEX('mailboxId:',log) , len(log)) end, 0, @SymbolsCount) as LogType, COUNT(*) as LogCount FROM EmailJobSessionLog WITH (NOLOCK) where SessionId between @MinSessionId and @MaxSessionId and ItemsFailed!=0 GROUP BY substring(case when CHARINDEX('mailbox:',log) <> 0 then SUBSTRING(log, CHARINDEX('mailbox:',log) , len(log)) else SUBSTRING(log, CHARINDEX('mailboxId:',log) , len(log)) end, 0, @SymbolsCount) ORDER BY LogCount DESC INSERT INTO @Res SELECT DISTINCT TOP 3 max(case when CHARINDEX('mailbox:',el.log) <> 0 then SUBSTRING(el.log, CHARINDEX('mailbox:',el.log) , len(el.log)) else SUBSTRING(el.log, CHARINDEX('mailboxId:',el.log) , len(el.log)) end), MAX(ec.logCount) FROM EmailJobSessionLog el WITH (NOLOCK) JOIN @EmailJobSessionLogCount ec ON substring(case when CHARINDEX('mailbox:',el.log) <> 0 then SUBSTRING(el.log, CHARINDEX('mailbox:',el.log) , len(el.log)) else SUBSTRING(el.log, CHARINDEX('mailboxId:',el.log) , len(el.log)) end, 0, @SymbolsCount) = ec.LogType WHERE el.SessionId between @MinSessionId and @MaxSessionId GROUP BY substring(case when CHARINDEX('mailbox:',el.log) <> 0 then SUBSTRING(el.log, CHARINDEX('mailbox:',el.log) , len(el.log)) else SUBSTRING(el.log, CHARINDEX('mailboxId:',el.log) , len(el.log)) end, 0, @SymbolsCount) ORDER BY MAX(ec.LogCount) DESC RETURN END