SIS PROP PRINYAt

declare @dt1 datetime = '2019-07-18 00:00:00.000'
declare @dt2 datetime = '2019-07-19 23:59:59.999'

declare @tm1 datetime = '19000101 01:00'
declare @tm2 datetime = '19000101 23:59'


declare @interval int = 12

declare @tasks table ([Id] uniqueidentifier)
    insert into @tasks select  [Id]
    from [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]
    where isoutput = 0

declare @slist int = 2
declare @xline int = 20
declare @operators table (id uniqueidentifier)
INSERT @OPERATORS VALUES('230FCDC7-5DCD-4126-9374-E1EEB4F1F165')

set datefirst 1

declare @mnog float

select @mnog = case @interval
    when 60 then 23.999999999999
    else 23.999999999999  -- по 60 мин
    end
if (@interval is null)
    set @interval = 60

---------------------------------------------------------------------------
DECLARE @Filtered TABLE(idchain uniqueidentifier, callresult int, datetimestart datetime, lenqueue int)
DECLARE @Temp0 TABLE(idchain uniqueidentifier,  suc int, unsuc int, [int] datetime)
DECLARE @Temp TABLE([int] datetime, idchain uniqueidentifier, suc int, unsuc int)

INSERT INTO @Filtered
select  idchain, callresult, datetimestart, lenqueue
    from [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
    where isoutput = 0
        and Datetimestart between @dt1 and @dt2
        and TimeStart  between @tm1 and @tm2
        and IDTask in (select * from @tasks)
        and idchain is not null
        and (IDOperator in (select * from @operators))

INSERT INTO @Temp0
select  [Idchain],
            [suc] = case when ([CallResult] in (5, 8)) then 1 else 0 end,
            [unsuc] = case @slist
                        when 1 then case when not([CallResult] = 5 or [CallResult] = 18) then 1 else 0 end
                        when 2 then case when not ([CallResult] = 5 or [CallResult] = 18 or lenqueue <= @xline) then 1 else 0 end
                        else 0
                      end,
            [int] = case
                when @interval = 1 then cast(floor(cast (datetimestart as float)) as datetime)
                when @interval = 7 then cast(floor(cast (datetimestart as float) - datepart (dw, datetimestart) + 1) as datetime)
                when @interval = 12 then cast (cast(datepart (yy, datetimestart) as nvarchar (4)) + '-' + cast(datepart (mm, datetimestart) as nvarchar (2)) + '-01' as datetime)
                else
                    cast(
                        ( floor( cast(datetimestart as float) * @mnog) / @mnog -
                        floor(cast(datetimestart as float)) )
                        as datetime)
                end

    from
    @Filtered t

INSERT INTO @Temp
select min(int) [int], idchain, sum(suc) suc, sum(unsuc) unsuc
    from
    @Temp0 t
    group by idchain

select  [int] = case @interval
            when 60 then convert(nvarchar(5),[int],108) + '—' + convert(nvarchar(5),dateadd(hh,1,[int]),108)
            when 1 then convert(nvarchar(5),[int],4) + ' ' + cast(datepart(yy,[int]) as nvarchar(4)) -- + ' г.'
            when 7 then convert(nvarchar(5),[int] ,4) + '—' +
                                convert(nvarchar(5), dateadd(dd, 6, [int]) ,4)
            when 12 then case datepart(mm, [int])
                        when 1 then 'Январь '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 2 then 'Февраль '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 3 then 'Март '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 4 then 'Апрель '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 5 then 'Май '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 6 then 'Июнь '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 7 then 'Июль '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 8 then 'Август '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 9 then 'Сентябрь '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 10 then 'Октябрь '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 11 then 'Ноябрь '+ cast(datepart(yy, [int])as nvarchar(4))
                        when 12 then 'Декабрь '+ cast(datepart(yy, [int])as nvarchar(4))
                        end
            else convert(nvarchar(5),[int],108) + '—' + convert(nvarchar(5),dateadd(mi,30,[int]),108)
            end,
        sum (suc+unsuc) [total],
        sum (suc) [served],
        sum (unsuc) [lost]
from
@Temp t
group by t.[int]
order by t.[int]

--DROP TABLE #temp