Дашбоард по ГЕТТ

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
INTO #FilteredTable
FROM [oktell].[dbo].[A_Stat_Connections_1x1]
WHERE (ConnectionType = 5 OR ConnectionType = 1)
    AND (Astr LIKE '%B2B%' OR Bstr LIKE '%B2B%')
    AND (SUBSTRING(Astr, 0, 1) = 's' OR SUBSTRING(Bstr, 0, 1) = 's' OR Astr LIKE '%B2B_s%'OR Bstr LIKE '%B2B_s%')

DECLARE @Id_Users0 TABLE (Id uniqueidentifier, [Name] nvarchar(50))
DECLARE @Id_Users TABLE (Id uniqueidentifier, [Name] nvarchar(50))

--ЗАДАЕМ СООТВЕТСТВИЕ МЕЖДУ ИМЕНАМИ И ID ОПЕРАТОРОВ
INSERT INTO @Id_Users0
SELECT BUserId,Bstr
FROM #FilteredTable
WHERE ConnectionType = 5
UNION
SELECT AUserId, Astr
FROM #FilteredTable
WHERE ConnectionType = 1

INSERT INTO @Id_Users SELECT * FROM @Id_Users0 GROUP BY Id, [Name]

DELETE @Id_Users0
----------------------------------------------------------------

--ФОРМИРУЕМ ВХОДЯЩИЕ ВЫЗОВЫ
DECLARE @InboundCalls TABLE(IdChain uniqueidentifier, UserId uniqueidentifier)

INSERT INTO @InboundCalls
SELECT IdChain, BUserId
  FROM #FilteredTable
  WHERE ConnectionType = 5

DECLARE @InboundMarks TABLE(IdChain uniqueidentifier, UserId uniqueidentifier, Mark int, Iterator int)

INSERT INTO @InboundMarks
SELECT IC.IdChain, IC.UserId, TM.MARK, IIF(TM.MARK IS NOT NULL, 1, 0)
FROM @InboundCalls AS IC
LEFT JOIN [oktell].[dbo].[tbl_MARK] AS TM
ON IC.IdChain = TM.IDHCAIN

DELETE @InboundCalls

DECLARE @InboundFinal TABLE(UserId uniqueidentifier, [Count] int, MiddleMark float, CountMarks int)

INSERT INTO @InboundFinal
SELECT UserId
    ,COUNT(UserId)
    ,ROUND(ISNULL(SUM(Mark), 0) / SUM(Iterator), 2)
    ,SUM(Iterator)
FROM @InboundMarks
GROUP BY UserId

DELETE @InboundMarks
------------------------------------------------------

--ФОРМИРУЕМ ИСХОДЯЩИЕ ВЫЗОВЫ
DECLARE @OutgoingCalls TABLE(IdChain uniqueidentifier, UserId uniqueidentifier)

INSERT INTO @OutgoingCalls
SELECT IdChain, AUserId
  FROM #FilteredTable
  WHERE ConnectionType = 1

DECLARE @OutgoingMarks TABLE(IdChain uniqueidentifier, UserId uniqueidentifier, Mark int, Iterator int)

DROP TABLE #FilteredTable

SELECT *
INTO #OlpTemp
FROM [oktell].[dbo].[Olp_dialog]
WHERE result != -1 AND result IS NOT NULL

INSERT INTO @OutgoingMarks
SELECT OC.IdChain, OC.UserId, OT.result, IIF(OT.result IS NOT NULL, 1, 0)
FROM @OutgoingCalls AS OC
INNER JOIN #OlpTemp AS OT
ON OC.IdChain = OT.IdChain

DROP TABLE #OlpTemp

DELETE @OutgoingCalls
--SELECT * FROM @OutgoingMarks

DECLARE @OutgoingFinal TABLE(UserId uniqueidentifier, [Count] int, MiddleMark float, CountMarks int)

INSERT INTO @OutgoingFinal
SELECT UserId
    ,COUNT(UserId)
    ,ROUND(ISNULL(SUM(Mark), 0) / SUM(Iterator), 2)
    ,SUM(Iterator)
FROM @OutgoingMarks
GROUP BY UserId

DELETE @OutgoingMarks
------------------------------------------------------------------------

DECLARE @FinalTable TABLE([Name] nvarchar(50), CountInbound int, CountOutgoing int, MiddleIn int, MiddleOut int, CountMarks int, PersentMarks nvarchar(20))

INSERT INTO @FinalTable
SELECT IU.Name
    , ISNULL(inf.[Count], 0)
    , ISNULL(outf.[Count], 0)
    , inf.MiddleMark
    , outf.MiddleMark
    , ISNULL(inf.CountMarks, 0) + ISNULL(outf.CountMarks, 0)
    --, (SELECT [dbo].CalculatePersent(ISNULL(inf.CountMarks, 0) + ISNULL(outf.CountMarks, 0), ISNULL(inf.[Count], 0) + ISNULL(outf.[Count], 0)))
    , CONCAT(IIF(ISNULL(inf.[Count], 0) + ISNULL(outf.[Count], 0) != 0, ROUND(CAST(ISNULL(inf.CountMarks, 0) + ISNULL(outf.CountMarks, 0) as float) / CAST(ISNULL(inf.[Count], 0) + ISNULL(outf.[Count], 0) as float), 4), 0) * 100, '%')
FROM @InboundFinal as inf
LEFT JOIN @Id_Users as IU
ON inf.UserId = IU.Id
LEFT JOIN @OutgoingFinal as outf
ON inf.UserId = outf.UserId

DELETE @InboundFinal
DELETE @OutgoingFinal

--INSERT @FinalTable VALUES
--  (

--  )
DECLARE @InboundCount int;
DECLARE @OutgoingCount int;
DECLARE @MiddleMarkIn int;
DECLARE @MiddleMarkOut int;
DECLARE @CountOfMarks int;
DECLARE @Persent nvarchar(20);
DECLARE @CountMarkIn int = (SELECT SUM(IIF(MiddleIn IS NOT NULL, 1, 0)) FROM @FinalTable)
DECLARE @CountMarkOut int = (SELECT SUM(IIF(MiddleOut IS NOT NULL, 1, 0)) FROM @FinalTable)

SET @InboundCount = (SELECT SUM(CountInbound) FROM @FinalTable)
SET @OutgoingCount = (SELECT SUM(CountOutgoing) FROM @FinalTable)
SET @MiddleMarkIn = (SELECT SUM(MiddleIn) / @CountMarkIn FROM @FinalTable)
SET @MiddleMarkOut = (SELECT SUM(MiddleOut) / @CountMarkOut FROM @FinalTable)
SET @CountOfMarks = (SELECT SUM(CountMarks) FROM @FinalTable)
--SET @Persent = (SELECT [dbo].CalculatePersent(ISNULL(@CountOfMarks, 0), ISNULL(@InboundCount, 0) + ISNULL(@OutgoingCount, 0)))
SET @Persent = CONCAT(IIF(ISNULL(@InboundCount, 0) + ISNULL(@OutgoingCount, 0) != 0, ROUND(CAST(ISNULL(@CountOfMarks, 0) as float) / CAST(ISNULL(@InboundCount, 0) + ISNULL(@OutgoingCount, 0) as float), 4), 0) * 100, '%')

INSERT @FinalTable VALUES('Итого', @InboundCount, @OutgoingCount, @MiddleMarkIn, @MiddleMarkOut, @CountOfMarks, @Persent)

SELECT [Name] as 'Name'
    ,CountInbound as 'Inbound Calls'
    ,CountOutgoing  as 'Outgoing Calls'
    ,MiddleIn as 'CSAT Inbound'
    ,MiddleOut as 'CSAT Outgoing'
    ,CountMarks as 'Total Rated'
    ,PersentMarks as '% Rated Calls'
FROM @FinalTable