Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Trying to find what Epics are not assigned to a PI in Enterprise Insights

Mark Dodrill April 3, 2023

I am using Azure Data Studio to talk to my Enterprise Insights instance.  I am trying to write a SQL query that will find and return any Jira Align Epics that are NOT assigned to any Program Increment.  Per the EI Schema version 10.115, there isn't a direct linkage between Epics and Program Increments.  However, I see there is a MAP table from Epics to Program Increments, but I can't seem to get the SQL correct to find Epic IDs that are NOT in this mapping table.

I've tried several things similar to:

 

SELECT DISTINCT
    Epics.[Epic ID],
    Epics.[Epic Name]
FROM
    [current_dw].[Epic] AS Epics
    RIGHT OUTER JOIN
        [current_dw].[MAP Epic to Program Increment] AS PIs ON Epics.[Epic ID] = PIs.[FK Epic ID]
I've tried both LEFT OUTER JOINs and RIGHT OUTER JOINs, but I don't get the right data (either I get zero records or all of them, when I am expecting to get just one.
Any thoughts here?  It seems like I'm close, but not quite there.
Thank you.

1 answer

1 accepted

1 vote
Answer accepted
Sam Tsubota
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 3, 2023

Hi @Mark Dodrill ,

 

You need to use the MAP to Epic to Program Increment table because Epics have a one to many relationship with Program Increments in order to determine the which Program Increments are assigned to an Epic. One of the things about the Enterprise Insights table is that there is a record in most tables with a 0 ID number that identifies if no corresponding record is assigned. For example, the 0 ID record in the current_dw.Program Increment table is has a PI Name = No Program Increment Assigned. In your query, you could have added a WHERE PIs.[FK Program Increment ID] = 0 to return all Epics not assigned to a PI. I suggest joining the MAP table using a LEFT OUTER JOIN instead of a RIGHT OUTER JOIN. I've modify your query and joined the Program Increment table to add more information to your query to illustrate what I explained.

 

SELECT DISTINCT

E.[Epic ID]

,E.[Epic Name]

,M2PI.[FK Program Increment ID]

,PI.[PI Name]

FROM [current_dw].[Epic] AS E

LEFT OUTER JOIN [current_dw].[MAP Epic to Program Increment] AS M2PI ON E.[Epic ID] = M2PI.[FK Epic ID]

LEFT OUTER JOIN [current_dw].[Program Increment] AS PI ON PI.[Program Increment ID] = M2PI.[FK Program Increment ID]

WHERE PI.[PI Name] = 'No Program Increment Assigned'
Mark Dodrill April 4, 2023

Awesome, thanks so much!  I appreciate your help on this.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events