AO_60DB71_SPRINT

[Patrick Thornton]

I am attempting to figure out how JIRA keeps track of what issues are associated to a sprint for the following:

1. Issues added to a sprint after the sprint started (the asterisks) on the sprint report
   
2. How do you keep track of the story points at the beginning of the sprint vs the end of the sprint
   
3. And lastly how do you know if an issue was closed in a sprint or rolled over to the next sprint.

The data has to be stored somewhere because I keep coming up with different numbers, slightly different. The report I am writing is like 95% accurate there just appears to continue to be anomalies in the SQL logic.

Attempting to write a query that effectively returns
Sprint | ISSUENUM | Subject | SprintClosed | StoryPointsStartOfSprint | StoryPointsEndOfSprint | StoryPointsCompletedThisSprint | StoryPointsAddedAfterStartOfThisSprint  | StoryPointsRemovedThisSprint | StoryPointsNotCompletedThisSprint

SQL is pretty bad at this point but I think I can significantly simplify it if I can figure out where the data is rather then trying to calculate it.

IF OBJECT_ID('tempdb.dbo.#tmp_sprints', 'U') IS NOT NULL
drop table #tmp_sprints

select top 4 SprintNumber into #tmp_sprints from JIRA_AGILE_Sprints group by SprintNumber order by 1 desc

--select Sprint, ScrumTeam,
--sum(StoryPointsSprint) StoryPointsSprint,
--sum(StoryPointsClosed) StoryPointsClosed,
--sum(StoryPointsNotClosed) StoryPointsNotClosed,
--sum(StoryPointsRolled) StoryPointsRolled

--from
--(

--select Sprint, JIRA, ScrumTeam, IssueType, IssueStatus, StoryPointsStart, StoryPointsEnd,
-- case when Sprint = LatestSprint AND IssueStatus = 'Closed' then StoryPointsEnd else 0 end as StoryPointsClosed,
-- case when Sprint = LatestSprint AND IssueStatus != 'Closed' then StoryPointsEnd else 0 end as StoryPointsNotClosed,
-- case when Sprint != LatestSprint then StoryPointsEnd else 0 end as StoryPointsRolled
--from
--(
select Sprint, JIRA, JIRANum, StartDate, EndDate, ScrumTeam, IssueType, IssueStatus, LatestSprint, SprintNumber,
case when SprintClosed = 1 and StoryPointsStart is not null and StoryPointsStart != 0 then
StoryPointsStart
else
StoryPoints
end StoryPointsStart,
case when SprintClosed = 1 then
case when StoryPointsEnd is null or StoryPointsEnd = 0 then
case when StoryPointsStart is null then
StoryPoints
else
StoryPointsStart
end
else
StoryPointsEnd
end
else
StoryPoints
end StoryPointsEnd,
SprintClosed,
JIRAClosedInSprint,
RESOLUTIONDATE
from
(
select cfosprntall.Sprint, cfosprntall.SprintNumber, p.pkey + '-' + cast(ji.issuenum as varchar) JIRA, ji.issuenum as JIRANum, cfoteam.customvalue ScrumTeam, it.pname as IssueType,
i.pname IssueStatus, isnull(storypts.NUMBERVALUE,0) StoryPoints, isnull(spstart.OLDSTRING, 0) StoryPointsStart,
isnull(spend.NEWSTRING, 0) StoryPointsEnd, cfosprntall.StartDate,
cfosprntall.SprintClosed, cfosprntall.JIRAClosedInSprint, cfosprntall.EndDate, ji.RESOLUTIONDATE,
cfosprnt.*
from jiraissue ji
inner join project p on p.ID = ji.project
inner join issuetype it on it.id = ji.issuetype
inner join issuestatus i on i.ID = ji.issuestatus
inner join customfieldvalue team ON team.CUSTOMFIELD = 10060 AND team.ISSUE = ji.ID
inner join customfieldoption cfoteam on cfoteam.CUSTOMFIELD = 10060 and cfoteam.id = team.stringvalue
inner join customfieldvalue storypts ON storypts.CUSTOMFIELD = 13587 AND storypts.ISSUE = ji.ID

outer apply
(
select Sprint, SprintNumber, StartDate, EndDate, CLOSED SprintClosed,
case when ji.RESOLUTIONDATE <= EndDate then 1 else 0 end JIRAClosedInSprint
from
(
select t2.Name as Sprint,
cast(
case
when t2.SprintNumber != '' AND isnumeric(t2.SprintNumber) = 1 then try_cast(t2.SprintNumber as int)
when t2.SprintNumber2 != '' AND isnumeric(t2.SprintNumber2) = 1 then try_cast(t2.SprintNumber2 as int)
else
null
end as int
) SprintNumber, t2.closed, StartDate, EndDate
from
(
select Name,
substring(t.NAME, len(t.NAME) - charindex(' ', reverse(t.NAME))+2, 4) SprintNumber,
substring(t.NAME, len(t.NAME) - charindex('-', reverse(t.NAME))+2, 4) SprintNumber2, t.closed,
dateadd(s, (t.[start_date]/1000), CAST( '1970-01-01' as datetime ) ) as StartDate,
dateadd(s, (t.[end_date]/1000), CAST( '1970-01-01' as datetime ) ) as EndDate
from
(
select top 100 percent cast(b.name as varchar(100)) Name, b.closed, b.START_DATE, b.END_DATE
from dbo.customfieldvalue a
inner join dbo.AO_60DB71_SPRINT b on b.id = a.stringvalue
where a.CUSTOMFIELD = 13581 AND a.ISSUE = ji.ID and isnumeric(a.stringvalue) = 1
order by b.[start_date] desc
) t
) t2
) t3
where t3.SprintNumber is not null
) cfosprntall
outer apply
(
select top 1 isnull(convert(int, CONVERT(NVARCHAR(100),ci.OLDSTRING)), convert(int, CONVERT(NVARCHAR(100),ci.NEWSTRING))) OLDSTRING
from changegroup cg
inner join [changeitem] ci on ci.groupid = cg.id
where cg.issueid = ji.ID and fieldtype = 'custom' and field = 'story points' and cg.CREATED >= cfosprntall.startDate --and ISNUMERIC(ci.OLDSTRING) = 1
order by cg.CREATED asc
) spstart
outer apply
(
select top 1 convert(int, CONVERT(NVARCHAR(100),ci.NEWSTRING)) NEWSTRING
from changegroup cg
inner join [changeitem] ci on ci.groupid = cg.id
where cg.issueid = ji.ID and fieldtype = 'custom' and field = 'story points' and cg.CREATED <= cfosprntall.EndDate --and ci.NEWSTRING is not null --and ISNUMERIC(ci.NEWSTRING) = 1
order by cg.CREATED desc
) spend
outer apply
(
select Sprint LatestSprint, SprintNumber LatestSprintNumber
from
(
select t2.Name as Sprint,
cast(
case
when t2.SprintNumber != '' AND isnumeric(t2.SprintNumber) = 1 then try_cast(t2.SprintNumber as int)
when t2.SprintNumber2 != '' AND isnumeric(t2.SprintNumber2) = 1 then try_cast(t2.SprintNumber2 as int)
else
null
end as int
) SprintNumber, t2.closed
from
(
select Name,
substring(t.NAME, len(t.NAME) - charindex(' ', reverse(t.NAME))+2, 4) SprintNumber,
substring(t.NAME, len(t.NAME) - charindex('-', reverse(t.NAME))+2, 4) SprintNumber2, t.closed
from
(
select top 1 cast(b.name as varchar(100)) Name, b.closed
from dbo.customfieldvalue a
inner join dbo.AO_60DB71_SPRINT b on b.id = a.stringvalue
where a.CUSTOMFIELD = 13581 AND a.ISSUE = ji.ID and isnumeric(a.stringvalue) = 1
order by b.[start_date] desc
) t
) t2
) t3
where t3.SprintNumber is not null
) cfosprnt
where it.pname not like '%sub%task%'
) t
where
ScrumTeam = 'Null' and
SprintNumber in (select SprintNumber from #tmp_sprints)
and sprintnumber = '2005'
--and jira = 'CPSI-1085'
order by 14, 3
--) t
--order by SprintNumber desc, JIRANum
--) t
--group by Sprint, ScrumTeam