Question
Subtotal SQL Statement
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?
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?
AdChoices