I have a table with the below sample output.

uid     atime      adate      A_in_out

11    8:16     14/05/2014       I
11   13:35   14/05/2014         I
11    17:23    14/05/2014       I
11    21:09    14/05/2014       I
12    14:06    14/05/2014       I
12    22:39    14/05/2014       I
13    8:00     14/05/2014       I
13    17:12    14/05/2014       I

I want to build a query to achieve the below results:

UID    Date          TimeIn TimeOut Hours

11     14/05/2014    8:16   13:35  5:19
11     14/05/2014    17:23  21:09  3:46
12     14/05/2014    14:06  22:39  8:33
13     14/05/2014    8:00   17:12  9:12

i am trying following codes..

<?php
                    $uid="";
                     if (isset($_POST['show'])) 
                     $uid = $_POST['uid'];
                     $adate = $_POST['adate'];
                      $sno    = 1;

     /*$query  = "select * from users ORDER BY userid";
    $result = mysqli_query($con, $query);

                  if (mysqli_affected_rows($con) != 0) 
                   while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) 
                  $uid   = $row['userid'];
                  $adate = "";
                  $atime ="";*/
                  $query2 = "SELECT * FROM ATTND WHERE uid ='$uid' OR adate = '$adate' order by adate,atime";
                  $result2 = mysqli_query($con,$query2);
                 if (mysqli_num_rows($result2)>0) 
                     {
                  while ($row1 = mysqli_fetch_assoc($result2)) 
                  {       
                    $uid = $row1['uid'];
                    $adate = $row1['adate'];
                    $atime = $row1['atime'];

                    $adate = date('d-m-Y',strtotime($adate));
                    //$atime = date('h:i:A ',strtotime($atime));

                    $query3="SELECT * FROM attnd WHERE uid IN(SELECT atime FROM attnd WHERE atime%2 = 0)";

                    $result3 = mysqli_query($con,$query3);
                     $class = count( $atime ) % 2 == 0 ? 'even' : 'odd';  
                   if($uid % 2 == 0)
              {
                  //while ($row1 = mysqli_fetch_array($result3)){
               //$uid=$row1['uid'];

               echo "<h2>".$uid. " even </h2>"; 
              }
              //}                 
              else 
                  {
                 echo "<h2>".$uid. " is odd punch </h2>";
                 //print_r($uid);
                }

}

?>

1.I want to odd and even punch,, if odd and even punch means how to i stored in another table.
2.total hours and in and out time how to calculate
so pls Any one help me

It might be easier to do this in code.

What database (MSSQL, MySQL, other) are you using? What you want to achieve in SQL is dependent on which server you are using. You can write a join that connects every record with the next available one easily, but that won't take into account the odd/even issue.

iam using MYSQL 5.0 VERSION

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.