NEW Онлайн статистика v.2 COPY UBER

declare @tasktable table(id uniqueidentifier)
insert into @tasktable
select [id] from @task


declare @sqltext nvarchar(max)
--declare @d1 datetime ='2018-09-27 00:55:32.327'
--declare @d2 datetime = '2018-09-28 23:55:32.327'

--declare @tasktable table(id uniqueidentifier)
--insert into @tasktable
--select 'E9B14543-37F8-47F5-88F7-387251079253'--[id] from oktell.dbo.A_TaskManager_Tasks where IdProject = 'B5630E46-1943-4E36-BD35-D6A927ADA4F4'-- name like '%push%'


--declare @sqltext nvarchar(max)
--declare @d1 datetime ='2019-01-01 00:00:00'
--declare @d2 datetime = '2019-02-28 23:59:59'

declare @tasks table (id uniqueidentifier, name nvarchar(250))
insert into @tasks select /*top 4  */id, Name from oktell.dbo.A_TaskManager_Tasks with(nolock)
where id in (select id from @tasktable)
declare @tsks table(N int,id uniqueidentifier, name nvarchar(250))
insert into @tsks
select ROW_NUMBER() OVER(Order by id),id, name from @tasks
declare @id table(idtask uniqueidentifier,tabl nvarchar(100),i nvarchar(10), name nvarchar(250), newContact int)
declare @tsk uniqueidentifier
declare @name nvarchar(250)
declare @listid nvarchar(100)
declare @i int =1
--select * from @tsks
while (@i<=(select count(*) from @tasks))
begin
set @tsk=(select id from @tsks where N=@i)
set @name=(select name from @tsks where N=@i)
set  @listid= (select joinedtable from oktell.dbo.A_TaskManager_Lists
 with(nolock)
join oktell.dbo.A_TaskManager_Tasks on
oktell.dbo.A_TaskManager_Tasks.ListId=oktell.dbo.A_TaskManager_Lists.id
where oktell.dbo.A_TaskManager_Tasks.id=@tsk)
--where ID=(select listid from oktell.dbo.A_TaskManager_Tasks with(nolock) where id=@tsk))

if(isnull(@listid,'')!='' /*AND OBJECT_ID(@listid, 'U') IS NOT NULL*/ )
begin
--print @listid
declare @counter int=0
set @sqltext ='select '+cast(@i as nvarchar(10))+',count(*), '''+@name+''' from '+@listid+' where phone is not null'
insert into @id(i,tabl, name)
exec(@sqltext)

declare @ta table (id int, idtask uniqueidentifier)

set @sqltext ='select id, '+''''+cast(@tsk as nvarchar(50))+''''+ 'from '+@listid+' where phone is not null'
insert into @ta(id,idtask)
exec(@sqltext)

update @id
set idtask=(select @tsk)
where i=@i

  print @listid
declare @columnid nvarchar(250)
set @columnid=
(select ColumnId from oktell.dbo.A_TaskManager_Lists
 with(nolock)
join oktell.dbo.A_TaskManager_Tasks on
oktell.dbo.A_TaskManager_Tasks.ListId=oktell.dbo.A_TaskManager_Lists.id
where oktell.dbo.A_TaskManager_Tasks.id=@tsk)
  print @tsk
set @sqltext='SELECT  @counter=count(*)  FROM '+@listid+' t1
  left join   oktell.dbo.A_TaskManager_SubTaskNumber t2 on t1.['+@columnid+']=t2.IdInList and cast('''+cast(@tsk as nvarchar(250))+'''
as uniqueidentifier)=t2.TaskId
left join oktell.dbo.A_TaskManager_SubTasks as s on t2.IdInList=s.IdInList and t2.TaskId=s.taskid
  where
   (t2.NResult=0 or t2.NResult  is null)
   and
   ((t2.CountBusy=0 and t2.CountNotRespond=0 and t2.CountQHangOff=0 ) or t2.CountBusy is null)
    and s.NextEffortTime is  null and phone is not null '
   --and (isnull(t2.nextefforttime, getdate())) between '''+CONVERT(nvarchar(20), @d1, 120)+''' and '''+CONVERT(nvarchar(20), @d2, 120)+''''

   --print @sqltext
exec sp_executesql @sqltext, N'@counter int out', @counter out

update @id
set newContact=@counter
where i=@i
end
set @i=@i+1;
end

select idtask,name, tabl[Всего контактов]
,newContact as [Новые]
/*,(select count(distinct IdInList ) from oktell.dbo.A_TaskManager_SubTaskNumber with(nolock) where TaskId=idtask and ((CountBusy=0 and CountNotRespond=0 and CountQHangOff=0 ) or CountBusy is null)
    and (NResult=0 or NResult is null))[Новые]*/
,(select count(*) from [yandextaxi].[dbo].[tbl_processing]  as a with(nolock)


    where


    main.idtask=a.idtask /*and isnull(DateTimeStart,getdate()) between @d1 and @d2*/)[В обработке]

,(select count(distinct a.IdInList) from (select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
    inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
    where TaskId=main.idtask
    group by idinlist, TaskId) as a
    inner join @ta ta on a.idinlist = ta.id
    left join oktell.dbo.A_TaskManager_SubTasks as s on a.IdInList=s.IdInList and a.TaskId=s.taskid
    where
        a.TaskId=main.idtask
        and s.TaskId=main.idtask
        and ta.idtask =main.idtask
        and a.NResult=0
        and retryNumbers is   null)
+
(select count(distinct a.IdInList) from (select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
    inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
    where TaskId=main.idtask
    group by idinlist, TaskId) as a
    inner join @ta ta on a.idinlist = ta.id
    left join oktell.dbo.A_TaskManager_SubTasks as s on a.IdInList=s.IdInList and a.TaskId=s.taskid
    where
        a.TaskId=main.idtask
        and s.TaskId=main.idtask
        and ta.idtask =main.idtask
        and a.NResult =0
        and retryNumbers is not  null) [Перезвоны всего]

,(select count(distinct a.IdInList) from (select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
    inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
    where TaskId=main.idtask
    group by idinlist, TaskId) as a
    inner join @ta ta on a.idinlist = ta.id
    inner join oktell.dbo.A_TaskManager_SubTasks as s on a.IdInList=s.IdInList and a.TaskId=s.taskid
    where
    a.TaskId=main.idtask
    and s.TaskId=main.idtask
    and ta.idtask =main.idtask
    and a.NResult=0
    and retryNumbers is   null) [Неназначенные перезвоны]

,(select count(distinct a.IdInList) from (select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
    inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
    where TaskId=main.idtask
    group by idinlist, TaskId) as a
    inner join @ta ta on a.idinlist = ta.id
    left join oktell.dbo.A_TaskManager_SubTasks as s on a.IdInList=s.IdInList and a.TaskId=s.taskid
    where
     a.TaskId=main.idtask
    and s.TaskId=main.idtask
    and ta.idtask =main.idtask
    and a.NResult =0
    and retryNumbers is not  null) [Назначенные перезвоны]

,isnull((select count(distinct IdChain)  from
     oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock)
     where idtask=main.idtask
    and
    CallResult in(5,8,18)
    and isnull(DateTimeStart,getdate()) between @d1 and @d2),0)
    -isnull((SELECT count(distinct idchain)
          FROM [yandextaxi].[dbo].[tbl_result]
          where
          isnull(datewrite,getdate()) between @d1 and @d2 and taskid=main.idtask
          and
          categoryid in (
                    SELECT [id]
                    FROM [yandextaxi].[dbo].[tbl_final_category_view] with(nolock)
                    where name like '%автоответчик%'
                    )),0)
        [Дозвон]
,isnull((SELECT count(distinct idchain)
          FROM [yandextaxi].[dbo].[tbl_result]
          where
          isnull(datewrite,getdate()) between @d1 and @d2 and taskid=main.idtask
          and
          categoryid in (
                    SELECT [id]
                    FROM [yandextaxi].[dbo].[tbl_final_category_view] with(nolock)
                    where name like '%автоответчик%'
                    )),0) ["Автоответчик"]
,(select count(distinct a.idchain) from yandextaxi.dbo.tbl_result as a with(nolock)
    join oktell_cc_temp.dbo.A_Cube_CC_EffortConnections as c on a.idchain=c.IdChain and a.TaskId=c.idtask
    where
    TaskId=main.idtask
    and
    c.CallResult=5 /*and c.IdConn is not null*/ and  (isnull(categoryid,'-1')!='-1' or isnull([subcategoryid],'-1')!='-1' )
    and isnull(DateTimeStart,getdate()) between @d1 and @d2)[Опрос состоялся]

    ,(select count(distinct idinlist) from (
select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
    inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
    where TaskId=main.idtask
    and ta.idtask =main.idtask
    group by idinlist, TaskId) t where t.NResult=1) [Выведен из обзвона]
--,(select count(distinct a.IdInList) from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
--  inner join @ta ta on a.idinlist = ta.id
--  where TaskId=main.idtask and NResult=1) [Выведен из обзвона]

,(select count(distinct idinlist) from (
select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
    inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
    where TaskId=main.idtask
    and ta.idtask =main.idtask
    group by idinlist, TaskId) t where t.NResult=2) [Выведен по исчерпанию попыток]
--,(select count(distinct a.IdInList) from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
--  inner join @ta ta on a.idinlist = ta.id
--  where TaskId=main.idtask and NResult=2) [Выведен по исчерпанию попыток]

,(select count(distinct idinlist) from (
select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
    inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
    where TaskId=main.idtask
    and ta.idtask =main.idtask
    group by idinlist, TaskId) t where t.NResult=3) [Удалено]
--,(select count(distinct a.IdInList) from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
--  inner join @ta ta on a.idinlist = ta.id
--  where TaskId=main.idtask and NResult=3) [Удалено]

,(select count(*) from [yandextaxi].[dbo].[tbl_processing]  as a with(nolock)
    where
    main.idtask=a.idtask /*and isnull(DateTimeStart,getdate()) between @d1 and @d2*/)
+
newContact [Бэклог]
from @id as main