grouping output

Report · Jun 16, 2006

Hello,

I have tried so many ways of doing this that I have become confused. What I am trying to do is this;

I am reading record for an employee from a database and group them by empl name, then if the date in the record is NOT THURSDAY, write the record to the screen and increment the total hours var by the hrs value and get the next record.

if the date asociated with the record IS thursday write the record, increcment the total hours, write the weekly total, reset the weekly total to 0 ge the next record.

I used <cfif><cfelse> to handle the logic and datepart() to determine the day. This worked to a point. If there are multiple entries for the same thursday they are all written as individual weeks (yes, I know that is what the code says to do) and if the last record in the record set is not thursday I don't get a weekly total.

I tried to address the group of thursday entries by adding a date comparison in there but I couldn't get it to work either (I was missing some thing an could not put my finger on it).

My next shot will be loops, but Im sure which would be the best way to go.

Here is the code, I have modified it tweeked it,ripped sections out and replaced section.

Can someone help me straighten it out.


 <cfparam name="st_hrs" default="0">

 

 

 

 <table width="100%" cellspacing="0" cellpadding="3">

 <cfoutput query="getDetail" group="staffLname">

 <tr>

 <td class="lablesW"
colspan="4"><strong>#staffLname#, #staffFname#
</strong>

 <table width="100%">

 <tr>

 <td width="10%"> </td>

 <td width="90%">

 <table width="100%">

 <tr> 

 <th scope="col" class="lables"
width="25%"><strong>Charge
Number</strong></th>

 <th scope="col" class="lables"
width="12.5%"><strong>Hours</strong></th>

 <th scope="col" class="lables"
width="12.5%"><strong>CR</strong><strong></th>

 <th scope="col" class="lables"
width="50%"><strong>Description *
</strong></th>

 </tr>

 </table>

 </td>

 </tr>

 </table>

 <table width="100%">

 <cfoutput > 

 

 <cfif #dayofweek(DatePart('W', wkEndingDTD))# neq 5
><!---Development Note:: if the day is not THURSADY write the
record to the screen--->

 <tr> 

 <td class="lables"> 

 #dateformat(wkEndingDTD, 'mm/dd/yyyy')# 

 </td>

 <td> 

 <table width="100%" cellspacing="0" cellpadding="3">

 

 <

 <tr> 

 <td scope="row" class="lables"
width="25%">#catChrgNum#</td>

 <td class="lables" width="12.5%">#hrs#</td>

 <td class="lables" width="12.5%">#cr#</td>

 <td class="lables" width="50%"><cfif crDscr is
'>

 #ChrgCat# 

 <cfelse>

 #crDscr# 

 </cfif><!-- Development Note:: always display a
description, either the CR descr OR the charge number -->

 </td>

 </tr>

 </table></td>

 </tr>

 

 

 <cfset T_hrs = #st_hrs# + #hrs# ><!-- Development
Note:: sum of hours for the week -->

 <cfelse> Development Note:: If the day is Thursday
write the record to the screen 

 <cfif #dayofweek(DatePart('W', wkEndingDTD))# Neq
#pdtd#>

 

 

 <tr> 

 <td class="lables">#dateformat(wkEndingDTD,
'mm/dd/yyyy')# </td>

 <td> <table width="100%" cellspacing="0"
cellpadding="3">

 <tr> 

 <td class="lables"width="25%">#catChrgNum#</td>

 <td class="lables"width="12.5%">#hrs#</td>

 <td class="lables"width="12.5%">#cr#</td>

 <td class="lables" width="50%"><cfif crDscr is
'>

 #ChrgCat# 

 <cfelse>

 #crDscr# 

 </cfif></td><!-- Development Note:: always
display a description, either the CR descr OR the charge number
-->

 </tr>

 </table></td>

 </tr>

 </cfif> 

 <cfset T_hrs = #st_hrs# + #hrs# >

 <tr> 

 <td> </td>

 <td >

 <table width="100%" cellspacing="0" cellpadding="3">

 <tr>

 <td class="lablesW" width="25%"><strong>Weekly
Total </strong></td>

 <td
class="lablesW"width="12.5%"><strong>#T_hrs#</strong></td>

 <td
width="12.5%"><strong> </strong></td>

 <td
width="50%"><strong> </strong></td>

 </tr>

 </table>

 </td>

 </tr>

 <cfset T_hrs = 0 ><!-- Development Note :: reset
weekly total to zero-->

 </cfif> 

 </cfoutput> </table> 

 </td>

 </tr>

Thanks, I have another idea I want to try while you all are looking at this.

Report · Jun 16, 2006

Change the respective statements into:

<cfif dayofweek(wkEndingDTD) neq 5 >

<cfset T_hrs = st_hrs + hrs>

<cfif dayofweek(wkEndingDTD) Neq pdtd>

added later:
use just one cfoutput tag, namely, <cfoutput query="getDetail" group="staffLname">
(assumes your query did "order by staffLname")

Report · Jun 23, 2006

BKBK

Thanks for the input, unfortunately it didn't work.
Copy the code below and view it in your browser. this is what I am trying to accomplish. problems I am having, 1.) i can't get the thursdays to group and total, 2.) I cant get a total if the last record in the record set is not a thursday.


<table width="100%" border="1" cellspacing="0"
cellpadding="3">

 <tr bgcolor="#CCCCCC"> 

 <td colspan="5"><strong>Tester
JB</strong></td>

 </tr>

 <tr bgcolor="#CCCCCC"> 

 <td><strong>Date</strong></td>

 <td><strong>Charge Number
</strong></td>

 <td><strong>CR</strong></td>

 <td><strong>Hours</strong></td>


<td><strong>Descriptions</strong></td>

 </tr>

 <tr> 

 <td>Thursday</td>

 <td>A00001</td>

 <td>1200</td>

 <td>24</td>

 <td> spr Testing</td>

 </tr>

 <tr> 

 <td> </td>

 <td>Weekly Total</td>

 <td> </td>

 <td>24</td>

 <td> </td>

 </tr>

 <tr> 

 <td>Thursday</td>

 <td>A0002</td>

 <td>2345</td>

 <td>16</td>

 <td>Development</td>

 </tr>

 <tr> 

 <td>Thursday</td>

 <td>A0001</td>

 <td>1200</td>

 <td>2</td>

 <td>spr testing</td>

 </tr>

 <tr> 

 <td>thursday</td>

 <td>A0006</td>

 <td>1371</td>

 <td>4</td>

 <td>Development</td>

 </tr>

 <tr> 

 <td> </td>

 <td>Weekly Total</td>

 <td> </td>

 <td>22</td>

 <td> </td>

 </tr>



 <tr> 

 <td>Friday</td>

 <td>A0001</td>

 <td>1200</td>

 <td>8</td>

 <td>spr Testing</td>

 </tr>

 <tr> 

 <td>Monday</td>

 <td>A0001</td>

 <td>1201</td>

 <td>8</td>

 <td>spr Testing</td>

 </tr>

 <tr> 

 <td>Wednesday</td>

 <td>A0006</td>

 <td>2345</td>

 <td>8</td>

 <td>Development</td>

 </tr>

 <tr> 

 <td>Thursday</td>

 <td>A0002</td>

 <td>2345</td>

 <td>8</td>

 <td>Development</td>

 </tr>

 <tr> 

 <td> </td>

 <td>Weekly total</td>

 <td> </td>

 <td>32</td>

 <td> </td>

 </tr>

 <tr> 

 <td>Monday</td>

 <td>A0002</td>

 <td>1371</td>

 <td>4</td>

 <td>Development</td>

 </tr>

 <tr> 

 <td>Tuesday</td>

 <td>A0002</td>

 <td>1371</td>

 <td>8</td>

 <td>Devleopment</td>

 </tr>

 <tr>

 <td> </td>

 <td>weekly total</td>

 <td> </td>

 <td>12</td>

 <td> </td>

 </tr>

</table>

Report · Jun 23, 2006

Thanks for the input, unfortunately it didn't work.
It was not meant as a solution, just a suggestion. Your code is quite detailed and involved, but says nothing about the query behind it. What do you order by or group by in the query?

I would start with something simple, like the following, and develop it further from there. I have assumed that the rows in the resultset are for consecutive days and that wkEndingDTD stands for a date.

Report · Jun 26, 2006

My query is ordered by wkEndingDtD there is no group in the query. WkEndingDtD is a date. The users have the option of entering their hours daily or they can enter all their hours for the week on thursday by cob. So it is possible for users to have,multiple enteries on thursday (one for each charge number worked during the week) with total hours for the week.

Some users will do work under a half dozen different charge numbers depending on their function in the course of the day/ week, the daily/weekly option keeps admin time down.

I am still having an issue with getting a weekly total if the last record in record set IS NOT THURSDAY. I need to find a way to identifiy the EOF . I tried using <cfloop> and decrementing the recordcount but still could not get it to work properly

Thanks for the assistance.

Report · Jun 26, 2006

here's another suggestion, based on the new information

edited: modified code so it would also work for non-consecutive date values wkEndingDTD

Adobe Community

grouping output