Thanks @beardeddev for all your efforts and answering all the comments . Your content on windowing functions are very helpful and the best available on youtube. Thanks a ton.
That is interesting and can cause some issues that need to be highlighted with the business/process owners. Options could be to create the city twice with different keys/IDs and then decide what data to assign to which city, state through business rules that way when aggregating by state you aren’t double counting or create the city and assign both state keys/IDs and assign data to both however when aggregating this will cause double counting which will need to be handled. Both approaches can be considered correct but it’s really up to the business/process owners to decide. Thank you for your comment.
Hello! I've been watching several of your videos (very good ones by the way) looking for a way to write a query with a recursive CTE to roll up the costs of a BOM from the bottom up. Can you tell me somewhere to get information or are you interested in making a video about it? Thank you!
Very interesting, whilst this is definitely something I will cover in the future, I don't have a video on it currently. Whilst the instructions in this video will flatten the hierarchy, that is just going to be the starting point. I would then suggest checking out my video on unpivoting, that might be useful to get the data in a more useable tabular format that can be easily summed, th-cam.com/video/0ERIr8UAK1s/w-d-xo.html
Hi there, This does help a lot. I am having a specific challenge where I'm trying to work around JIRA and confluence limitations. In Confluence we can create a report from JIRA using Table Transformer. What I have is a table with all the issues from a JIRA Project and it has a hierarchy of EPIC >> TASK/STORY/SPIKE >> SUBTASK I have a massive amount of trouble trying to export and work with a report that lists out the tasks in a logical order, i.e. everything just comes out in a random order depending on the issue "Key" which is the primary key for the table. The subtasks are linked to tasks, stories or spikes by parent id column and in turn the tasks, stories and spikes are linked to the epics by parent id. So a table might look like this: Issue key Summary Issue Type Status Assignee Parent ISS-1 Task 1 Task To Do bob ISS-10 ISS-27 Task 2 Task Done alice ISS-10 ISS-32 Task 3 Task Done john ISS-10 ISS-33 Task 4 Task Done mark ISS-15 ISS-16 Task 5 Task To Do bob ISS-15 ISS-15 Epic 1 Epic To Do ISS-24 Epic 2 Epic To Do ISS-10 Epic 3 Epic To Do ISS-37 Subtask 1 Subtask In Progress alice ISS-1 ISS-38 Subtask 2 Subtask To Do john ISS-1 ISS-39 Subtask 3 Subtask To Do mark ISS-27 ISS-40 Subtask 4 Subtask In Progress alice ISS-27 ISS-45 Subtask 5 Subtask In Progress john ISS-16 ISS-41 Subtask 6 Subtask Done mark ISS-16 ISS-66 Spike 1 Spike To Do alice ISS-24 ISS-67 Story 1 Story To Do john ISS-24 ISS-69 Subtask 7 Subtask To Do mark ISS-66 ISS-72 Subtask 8 Subtask To Do alice ISS-66 ISS-70 Subtask 9 Subtask To Do john ISS-66 ISS-71 Subtask 10 Subtask To Do mark ISS-67 ISS-74 Subtask 11 Subtask To Do alice ISS-67 ISS-73 Subtask 12 Subtask To Do john ISS-67 And I want the output to read: Issue key Issue Type Summary Status Assignee Parent ISS-15 Epic Epic 1 To Do ISS-33 Task Task 4 Done mark ISS-15 ISS-16 Task Task 5 To Do bob ISS-15 ISS-45 Subtask Subtask 5 In Progress john ISS-16 ISS-41 Subtask Subtask 6 Done mark ISS-16 ISS-24 Epic Epic 2 To Do ISS-66 Spike Spike 1 To Do alice ISS-24 ISS-69 Subtask Subtask 7 To Do mark ISS-66 ISS-72 Subtask Subtask 8 To Do alice ISS-66 ISS-70 Subtask Subtask 9 To Do john ISS-66 ISS-67 Story Story 1 To Do john ISS-24 ISS-71 Subtask Subtask 10 To Do mark ISS-67 ISS-74 Subtask Subtask 11 To Do alice ISS-67 ISS-73 Subtask Subtask 12 To Do john ISS-67 ISS-10 Epic Epic 3 To Do ISS-1 Task Task 1 To Do bob ISS-10 ISS-37 Subtask Subtask 1 In Progress alice ISS-1 ISS-38 Subtask Subtask 2 To Do john ISS-1 ISS-27 Task Task 2 Done alice ISS-10 ISS-39 Subtask Subtask 3 To Do mark ISS-27 ISS-40 Subtask Subtask 4 In Progress alice ISS-27 ISS-32 Task Task 3 Done john ISS-10 I've tried a few self joins but just can't seem to get the logic working.
I know the pain, luckily not personally but through an associate that exports data from Jira. I have imported the original data you provided as I wanted to make sure the results were correct, the below query has solved the problem. I have used a recursive CTE, this saves writing out the joins and can be better from a performance perspective. First I identified the top level as you can see in the first SQL query there is a WHERE clause of Parent IS NULL. I then need to join back to the original table on IssueKey = Parent, this will identify the first level of children, however as this is recursive the operation will be performed until the join no longer returns any data, in this case twice as there are three levels. You can add Path1, Path2, Path3 to the result set to see how this is done, it will need to be altered if there are any more levels in any further data. WITH CTE AS ( SELECT IssueKey, Summary, IssueType, [Status], Assignee, Parent, Summary AS Path1, CAST(NULL AS VARCHAR(50)) AS Path2, CAST(NULL AS VARCHAR(50)) AS Path3, 0 AS [Level] FROM dbo.Issues WHERE Parent is null
UNION ALL
SELECT Child.IssueKey, Child.Summary, Child.IssueType, Child.[Status], Child.Assignee, Child.Parent, Path1, CASE WHEN [Level] + 1 = 1 THEN Child.Summary ELSE Path2 END, CASE WHEN [Level] + 1 = 2 THEN Child.Summary ELSE Path3 END, [Level] + 1 FROM CTE INNER JOIN dbo.Issues AS Child ON CTE.IssueKey = Child.Parent ) SELECT IssueKey, Summary, IssueType, [Status], Assignee, Parent FROM CTE ORDER BY Path1, Path2, Path3 Hope this makes sense and if you are happy I would love to produce a video on how I did this.
BeardedDev firstly can I say a very big and deep thank you for even looking at this! You have no idea how much I appreciate the response. I’ll give this a try as soon as I can and let you know how I go!
@@BeardedDevData when I try the solution I get "r.statements[0].compile is not a function". I'm assuming this is because I'm trying to use Confluence's table transformer and utilise the SQL query option which may be limited compared to a proper MS SQL type scenario?
@BeardedDev, it looks to be working, only it doesn't sort by in the order I am after: Epic->Tasks underneath->Subtasks under these.... Is there a way to do it?
Thanks @beardeddev for all your efforts and answering all the comments . Your content on windowing functions are very helpful and the best available on youtube. Thanks a ton.
Thanks so much.
thank you for clear englisch
In India Chandigarh is a city that is shared capital of 2 states: Haryana and Punjab.
That is interesting and can cause some issues that need to be highlighted with the business/process owners. Options could be to create the city twice with different keys/IDs and then decide what data to assign to which city, state through business rules that way when aggregating by state you aren’t double counting or create the city and assign both state keys/IDs and assign data to both however when aggregating this will cause double counting which will need to be handled. Both approaches can be considered correct but it’s really up to the business/process owners to decide. Thank you for your comment.
Hello! I've been watching several of your videos (very good ones by the way) looking for a way to write a query with a recursive CTE to roll up the costs of a BOM from the bottom up. Can you tell me somewhere to get information or are you interested in making a video about it? Thank you!
Very interesting, whilst this is definitely something I will cover in the future, I don't have a video on it currently. Whilst the instructions in this video will flatten the hierarchy, that is just going to be the starting point. I would then suggest checking out my video on unpivoting, that might be useful to get the data in a more useable tabular format that can be easily summed, th-cam.com/video/0ERIr8UAK1s/w-d-xo.html
if i have 2 or more null value in MnagerID
That's plausible, you can have multiple directors or executives at a company.
can you please make a video on Self join
with date example, Thanks
Can you give me more detail on what you mean by date example?
@@BeardedDevData family,locations,DATES,staff.
Hi there,
This does help a lot. I am having a specific challenge where I'm trying to work around JIRA and confluence limitations. In Confluence we can create a report from JIRA using Table Transformer.
What I have is a table with all the issues from a JIRA Project and it has a hierarchy of EPIC >> TASK/STORY/SPIKE >> SUBTASK
I have a massive amount of trouble trying to export and work with a report that lists out the tasks in a logical order, i.e. everything just comes out in a random order depending on the issue "Key" which is the primary key for the table. The subtasks are linked to tasks, stories or spikes by parent id column and in turn the tasks, stories and spikes are linked to the epics by parent id.
So a table might look like this:
Issue key Summary Issue Type Status Assignee Parent
ISS-1 Task 1 Task To Do bob ISS-10
ISS-27 Task 2 Task Done alice ISS-10
ISS-32 Task 3 Task Done john ISS-10
ISS-33 Task 4 Task Done mark ISS-15
ISS-16 Task 5 Task To Do bob ISS-15
ISS-15 Epic 1 Epic To Do
ISS-24 Epic 2 Epic To Do
ISS-10 Epic 3 Epic To Do
ISS-37 Subtask 1 Subtask In Progress alice ISS-1
ISS-38 Subtask 2 Subtask To Do john ISS-1
ISS-39 Subtask 3 Subtask To Do mark ISS-27
ISS-40 Subtask 4 Subtask In Progress alice ISS-27
ISS-45 Subtask 5 Subtask In Progress john ISS-16
ISS-41 Subtask 6 Subtask Done mark ISS-16
ISS-66 Spike 1 Spike To Do alice ISS-24
ISS-67 Story 1 Story To Do john ISS-24
ISS-69 Subtask 7 Subtask To Do mark ISS-66
ISS-72 Subtask 8 Subtask To Do alice ISS-66
ISS-70 Subtask 9 Subtask To Do john ISS-66
ISS-71 Subtask 10 Subtask To Do mark ISS-67
ISS-74 Subtask 11 Subtask To Do alice ISS-67
ISS-73 Subtask 12 Subtask To Do john ISS-67
And I want the output to read:
Issue key Issue Type Summary Status Assignee Parent
ISS-15 Epic Epic 1 To Do
ISS-33 Task Task 4 Done mark ISS-15
ISS-16 Task Task 5 To Do bob ISS-15
ISS-45 Subtask Subtask 5 In Progress john ISS-16
ISS-41 Subtask Subtask 6 Done mark ISS-16
ISS-24 Epic Epic 2 To Do
ISS-66 Spike Spike 1 To Do alice ISS-24
ISS-69 Subtask Subtask 7 To Do mark ISS-66
ISS-72 Subtask Subtask 8 To Do alice ISS-66
ISS-70 Subtask Subtask 9 To Do john ISS-66
ISS-67 Story Story 1 To Do john ISS-24
ISS-71 Subtask Subtask 10 To Do mark ISS-67
ISS-74 Subtask Subtask 11 To Do alice ISS-67
ISS-73 Subtask Subtask 12 To Do john ISS-67
ISS-10 Epic Epic 3 To Do
ISS-1 Task Task 1 To Do bob ISS-10
ISS-37 Subtask Subtask 1 In Progress alice ISS-1
ISS-38 Subtask Subtask 2 To Do john ISS-1
ISS-27 Task Task 2 Done alice ISS-10
ISS-39 Subtask Subtask 3 To Do mark ISS-27
ISS-40 Subtask Subtask 4 In Progress alice ISS-27
ISS-32 Task Task 3 Done john ISS-10
I've tried a few self joins but just can't seem to get the logic working.
I know the pain, luckily not personally but through an associate that exports data from Jira. I have imported the original data you provided as I wanted to make sure the results were correct, the below query has solved the problem.
I have used a recursive CTE, this saves writing out the joins and can be better from a performance perspective.
First I identified the top level as you can see in the first SQL query there is a WHERE clause of Parent IS NULL. I then need to join back to the original table on IssueKey = Parent, this will identify the first level of children, however as this is recursive the operation will be performed until the join no longer returns any data, in this case twice as there are three levels.
You can add Path1, Path2, Path3 to the result set to see how this is done, it will need to be altered if there are any more levels in any further data.
WITH CTE
AS
(
SELECT
IssueKey,
Summary,
IssueType,
[Status],
Assignee,
Parent,
Summary AS Path1,
CAST(NULL AS VARCHAR(50)) AS Path2,
CAST(NULL AS VARCHAR(50)) AS Path3,
0 AS [Level]
FROM dbo.Issues
WHERE Parent is null
UNION ALL
SELECT
Child.IssueKey,
Child.Summary,
Child.IssueType,
Child.[Status],
Child.Assignee,
Child.Parent,
Path1,
CASE WHEN [Level] + 1 = 1 THEN Child.Summary ELSE Path2 END,
CASE WHEN [Level] + 1 = 2 THEN Child.Summary ELSE Path3 END,
[Level] + 1
FROM CTE
INNER JOIN dbo.Issues AS Child
ON CTE.IssueKey = Child.Parent
)
SELECT
IssueKey,
Summary,
IssueType,
[Status],
Assignee,
Parent
FROM CTE
ORDER BY
Path1,
Path2,
Path3
Hope this makes sense and if you are happy I would love to produce a video on how I did this.
BeardedDev firstly can I say a very big and deep thank you for even looking at this! You have no idea how much I appreciate the response. I’ll give this a try as soon as I can and let you know how I go!
No problem, let me know how it works out.
@@BeardedDevData when I try the solution I get "r.statements[0].compile is not a function".
I'm assuming this is because I'm trying to use Confluence's table transformer and utilise the SQL query option which may be limited compared to a proper MS SQL type scenario?
@BeardedDev, it looks to be working, only it doesn't sort by in the order I am after:
Epic->Tasks underneath->Subtasks under these....
Is there a way to do it?
⭐⭐⭐⭐⭐