Friday, March 25, 2011

For the record...

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.