Subtotal SQL statement

[chrispilie]

I am working with a database table that has a list projects. Some of these projects have sub-projects. There is a column with weekending dates with corespond to manhours associated with that week. Here is an example.

JobNum SubJobNum WeekEndingDate Manhours
--------------------------------------------------------------------------
1 001 8-17-08 200
1 001 8-24-08 300
1 001 8-31-08 250
1 001 9-7-08 100
1 002 8-17-08 50
1 002 8-24-08 0
1 002 8-31-08 90
2 045 8-17-08 100
2 045 8-17-08 50

I guess you get the point. What I am trying to accomplish is query the running total of manhours by WeekEndingDate and by Job Number. Ultimately I am trying to get a percent complete planned. Each date would have the subtotal of total manhours divided by total manhours.

This is what I would like to output:

JobNum TotalWeekEndingManhours WeekEndingDate %Comp
----------------------------------------------------------------------------------------
1 250 8-17-08 25%
1 300 8-24-08 56%
1 340 8-31-08 90%
1 100 9-7-08 100%

This the query that I am working with so far.

<!--- Get the raw data from the database. --->
<cfquery name="GetPerc" datasource="db1">
SELECT p.WeekEndingDate, 
	SUM(p.Planned) AS TotPlanned,
	(SUM(p2.Planned*1^(p.WeekEndingDate-p2.WeekEndingDate))) AS TotPercPlanned,
	   SUM(p.Earned) AS SumEarned, 
	   SUM(p.WeeklyEarned) AS SumWeekly,
	(SUM(p.Planned)/#GetTotPlanned.TotPlanned#)*100 AS PercPlanned,
	(SUM(p.Earned)/#GetTotPlanned.TotPlanned#)*100 AS PercEarned,
	(DAY(p.WeekEndingDate) & '/' & MONTH(p.WeekEndingDate) & '/' & YEAR(p.WeekEndingDate)) AS 
formattedDate
FROM Productivity p LEFT JOIN Productivity p2
ON p.WeekEndingDate > p2.WeekEndingDate
WHERE p.JobNumber = '#URL.JobNumber#'
GROUP BY p.WeekEndingDate,
			p.JobNumber

Anyone has any suggestions?

[chrispilie]

sorry about the column formatting!!!