Отделы по интервалам цмд дристня

DECLARE @dt1 DATETIME =  '07-18-2019 00:00:00.001'
DECLARE @dt2 DATETIME =  '07-19-2019 23:59:59.990'
declare @interval int = 2
declare @gr int = 0
-------------------------------------------------------------------------------------------------------------------------------------------------------------

declare @gr2 uniqueidentifier
select @gr2 = case @gr
    when 0 then '38249813-A25B-4DF5-9818-015F485AE480'
    when 1 then '1BB684F3-40AC-4685-9C35-20A7CAF2E205'
    when 2 then 'DEA0C168-129D-4F55-BD28-18FD69F90058'
    when 3 then '218F7056-F250-4C38-878E-65A9072A152E'
    when 4 then '60439EA5-25B8-44BB-A24F-B12AAC46362C'
    when 5 then 'C95E4E86-F887-4E1E-B644-BB479BBE8CD2'
    when 6 then '59F2FDA7-8CEE-4D74-A21A-DAA899678383'
end

declare @intMin int
select @intMin = case @interval
    when 1 then 60
    when 2 then 1440
    when 3 then 15
    when 4 then 30
end

declare @group table (id uniqueidentifier)

insert into @group
select ID from [oktell_settings].[dbo].[A_Users]
where ParentGroupID = @gr2

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------ПРИНЯТО------------
----------------------------

DECLARE @ConnectionIdChains TABLE (IdChain UNIQUEIDENTIFIER, BUserId UNIQUEIDENTIFIER, Name NVARCHAR(250), lenq int, lentime int, timestart datetime, GroupId uniqueidentifier)

INSERT INTO @ConnectionIdChains (IdChain, BUserId, Name, lenq, lentime, timestart, [A_Groups].GroupId)

SELECT IdChain, [BUserId], AU.[Name], datediff(SS, TimeStart, TimeAnswer) , datediff(ss, TimeAnswer, TimeStop) , TimeStart, [A_Groups].ID
FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS CON3 WITH (NOLOCK)
LEFT JOIN [oktell].[dbo].[A_Users] AS AU
ON CON3.[BUserId] = AU.ID
LEFT JOIN [oktell].[dbo].[A_Groups] AS [A_Groups]
ON AU.ParentGroupID = [A_Groups].ID
WHERE BUserId IN (SELECT [Id] FROM @group)
        AND TimeStart BETWEEN @dt1 AND @dt2
        AND ([ConnectionType] = 5)
GROUP BY IdChain, [BUserId], AU.[Name], datediff(ss, TimeStart, TimeAnswer) , datediff(ss, TimeAnswer, TimeStop), TimeStart, [A_Groups].ID

--select * from @ConnectionIdChains
----------------------------------------------------------------------------------------
---------ПРОПУЩЕННО------------
-------------------------------
DECLARE @MissedIdChains TABLE (IdChain UNIQUEIDENTIFIER, BUserId UNIQUEIDENTIFIER, Name NVARCHAR(250), timestart datetime)

INSERT INTO @MissedIdChains

SELECT [MissedCalls].IdChain
    , NULL, NULL
    --,US.ID
    --,US.[Name]
    ,(SELECT TOP 1 TimeStart FROM [oktell].[dbo].[A_Stat_Connections_1x1] AS sc1 WITH (NOLOCK) where sc1.IdChain=[MissedCalls].IdChain ORDER BY TimeStart)
FROM [oktell].[dbo].[A_Stat_MissedCalls] AS [MissedCalls] WITH (NOLOCK)
LEFT JOIN [oktell].[dbo].[A_Users] AS US
    ON [MissedCalls].[BUserId] = US.ID
LEFT JOIN [oktell].[dbo].[A_Groups] AS [A_Groups]
    ON US.ParentGroupID = [A_Groups].ID
LEFT JOIN @ConnectionIdChains AS Accepted
    ON ([MissedCalls].[IdChain] = Accepted.IdChain AND [A_Groups].ID = Accepted.GroupId)
WHERE [MissedCalls].BUserId IN (SELECT [Id] FROM @group)
        AND [MissedCalls].TimeStart BETWEEN @dt1 AND @dt2
        AND [A_Groups].ID IS NOT NULL
        AND Accepted.IdChain IS NULL
GROUP BY [MissedCalls].IdChain--, [MissedCalls].[BUserId], US.[Name]

------------------------------------------------------------------------------------------------

DECLARE @Result TABLE (IdChain UNIQUEIDENTIFIER, BUserId UNIQUEIDENTIFIER, Name NVARCHAR(250), FlagAccepting INT, FlagMissed INT, lenq int, lentime int, timestart datetime)

INSERT INTO @Result (IdChain, BUserId, Name, FlagAccepting, FlagMissed, timestart)
SELECT IdChain, BUserId, Name, '0', '1', timestart
FROM @MissedIdChains

INSERT INTO @Result (IdChain, BUserId, Name, FlagAccepting, FlagMissed, lenq, lentime, timestart)
SELECT IdChain, BUserId, Name, '1', '0', lenq, lentime, timestart
FROM @ConnectionIdChains

--select * from @Result


select case @interval
            when 1 then convert(nvarchar(5),[time],108) + ' - ' + convert(nvarchar(5),dateadd(hh,1,[time]),108)
            when 2 then convert(nvarchar(5),[time],4) + ' ' + cast(datepart(yy,[time]) as nvarchar(4)) -- + ' г.'
            when 3 then convert(nvarchar(5),[time],108) + ' - ' + convert(nvarchar(5),dateadd(mi,15,[time]),108)
            when 4 then convert(nvarchar(5),[time],108) + ' - ' + convert(nvarchar(5),dateadd(mi,30,[time]),108)
        end time
        ,count(*) [Всего]
        , sum(FlagAccepting) [Принято]
        , sum(FlagMissed) [Пропущено]
        , oktell.dbo.GetTimeFromSecond( round(avg(lenq),0)) [Ср. время ож.]
        , oktell.dbo.GetTimeFromSecond( round(avg(lentime),0))  [Ср. дл. (успешных)] from (
        select *, case when @interval in (1,3,4) then DATEADD(mi, DATEDIFF(mi, '19000101', TimeStart)/@intMin*@intMin, '19000101')
            when @interval = 2 then cast(floor(cast (timestart as float)) as datetime)
             end time from @Result) t
group by time