Calculating Main Current Task column by concatenating 2 task names based on multiple conditions
Hello, I am trying to solve the problem if someone can help that would be appreciated. I am beginning my journey with SQL. Desired Output - Main Current Task Column Task ID column is in ASCENDING order, and each language as a group has multiple Parent tasks. 1st Condition I am trying to calculate Main Current Task Column in such a way where ever Tsk Status is Delivered and Project_Deliverable_Not_Deliverable is 'Deliverable' between one Parent and other parent (i.e., Column - Parent_Find) within a same language group it concatenates the Task of Parent + Task from the row which as Delivered status from Tsk Status column (i.e., Task 3) and gives Main Current Task as Task 1 + Task 3 Likewise Task 4 + Task 9 for second Parent within same English language. 2nd if CONDITION understanding Can be any langauge, but For example lets say for Czech, as a language group Tsk Status is delivered for Task B but Project_Deliverable_Not_Deliverable is 'Non-Deliverable' hence no concatenation with Parent task as earlier just Task B. and Task D as a output within same Czech group as new parent is present, (order by ASC Tsk Id column) because Tsk Status is delivered, Project_Deliverable_Not_Deliverable is deliverable and Parent at a same time, hence only picking Task D. I was thinking to develop a logic saying where Parent Find = Parent and Tsk Id < Tsk Id of 2nd Parent within same language group and write if else statements to pick the relative task from Task Type column and then concatenate with Task of the parent. Issue is there can be multiple parent within a language group order by Tsk Id ASC Any help with the logic is helpful. Thanks. Job ID Langauge Task Type Parent_Find Project Id Tsk Id Tsk Status Main Current Task Project_Deliverable_Not_Deliverable 12345 English English Task 1 Parent 12345 8206892 Finished Task 1 + Task 3 Deliverable 12345 English English Task2 12345 8206893 Finished Task 1 + Task 3 Deliverable 12345 English English Task3 12345 8206894 Delivered Task 1 + Task 3 Deliverable 12345 English English Task 4 Parent 12345 8206895 Finished Task 4 + Task 9 Deliverable 12345 English English Task 5 12345 8206896 Finished Task 4 + Task 9 Deliverable 12345 English English Task 6 12345 8206897 Finished Task 4 + Task 9 Deliverable 12345 English English Task 7 12345 8206898 Finished Task 4 + Task 9 Deliverable 12345 English English Task 8 12345 8206899 Finished Task 4 + Task 9 Deliverable 12345 English English Task 9 12345 8206900 Delivered Task 4 + Task 9 Deliverable 12345 English English Task 10 12345 8206901 Canceled Task 4 + Task 9 Deliverable 23412 Czech Czech Task A Parent 23412 8206902 Canceled Task B Deliverable 23412 Czech Czech Task B 23412 8206903 Delivered Task B Non-Deliverable 23412 Czech Czech Task C 23412 8206904 Canceled Task B Deliverable 23412 Czech Czech Task D Parent 23412 8206905 Delivered Task D Deliverable 23412 Czech Czech Task E 23412 8206906 Delivered Task D Deliverable 23412 Czech Czech Task F Parent 23412 8206907 Finished Task F + Task I Deliverable 23412 Czech Czech Task G 23412 8206908 Finished Task F + Task I Deliverable 23412 Czech Czech Task H 23412 8206909 Finished Task F + Task I Deliverable 23412 Czech Czech Task I 23412 8206910 Delivered Task F + Task I Deliverable
SQL queries have become extremely slow compared to nearly identical queries on the same tables
Hi all. I've been drafting a SQL query from within MS SSMS, as a .SQL file, and then running it from within SSMS, on the same system that is running the 2019 developer edition server. The query file contains around 100 or so queries, virtually all UPDATE queries, which populate rows in a SUMMARYTABLE with results taken from the WORKTABLE. I've noticed that recently, the last 10% or so of these queries have become exceptionally slow - around 500 times slower than comparable queries. Here is an example of one of the fast ones, and one of the slow ones. The first takes less than a second: UPDATE database1.dbo.SUMMARYTABLE SET database1.dbo.SUMMARYTABLE.SalesTierCount = (select count(*) from database1.dbo.WORKTABLE as T1 WHERE T1.Manager = database1.dbo.SUMMARYTABLE.Manager and T1.SalesTier =1 and T1.Zdate < database1.dbo.SUMMARYTABLE.Zdate) the second one takes about seven minutes: UPDATE database1.dbo.SUMMARYTABLE SET database1.dbo.SUMMARYTABLE.LifetimeCount = (select count(*) from database1.dbo.WORKTABLE as T1 WHERE T1.retailer = database1.dbo.SUMMARYTABLE.retailer and T1.SalesTier =1 and T1.Zdate < database1.dbo.SUMMARYTABLE.Zdate) Does anyone have any suggestions for rectifying this extremely slow issue? 2 of the 3 WHERE/AND clauses are identical. The third clause differs, however both queries query the same type of data for that clause - both are varchar(50), in both tables. This is a link to the execution plan for both queries: https://www.brentozar.com/pastetheplan/?id=rypvD7Zb3 The final part of both plans shows an INDEX SCAN (non-clustered). The first, fast query, has the "Number of rows read" of around 200,000, the second, around 436 million. The total time to run the entire .SQL query has blown out from a minute or so, to nearly 4 hours! Any suggestions greatly appreciated.