09 Oktell

if (@interval is null)
    set @interval = 2

if not (@interval in (1, 2, 3))
    set @interval = 2

declare @intMin int

select @intMin = case @interval
    when 1 then 15
    when 2 then 30
    when 3 then 60
end


create table #taskstmp([id] uniqueidentifier)

insert into #taskstmp
    select cast(id as uniqueidentifier) from @tasks
if ( @@rowcount = 0 )
    insert into #taskstmp
    SELECT  [Id]
    FROM [oktell_cc_temp].[dbo].[A_Cube_CC_Cat_Task]
    Where Isoutput = 0

select case @interval
            when 1 then convert(nvarchar(5),[time],108) + ' - ' + convert(nvarchar(5),dateadd(mi,15,[time]),108)
            when 2 then convert(nvarchar(5),[time],108) + ' - ' + convert(nvarchar(5),dateadd(mi,30,[time]),108)
            when 3 then convert(nvarchar(5),[time],108) + ' - ' + convert(nvarchar(5),dateadd(hh,1,[time]),108)
        end time,
    total, suc, failed,
    round(suc*100.0e0/total, 2) prc, round(failed*100.0e0/total, 2) lcr, servdx,
    round(case @slist
        when 1 then case when suc=0 then 0 else servdx*100.0e0/suc end
        when 2 then case when total=0 then 0 else servdx*100.0e0/total end
        when 3 then case when suc+lostx=0 then 0 else servdx*100.0e0/(suc+lostx) end
        when 4 then case when total=0 then 0 else slx*100.0e0/total end
    end, 2) slx
from(
select [time], count(*) total, sum(suc) suc, sum(unsuc) failed,
sum(case when suc=1 and lenQueue<@xline then 1 else 0 end) servdx,
sum(case when lenQueue<@xline then 1 else 0 end) slx,
sum(case when unsuc=1 and lenqueue>@xline then 1 else 0 end) lostx
from(
    select min([time]) [time], max(suc) suc, min(unsuc) unsuc, max(LenQueue) LenQueue
    from(
        select IdChain,
        case when (CallResult = 5 or CallResult = 18) then 1 else 0 end suc,
        case when not (CallResult = 5 or CallResult = 18) then 1 else 0 end unsuc,
        case when (CallResult = 5 or CallResult = 18) and LenQueue<@xline then 1 else 0 end servdx,
        case when LenQueue<@xline then 1 else 0 end slx,
        case when LenQueue>@xline and CallResult != 5 and CallResult != 18 then 1 else 0 end lostx,
        LenQueue, [time]
        from(
            select  idchain,
            callresult,
            lenqueue,
            [time] = DATEADD(mi, DATEDIFF(mi, '19000101', TimeStart)/@intMin*@intMin, '19000101')
            from [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]
            where DateTimeStart between @dt1 and @dt2
                and IdChain is not null
                and IdTask in (select id from #taskstmp)
                and IsOutput = 0
        )t
    )t
    group by IdChain)t
group by time)t
order by t.time

drop table #taskstmp