SQL Hack for Reporting Project Phase and Status

It’s been a while since I’ve posted some code, but I did a nice little SQL hack today that I’ve been puzzling over for a while. I freely admit that I may have made this more difficult with the original data model, but the die has been cast.

Consider a single SQL table that captures project updates as comments note that this table also allows me to change the “phase” (ex. Design, Development, Test, Production) and/or Status (ex. Proposed, Red, Yellow, Green, Complete) of the project. A sample set of comments for a single project would look like this:

tblComments
ID	Phase	Status	Comment Date	Comment
1	Opportunity	Proposed	7/4/2007	Lorem ipsum dolor sit amet
2	Opportunity	Proposed	7/13/2007	consectetuer adipiscing elit
3	Opportunity	On Hold	7/17/2007	sed diam nonummy nibh euismod
4	Opportunity	On Hold	7/22/2007	tincidunt ut laoreet dolore magna
5	Concept	Proposed	7/25/2007	aliquam erat volutpat Ut
6	Concept	Active – Green	7/27/2007	wisi enim ad minim veniam
7	Concept	Active – Green	7/28/2007	quis nostrud exerci tation
8	Concept	Active – Green	7/30/2007	ullamcorper suscipit lobortis nisl ut
9	Design	Active – Green	8/8/2007	aliquip ex ea commodo consequat
10	Design	Active – Yellow	8/9/2007	Duis autem vel eum
11	Design	Active – Yellow	8/18/2007	iriure dolor in hendrerit in
12	Design	Active – Green	8/26/2007	vulputate velit esse molestie consequat
13	Design	Active – Green	9/4/2007	vel illum dolore eu
14	Design	Active – Green	9/14/2007	feugiat nulla facilisis at vero
15	Development	Active – Green	9/21/2007	eros et accumsan et iusto
16	Development	Active – Yellow	9/22/2007	odio dignissim qui blandit praesent
17	Development	Active – Green	9/29/2007	luptatum zzril delenit augue duis
18	Development	Active – Yellow	10/2/2007	dolore te feugait nulla facilisi
19	Development	Active – Red	10/7/2007	Epsum factorial non deposit quid
20	Development	Active – Red	10/14/2007	pro quo hic escorol
21	Concept	Active – Red	10/18/2007	Olypian quarrels et gorilla congolium
22	Concept	Active – Red	10/28/2007	sic ad nauseum Souvlaki
23	Concept	Active – Yellow	11/4/2007	ignitus carborundum e pluribus unum
24	Concept	Active – Green	11/6/2007	Defacto lingo est igpay
25	Concept	Active – Green	11/14/2007	atinlay Marquee selectus non
26	Design	Active – Green	11/19/2007	provisio incongruous feline nolo contendre
27	Design	Active – Green	11/26/2007	Gratuitous octopus niacin
28	Development	Active – Green	11/29/2007	sodium glutimate Quote meon
29	Implementation	Active – Green	12/1/2007	an estimate et non interruptus
30	Implementation	Active – Green	12/5/2007	stadium Sic tempus fugit
31	Implementation	Active – Green	12/13/2007	esperanto hiccup estrogen Glorious
32	Implementation	Active – Green	12/16/2007	baklava ex librus hup hey
33	Operation	Active – Green	12/21/2007	ad infinitum Non sequitur
34	Operation	Active – Green	12/23/2007	condominium facile et geranium incognito
35	Operation	Completed	12/30/2007	Epsum factorial non
36	Operation	Completed	1/4/2008	deposit quid pro quo hic escorol Marquee

It’s a project’s life cycle thru multiple phases; but notice that this project starts on 7/4, gets up to Development, then a new requirement comes along (10/18) and forces it to go back to Concept phase to rethink. When querying this, I want a result set to show one record for the first time the project goes to the next phase … something like this …

ID	Phase	Comment Date
1	Opportunity	7/4/2007
5	Concept	7/25/2007
9	Design	8/8/2007
15	Development	9/21/2007
21	Concept	10/18/2007
26	Design	11/19/2007
28	Development	11/29/2007
29	Implementation	12/1/2007
33	Operation	12/21/2007

My first attempt was using GROUP BY in a select statement, but I couldn’t get that to work – it doesn’t do subtotals. Also, COMPUTE BY was not an option – I want to create a view, and have the results sitting there for me. I asked my favorite SQL guru, who solved the problem using cursors, but that didn’t get me to my view.

I ended up solving using subqueries, comparing the current record to the previous record, and setting a flag whenever the Phase changes … like this …

• Add a User-defined function …

[sql] CREATE FUNCTION PhaseChangeFlag (@CurrPhase varchar(10), @PrevPhase varchar(10))
RETURNS int
AS
BEGIN
IF @CurrPhase <> @PrevPhase RETURN(1)
RETURN(0)
END
[/sql]

• Create a SELECT statement that sub-queries to find the previous record

[sql] SELECT ID, Phase, PhaseChangeFlag( Phase,
SELECT prv_rec.Phase FROM tblComments AS prv_rec
WHERE prv_rec.ID = ( SELECT MAX(chk_rec.ID) <<<< hack!
FROM tblComments AS chk_rec
WHERE chk_rec.ID &lt; cur_rec.ID)
)
) AS PhaseChange
FROM tblComments cur_rec
[/sql]

Now I get a result set that looks like this …

ID	Phase	PhaseChange	Comment Date
1	Opportunity	1	7/4/2007
2	Opportunity	0	7/13/2007
3	Opportunity	0	7/17/2007
4	Opportunity	0	7/22/2007
5	Concept	1	7/25/2007
6	Concept	0	7/27/2007
7	Concept	0	7/28/2007
8	Concept	0	7/30/2007
9	Design	1	8/8/2007
10	Design	0	8/9/2007
11	Design	0	8/18/2007
12	Design	0	8/26/2007
13	Design	0	9/4/2007
14	Design	0	9/14/2007
15	Development	1	9/21/2007
16	Development	0	9/22/2007
17	Development	0	9/29/2007
18	Development	0	10/2/2007
19	Development	0	10/7/2007
20	Development	0	10/14/2007
21	Concept	1	10/18/2007
22	Concept	0	10/28/2007
23	Concept	0	11/4/2007
24	Concept	0	11/6/2007
25	Concept	0	11/14/2007
26	Design	1	11/19/2007
27	Design	0	11/26/2007
28	Development	1	11/29/2007
29	Implementation	1	12/1/2007
30	Implementation	0	12/5/2007
31	Implementation	0	12/13/2007
32	Implementation	0	12/16/2007
33	Operation	1	12/21/2007
34	Operation	0	12/23/2007
35	Operation	0	12/30/2007
36	Operation	0	1/4/2008

So I can easily write a query from this view that only shows records where PhaseChange = 1.

Now I can create web queries and reports that show project Phase and/or Status as of any date – significant progress towards the idea of transparency … aging projects, how many did we close for you this quarter, stuff like that.

I’m not too sure how “expensive” this query is, but the database is small.