Отчет по операторам

/****** Script for SelectTopNRows command from SSMS  ******/
DECLARE @OPERATOR uniqueidentifier
DECLARE @TIME_START datetime
DECLARE @TIME_FINISH datetime
DECLARE @DAY datetime

SET @OPERATOR = '5EC13849-09B6-41DB-B0DA-9E36ACE5E461'
SET @TIME_START = '1900-01-01 08:00:00.000'
SET @TIME_FINISH = '1900-01-01 18:00:00.000'
SET @DAY = '2018-04-06 0:00.000'

DECLARE @SECONDS_IN_MINUTE int
SET @SECONDS_IN_MINUTE = 60

DECLARE @Time_In_Ready int
DECLARE @Time_In_Break int
DECLARE @Time_In_Conversation int

DECLARE @AllPhoneCalls TABLE([Name] nvarchar(100), DateStart date, TimeStart time, TimeFinish time, LenTime float, [State] int)

--Сохраняем отфильтрованные звонки в переменную
INSERT INTO @AllPhoneCalls
SELECT
    [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo].Name
    ,[DateStart]
    ,CAST([DateTimeStart] AS time) [TimeStart]
    ,CAST([DateTimeStop] AS time) [TimeStop]
    ,[LenTime]
    ,[State]
FROM [oktell_cc_temp].[dbo].[Operator_States_Test]
INNER JOIN
  [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo]
  ON [oktell_cc_temp].[dbo].[Operator_States_Test].IdOperator = [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_OperatorInfo].ID
  WHERE [oktell_cc_temp].[dbo].[Operator_States_Test].DateStart = @DAY
        AND CAST([oktell_cc_temp].[dbo].[Operator_States_Test].DateTimeStop AS date) = CAST(@DAY AS date)
        AND CAST([DateTimeStart] AS time) >= CAST(@TIME_START AS time)
        AND CAST([DateTimeStop] AS time) <= CAST(@TIME_FINISH AS time)
        AND [oktell_cc_temp].[dbo].[Operator_States_Test].IdOperator = @OPERATOR

SELECT
    @Time_In_Ready = ROUND(SUM(IIF([State] = 10, LenTime, 0)) / @SECONDS_IN_MINUTE, 0)
    , @Time_In_Break = ROUND(SUM(IIF([State] = 9, LenTime, 0)) / @SECONDS_IN_MINUTE, 0)
    , @Time_In_Conversation = ROUND(SUM(IIF([State] = 4 OR [State] = 5 OR [State] = 6 OR [State] = 8, LenTime, 0)) / @SECONDS_IN_MINUTE, 0)
FROM @AllPhoneCalls

SELECT [Name]
    ,DateStart
    ,@Time_In_Ready AS 'Time in ready'
    ,@Time_In_Break AS 'Time in break'
    ,@Time_In_Conversation AS 'Time in conversation'
    ,CONCAT(CAST (ROUND(@Time_In_Conversation / CAST(@Time_In_Ready AS decimal), 2) * 100 AS int), '%')
        AS 'Time in conversation/Time in ready %'
    ,CONCAT(CAST(ROUND((@Time_In_Conversation + CAST(@Time_In_Ready AS decimal)) / (@Time_In_Ready + @Time_In_Break), 2) * 100 as int), '%')
        AS 'Time in conversation + Time in ready / Time in ready + Time in break %'
FROM @AllPhoneCalls
GROUP BY [Name], DateStart