SHARE
TWEET

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

WorkAkkaunt Jul 22nd, 2019 77 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. declare @tasktable table(id uniqueidentifier)
  2. insert into @tasktable
  3. select [id] from @task
  4.  
  5.  
  6. declare @sqltext nvarchar(max)
  7. --declare @d1 datetime ='2018-09-27 00:55:32.327'
  8. --declare @d2 datetime = '2018-09-28 23:55:32.327'
  9.  
  10. --declare @tasktable table(id uniqueidentifier)
  11. --insert into @tasktable
  12. --select 'E9B14543-37F8-47F5-88F7-387251079253'--[id] from oktell.dbo.A_TaskManager_Tasks where IdProject = 'B5630E46-1943-4E36-BD35-D6A927ADA4F4'-- name like '%push%'
  13.  
  14.  
  15. --declare @sqltext nvarchar(max)
  16. --declare @d1 datetime ='2019-01-01 00:00:00'
  17. --declare @d2 datetime = '2019-02-28 23:59:59'
  18.  
  19. declare @tasks table (id uniqueidentifier, name nvarchar(250))
  20. insert into @tasks select /*top 4  */id, Name from oktell.dbo.A_TaskManager_Tasks with(nolock)
  21. where id in (select id from @tasktable)
  22. declare @tsks table(N int,id uniqueidentifier, name nvarchar(250))
  23. insert into @tsks
  24. select ROW_NUMBER() OVER(Order by id),id, name from @tasks
  25. declare @id table(idtask uniqueidentifier,tabl nvarchar(100),i nvarchar(10), name nvarchar(250), newContact int)
  26. declare @tsk uniqueidentifier
  27. declare @name nvarchar(250)
  28. declare @listid nvarchar(100)
  29. declare @i int =1
  30. --select * from @tsks
  31. while (@i<=(select count(*) from @tasks))
  32. begin
  33. set @tsk=(select id from @tsks where N=@i)
  34. set @name=(select name from @tsks where N=@i)
  35. set  @listid= (select joinedtable from oktell.dbo.A_TaskManager_Lists
  36.  with(nolock)
  37. join oktell.dbo.A_TaskManager_Tasks on
  38. oktell.dbo.A_TaskManager_Tasks.ListId=oktell.dbo.A_TaskManager_Lists.id
  39. where oktell.dbo.A_TaskManager_Tasks.id=@tsk)
  40. --where ID=(select listid from oktell.dbo.A_TaskManager_Tasks with(nolock) where id=@tsk))
  41.  
  42. if(isnull(@listid,'')!='' /*AND OBJECT_ID(@listid, 'U') IS NOT NULL*/ )
  43. begin
  44. --print @listid
  45. declare @counter int=0
  46. set @sqltext ='select '+cast(@i as nvarchar(10))+',count(*), '''+@name+''' from '+@listid+' where phone is not null'
  47. insert into @id(i,tabl, name)
  48. exec(@sqltext)
  49.  
  50. declare @ta table (id int, idtask uniqueidentifier)
  51.  
  52. set @sqltext ='select id, '+''''+cast(@tsk as nvarchar(50))+''''+ 'from '+@listid+' where phone is not null'
  53. insert into @ta(id,idtask)
  54. exec(@sqltext)
  55.  
  56. update @id
  57. set idtask=(select @tsk)
  58. where i=@i
  59.  
  60.   print @listid
  61. declare @columnid nvarchar(250)
  62. set @columnid=
  63. (select ColumnId from oktell.dbo.A_TaskManager_Lists
  64.  with(nolock)
  65. join oktell.dbo.A_TaskManager_Tasks on
  66. oktell.dbo.A_TaskManager_Tasks.ListId=oktell.dbo.A_TaskManager_Lists.id
  67. where oktell.dbo.A_TaskManager_Tasks.id=@tsk)
  68.   print @tsk
  69. set @sqltext='SELECT  @counter=count(*)  FROM '+@listid+' t1
  70.  left join   oktell.dbo.A_TaskManager_SubTaskNumber t2 on t1.['+@columnid+']=t2.IdInList and cast('''+cast(@tsk as nvarchar(250))+'''
  71. as uniqueidentifier)=t2.TaskId
  72. left join oktell.dbo.A_TaskManager_SubTasks as s on t2.IdInList=s.IdInList and t2.TaskId=s.taskid
  73.  where
  74.   (t2.NResult=0 or t2.NResult  is null)
  75.   and
  76.   ((t2.CountBusy=0 and t2.CountNotRespond=0 and t2.CountQHangOff=0 ) or t2.CountBusy is null)
  77.     and s.NextEffortTime is  null and phone is not null '
  78.    --and (isnull(t2.nextefforttime, getdate())) between '''+CONVERT(nvarchar(20), @d1, 120)+''' and '''+CONVERT(nvarchar(20), @d2, 120)+''''
  79.    
  80.    --print @sqltext
  81. exec sp_executesql @sqltext, N'@counter int out', @counter out
  82.  
  83. update @id
  84. set newContact=@counter
  85. where i=@i
  86. end
  87. set @i=@i+1;
  88. end
  89.  
  90. select idtask,name, tabl[Всего контактов]
  91. ,newContact as [Новые]
  92. /*,(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)
  93.     and (NResult=0 or NResult is null))[Новые]*/
  94. ,(select count(*) from [yandextaxi].[dbo].[tbl_processing]  as a with(nolock)
  95.      
  96.    
  97.     where
  98.    
  99.    
  100.     main.idtask=a.idtask /*and isnull(DateTimeStart,getdate()) between @d1 and @d2*/)[В обработке]
  101.  
  102. ,(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)   
  103.     inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
  104.     where TaskId=main.idtask
  105.     group by idinlist, TaskId) as a
  106.     inner join @ta ta on a.idinlist = ta.id
  107.     left join oktell.dbo.A_TaskManager_SubTasks as s on a.IdInList=s.IdInList and a.TaskId=s.taskid
  108.     where
  109.         a.TaskId=main.idtask
  110.         and s.TaskId=main.idtask
  111.         and ta.idtask =main.idtask
  112.         and a.NResult=0
  113.         and retryNumbers is   null)
  114. +
  115. (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)
  116.     inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
  117.     where TaskId=main.idtask
  118.     group by idinlist, TaskId) as a    
  119.     inner join @ta ta on a.idinlist = ta.id
  120.     left join oktell.dbo.A_TaskManager_SubTasks as s on a.IdInList=s.IdInList and a.TaskId=s.taskid
  121.     where
  122.         a.TaskId=main.idtask
  123.         and s.TaskId=main.idtask
  124.         and ta.idtask =main.idtask
  125.         and a.NResult =0
  126.         and retryNumbers is not  null) [Перезвоны всего]
  127.  
  128. ,(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)   
  129.     inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
  130.     where TaskId=main.idtask
  131.     group by idinlist, TaskId) as a
  132.     inner join @ta ta on a.idinlist = ta.id
  133.     inner join oktell.dbo.A_TaskManager_SubTasks as s on a.IdInList=s.IdInList and a.TaskId=s.taskid
  134.     where
  135.     a.TaskId=main.idtask
  136.     and s.TaskId=main.idtask
  137.     and ta.idtask =main.idtask
  138.     and a.NResult=0
  139.     and retryNumbers is   null) [Неназначенные перезвоны]
  140.  
  141. ,(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)   
  142.     inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
  143.     where TaskId=main.idtask
  144.     group by idinlist, TaskId) as a    
  145.     inner join @ta ta on a.idinlist = ta.id
  146.     left join oktell.dbo.A_TaskManager_SubTasks as s on a.IdInList=s.IdInList and a.TaskId=s.taskid
  147.     where
  148.      a.TaskId=main.idtask
  149.     and s.TaskId=main.idtask
  150.     and ta.idtask =main.idtask
  151.     and a.NResult =0
  152.     and retryNumbers is not  null) [Назначенные перезвоны]
  153.  
  154. ,isnull((select count(distinct IdChain)  from
  155.      oktell_cc_temp.dbo.A_Cube_CC_EffortConnections with(nolock)
  156.      where idtask=main.idtask
  157.     and
  158.     CallResult in(5,8,18)
  159.     and isnull(DateTimeStart,getdate()) between @d1 and @d2),0)
  160.     -isnull((SELECT count(distinct idchain)
  161.           FROM [yandextaxi].[dbo].[tbl_result]
  162.           where
  163.           isnull(datewrite,getdate()) between @d1 and @d2 and taskid=main.idtask
  164.           and
  165.           categoryid in (
  166.                     SELECT [id]
  167.                     FROM [yandextaxi].[dbo].[tbl_final_category_view] with(nolock)
  168.                     where name like '%автоответчик%'
  169.                     )),0)
  170.         [Дозвон]
  171. ,isnull((SELECT count(distinct idchain)
  172.           FROM [yandextaxi].[dbo].[tbl_result]
  173.           where
  174.           isnull(datewrite,getdate()) between @d1 and @d2 and taskid=main.idtask
  175.           and
  176.           categoryid in (
  177.                     SELECT [id]
  178.                     FROM [yandextaxi].[dbo].[tbl_final_category_view] with(nolock)
  179.                     where name like '%автоответчик%'
  180.                     )),0) ["Автоответчик"]
  181. ,(select count(distinct a.idchain) from yandextaxi.dbo.tbl_result as a with(nolock)
  182.     join oktell_cc_temp.dbo.A_Cube_CC_EffortConnections as c on a.idchain=c.IdChain and a.TaskId=c.idtask
  183.     where
  184.     TaskId=main.idtask
  185.     and
  186.     c.CallResult=5 /*and c.IdConn is not null*/ and  (isnull(categoryid,'-1')!='-1' or isnull([subcategoryid],'-1')!='-1' )
  187.     and isnull(DateTimeStart,getdate()) between @d1 and @d2)[Опрос состоялся]
  188.  
  189.     ,(select count(distinct idinlist) from (
  190. select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock) 
  191.     inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
  192.     where TaskId=main.idtask
  193.     and ta.idtask =main.idtask
  194.     group by idinlist, TaskId) t where t.NResult=1) [Выведен из обзвона]
  195. --,(select count(distinct a.IdInList) from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
  196. --  inner join @ta ta on a.idinlist = ta.id
  197. --  where TaskId=main.idtask and NResult=1) [Выведен из обзвона]
  198.  
  199. ,(select count(distinct idinlist) from (
  200. select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock) 
  201.     inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
  202.     where TaskId=main.idtask
  203.     and ta.idtask =main.idtask
  204.     group by idinlist, TaskId) t where t.NResult=2) [Выведен по исчерпанию попыток]
  205. --,(select count(distinct a.IdInList) from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
  206. --  inner join @ta ta on a.idinlist = ta.id
  207. --  where TaskId=main.idtask and NResult=2) [Выведен по исчерпанию попыток]
  208.  
  209. ,(select count(distinct idinlist) from (
  210. select distinct IdInList,TaskId, min(nresult) NResult,max(NextEffortTime) NextEffortTime from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock) 
  211.     inner join @ta ta on a.idinlist = ta.id and ta.idtask =main.idtask
  212.     where TaskId=main.idtask
  213.     and ta.idtask =main.idtask
  214.     group by idinlist, TaskId) t where t.NResult=3) [Удалено]
  215. --,(select count(distinct a.IdInList) from oktell.dbo.A_TaskManager_SubTaskNumber as a with(nolock)
  216. --  inner join @ta ta on a.idinlist = ta.id
  217. --  where TaskId=main.idtask and NResult=3) [Удалено]
  218.  
  219. ,(select count(*) from [yandextaxi].[dbo].[tbl_processing]  as a with(nolock)
  220.     where
  221.     main.idtask=a.idtask /*and isnull(DateTimeStart,getdate()) between @d1 and @d2*/)
  222. +
  223. newContact [Бэклог]
  224. from @id as main
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top