Monthly Reports

[LearnersPermit]

I'm working on an application which tracks the time that staff spend on specific projects. Users will record the number of hours that they spend each day on their various projects. At the end of the month I wish to have a summary report that will summarize the number of hours spent each month on projects.

I am able to prepare a report for one month but have not been able to show more than one month or to summarize the hours spent on a specific project by all staff. My report should look like

Project Jan. Feb. Mar. Apr. May , etc.
Project 1 25 33 22 66
Project 2

My Code
<cfquery name="qsummary" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
select record.recid,record.day,record.hours_worked,record.projid,project.projid,project.project
from Record,
Project
where record.projid=project.projid
and record.day between <cfqueryparam cfsqltype="cf_sql_date" value="June 1, 2006"> and <cfqueryparam cfsqltype="cf_sql_date" value="June 30, 2006">
order by record.day

</cfquery>

<table>
<tr>
<td>
<cfoutput>
<p class="gen">Monthly Summary Report</p>
<table border=1 cellspacing=0 align="left">
<tr>
<th>Project</th>
<th>June</th>
</tr>
<cfloop query="qsummary">
<tr>
<td><p class="gen">#qsummary.project#</p></td>

<td align="center"><p class="gen">#qsummary.hours_worked#</p></td>
</tr>
</cfloop>

</cfoutput>

Thank you for your assistance.

 

[jhall01]

Depending on your Database system you could do something similar (date functions vary DB to DB):

<cfquery name="qsummary" datasource="#request.dsn#" username="#request.uname#" password="#request.passwd#">
SELECT
  b.project
 ,SUM(CASE WHEN month(a.day) = 1 THEN a.hours_worked ELSE 0 END) jan
,SUM(CASE WHEN month(a.day) = 2 THEN a.hours_worked ELSE 0 END) feb
,SUM(CASE WHEN month(a.day) = 3 THEN a.hours_worked ELSE 0 END) mar
,SUM(CASE WHEN month(a.day) = 4 THEN a.hours_worked ELSE 0 END) apr
,SUM(CASE WHEN month(a.day) = 5 THEN a.hours_worked ELSE 0 END) may
FROM
  record a INNER JOIN project b ON a.projID = b.projID
WHERE year(a.day) = 2006
GROUP BY b.project
</cfquery>

then just handle your output:

<table><tr><td>Jan</td><td>Feb</td></tr>
<cfoutput query="qsummary>
<tr><td>#qsummary.jan#</td><td>#qsummary.feb#</td></tr>
</cfoutput>
</table>

 

[LearnersPermit]

Thank you so much for your help. With the help of our DBA a few small tweaks were made to get it working with Oracle 8i and I'm getting my results.

SELECT
b.project
,SUM(CASE WHEN to_char(a.day, 'mm') = '01' THEN a.hours_worked ELSE 0 END) jan
,SUM(CASE WHEN to_char(a.day, 'mm') = '02' THEN a.hours_worked ELSE 0 END) feb
FROM
record a, project b
WHERE to_char(a.day, 'yyyy') = '2006'
and a.projid = b.projid
GROUP BY b.project