Advertisement
WorkAkkaunt

UBER оптимизация

Aug 5th, 2019
252
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 2.04 KB | None | 0 0
  1. DECLARE @DATE_START datetime = '20190729 23:59'
  2. DECLARE @DATE_END datetime = '20190731 23:59'
  3.  
  4. DECLARE @TASKS TABLE(Id uniqueidentifier)
  5.  
  6. INSERT INTO @TASKS
  7. SELECT Id
  8. FROM [oktell].[dbo].[A_TaskManager_Tasks];
  9. --------------------------------------------------------------------------------------------------------------------
  10. DECLARE @TasksEnumerator CURSOR
  11.  
  12. SET @TasksEnumerator = CURSOR SCROLL FOR
  13. SELECT TMT.Id, TMT.Name, JoinedTable
  14. FROM [oktell].[dbo].[A_TaskManager_Lists] as TML
  15. JOIN [oktell].[dbo].[A_TaskManager_Tasks] as TMT
  16.     ON  TMT.ListId = TML.Id
  17. WHERE TMT.Id IN (SELECT * FROM @TASKS)
  18.  
  19. ------ОТКРЫВАЕМ КУРСОР-----------------------------------------------------------------------------------------------
  20. DECLARE @sqltext nvarchar(max)
  21.  
  22. DECLARE @TaskId uniqueidentifier
  23. DECLARE @Name nvarchar(100)
  24. DECLARE @JTable nvarchar(100)
  25. DECLARE @CountAll int
  26.  
  27. DECLARE @IdTable TABLE(TaskId uniqueidentifier, Name nvarchar(100), JTable nvarchar(100), Contacts int, NewContacts int)
  28.  
  29. OPEN @TasksEnumerator
  30.  
  31. FETCH NEXT FROM @TasksEnumerator INTO @TaskId, @Name, @JTable
  32.  
  33. BEGIN TRY
  34.     WHILE @@FETCH_STATUS = 0
  35.         BEGIN      
  36.             FETCH NEXT FROM @TasksEnumerator INTO @TaskId, @Name, @JTable
  37.  
  38.             DECLARE @CountNew int = 0
  39.  
  40.             SET @sqltext = 'SELECT @Count = COUNT(*) FROM ' + @JTable
  41.             EXEC SP_EXECUTESQL @sqltext, N'@Count int out', @CountAll out
  42.  
  43.             INSERT INTO @IdTable VALUES(@TaskId, @Name, @JTable, @CountAll, @CountNew) 
  44.         END
  45.  
  46.     CLOSE @TasksEnumerator
  47.     DEALLOCATE @TasksEnumerator
  48.     END TRY
  49. BEGIN CATCH
  50.  
  51. END CATCH
  52. ------------------------------------------------------------------------------
  53. SELECT [Name] as 'Оператор'
  54.     , Contacts as 'Всего контактов'
  55.     , NewContacts as 'Новые'
  56.     ,(SELECT COUNT(*) FROM [yandextaxi].[dbo].[tbl_processing]  as tp WITH(NOLOCK) WHERE main.TaskId = tp.idtask) as 'В обработке'
  57.     --,(SELECT COUNT(*) from oktell.dbo.A_TaskManager_SubTasks as ST WHERE Main.TaskId = ST.TaskId AND NResult = 1) 'Дозвон успешен'
  58. FROM @IdTable as Main
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement