This post is to attempt placate certain individuals who are upset with my entry into a contest. It is probably not of interest to those who typically read this oft-neglected blog.
This is an example of one of the SQL queries that I wrote for my digital kanban board:
select a.AssignableID as 'id', et.Abbreviation as 'type', g.Name as 'name', es.EntityStateID as 'stateId', a.Effort as 'effort', b.SeverityID as 'severityId', a.ProjectID as 'projectId', a.IterationID as 'iterationId' from Assignable a join EntityState es on a.EntityStateID = es.EntityStateID join General g on a.AssignableID = g.GeneralID join EntityType et on g.EntityTypeID = et.EntityTypeID join Project p on a.ProjectID = p.ProjectID left outer join Iteration i on a.IterationID = i.IterationID left outer join General i_g on i.IterationID = i_g.GeneralID left outer join Bug b on a.AssignableID = b.BugID left outer join Severity s on b.SeverityID = s.SeverityID where es.Name <> 'Backlog' and p.IsActive = 1 and (g.EntityTypeID = 4 or g.EntityTypeID = 8) and (getdate() between i_g.StartDate and dateadd(d, 7, g.EndDate) or es.Final = 0) order by s.Importance, a.Effort desc
We have a user story state called "Backlog", which we're omitting in this query using the first item in the
where. (Personally, I think backlogging should be done simply by taking it out of the iteration, but that's my opinion.) We also check to make sure we're not showing items from inactive projects (
p.IsActive = 1), that we're only showing user stories and bugs (
g.EntityTypeID = 4 or g.EntityTypeID = 8) and that the iteration it belongs to is active or the item is still open even if the iteration has finished (
getdate() between i_g.StartDate and dateadd(d, 7, g.EndDate) or es.Final = 0). Note that when an iteration finishes, the items from it continue to appear for a week afterward.
This should be enough to convince reasonable individuals that I did indeed write this myself, not that writing it myself was even a criterion for consideration in the contest in the first place.
Update: Here's a video demo of it, hosted by myself.