Enterprise Insights: How to join sprint, jira sprint, and anchor sprint tables?

[Mary Heather Cox]

Background: We sync our Jira Cloud instance with Jira Align.  Sprint sync is the bane of my existence!

Scenario: Multiple Jira Sprints are Mapped to single Jira Align Team Sprint.

I am trying to identify why we get that error, when it appears everything is in order.

So, I'm trying to join 3 tables: Jira Sprint, Sprint, and Anchor Sprint but I feel like I'm missing a FK or any ability to join Jira Sprint to Anchor Sprint or Sprint.

I am trying to get the start/end dates of the sprints in the 3 different tables to compare them. Even then, if I join Anchor Sprint and Sprint on FK_Sprint_ID and Anchor_Sprint_ID I've already ruled out the ones that don't join and those are the ones.

Atlassian Analytics is no help, some I'm trying to join in Azure SQL tool and that's not working either.

[Allan Maxwell]

This works for me:

SELECT spr.[Sprint Name], jspr.[Jira Sprint Name], aspr.[Anchor Sprint Name]

FROM [current_dw].[Sprint] AS spr

LEFT JOIN [current_dw].[Jira Sprint] AS jspr ON spr.[Sprint ID] = jspr.[FK Sprint ID]

LEFT JOIN [current_dw].[Anchor Sprint] AS aspr ON spr.[FK Anchor Sprint ID] = aspr.[Anchor Sprint ID]

WHERE jspr.[Jira Sprint ID] is not NULL;