Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Oct 2010
    Posts
    99
    Thanks
    10
    Thanked 1 Time in 1 Post

    Nested query, then SUM

    Good day all,
    I need some assistance on how to finish this query.

    Code:
    SELECT hours.empid, SUM(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))) as timecalc, employees.employeeid, employees.firstname, employees.lastname, employees.class FROM hours, employees WHERE hours.empid = employees.employeeid AND hours.clockin BETWEEN '$_POST[startdate]' AND '$_POST[enddate]' GROUP BY hours.empid ORDER BY hours.empid DESC
    Above I am summing clock in and clout out times in a timeclock system. This works great, but I would also like to show each clock in and clock out record before displaying the sum for that employee.

    Can I loop through each employees records, display them, and then display the summed results at the end, then onto the next employee and so on?

    I hope that makes sense!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    That would be far the more sensible way.

    Something like this (not real code, but should give you the idea):

    Code:
    <?php
    
    $sql = "SELECT  E.empid, E.firstname, E.lastname, E.class, H.clockout, H.clockin
    FROM employees AS E HOURS AS H 
    WHERE H.empid = E.employeeid 
    AND H.clockin BETWEEN '$_POST[startdate]' AND '$_POST[enddate]' 
    ORDER BY H.empid DESC"
    
    $result = mysql_query( $sql );
    
    $lastid = -1;
    $sum = 0;
    
    while ( $row = mysql_fetch_assoc( $result )
    {
        $empid = $row["empid"];
        $fname = $row["firstname"];
        $lname = $row["lastname"];
        $eclass = $row["$class"];
        $clockout = $row["clockout"];
        $clockin = $row["clockin"];
        $tdiff = ...PHP code to get time diff from checkin to checkout...
    
        if ( $empid != $lastid )
        {
            if ( $lastid != -1 )
            {
                echo "<tr><td align=\"right\" colspan="\7\">$sum</td></tr>
                      <tr><td colspan=7><hr></td></tr\n";
            }
            $sum = tdiff;
            echo "<tr><td>$empid</td><td>$fname></td><td>$lname></td>";
        } else {
            $sum += $tdiff;
            echo "<tr><td colspan=3>&nbsp;</td>";
        }
        echo "<td>$eclass</td><td>$clockout</td><td>$clockin</td><td>$tdiff</td></tr>\n";
    }
    echo "<tr><td align=\"right\" colspan="\7\">$sum</td></tr>
          <tr><td colspan=7><hr></td></tr\n";
    ?>
    TOTALLY untested. Play with it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •