5 Replies Latest reply on Jun 26, 2006 6:38 AM by BKBK

    grouping output

    jbyrd4k
      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.



      [code]
      <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>
      [/code]




      Thanks, I have another idea I want to try while you all are looking at this.
        • 1. grouping output
          BKBK Adobe Community Professional & MVP
          Change the respective statements into:

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

          <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")




          • 2. Re: grouping output
            jbird5k Level 1
            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.

            [code]
            <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>

            [/code]
            • 3. Re: grouping output
              BKBK Adobe Community Professional & MVP
              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.
              • 4. Re: grouping output
                jbird5k Level 1
                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.
                • 5. grouping output
                  BKBK Adobe Community Professional & MVP
                  here's another suggestion, based on the new information



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