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.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Articles
Digital Transformation Automation

Simple Ways to Quickly Build Valuable Digital Experience

Practical thoughts and examples - how to manufacture time and attention, to get hands-on, relevant skills in new technologies
read more

3D Printing Requires Wildly Different Thinking

Additive Manufacturing faces slow adoption in most industrial companies due to material costs - and the challenge for designers to think differently about what was previously impossible.
read more

James MacLennan

... is the Managing Partner at Maker Turtle LLC, a digital consultancy focused on creating value in ways that align with your strategy and drive engagement with employees, customers, and stakeholders. He is an active creator, providing thought leadership through on-line & print publications, and public speaking / keynotes.