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

[Mark Dodrill]

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.

[Sam Tsubota · Accepted solution]

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]

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