12 replies to this topic

[newphpcoder]

Good day!

Theirs a programmer told me that could I used Union Statement to solve my issue in my query, but i can't imagine how can I do that, because my query I used case statement, I have no idea how can I used union instead of case statement.

Here is my problem, I need to get the rendered hours and the rendered hours should be only 8 hours or less 8 hours if the employee was time in late.

I have 3 shifts 21:35 - 05:35, 05:35- 13:35, 13:35-21:35

using case statement it works only in one shift, I want to get the rendered with different scheduled.

Like for example :
Employee 1 time in = 21:00 time out = 05:40 the rendered = 8 hours only event he time in early and time out late it only compute the 21:35 - 05:35
Employee 2 time in = 06:35 time out = 13:50 the rendered should be = 7 hours only because he was late 1 hour because his schedule is 05:35 but he time in 06:35 even he time out late its not considered because only time from 05:35-13:35 will get
Employee 3 time in = 13:35 time out = 24:35 the rendered should be = 8 only even he timeout late.

Rendered should be compute only the 8 hours of work, no matter he time out late only 8 hours would be get based on their schedule. and subtrace if he time in late.

here is my syntax in case statement:

UPDATE employee SET rendered = sec_to_time(time_to_sec('08:00:00') + 

            case 

              when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0

              else time_to_sec('21:35:00') - time_to_sec(time(timein))

            end +

            case 

              when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0

              else time_to_sec(time(timeout)) - time_to_sec('05:35:00')

            end;

it is only for one shift and it works, but when I tried this to get the rendered for 3 shifts:

UPDATE employee SET rendered = sec_to_time(time_to_sec('08:00:00') + 

            case 

              when time_to_sec(time(timein)) < time_to_sec('21:35:00') then 0

              else time_to_sec('21:35:00') - time_to_sec(time(timein))

            end +

            case 

              when time_to_sec(time(timeout)) > time_to_sec('05:35:00') then 0

              else time_to_sec(time(timeout)) - time_to_sec('05:35:00')

            end +

case 

              when time_to_sec(time(timein)) < time_to_sec('05:35:00') then 0

              else time_to_sec('05:35:00') - time_to_sec(time(timein))

            end +

            case 

              when time_to_sec(time(timeout)) > time_to_sec('13:35:00') then 0

              else time_to_sec(time(timeout)) - time_to_sec('13:35:00')

            end +

	case 

              when time_to_sec(time(timein)) < time_to_sec('13:35:00') then 0

              else time_to_sec('13:35:00') - time_to_sec(time(timein))

            end +

            case 

              when time_to_sec(time(timeout)) > time_to_sec('21:35:00') then 0

              else time_to_sec(time(timeout)) - time_to_sec('21:35:00')

            end

);

wrong output was displayed.

If UNION Statement is the solution? How? Thank you

Is there any query can i used? I really need to solved this .

Any help is highly appreciated.

Thank you so much

[WingedPanther]

UNION is used to merge result sets in a SELECT statement.

[newphpcoder]

WingedPanther said:

UNION is used to merge result sets in a SELECT statement.

So you mean, its not the aolution in my problem?

Thank you

[WingedPanther]

Correct.

Dealing with your issue, it's not clear what you're trying to do, but that may be due to not understanding what your table schema is. I have a feeling that adding a shift column would make your calculations MUCH easier to deal with.

[newphpcoder]

WingedPanther said:

Correct.

Dealing with your issue, it's not clear what you're trying to do, but that may be due to not understanding what your table schema is. I have a feeling that adding a shift column would make your calculations MUCH easier to deal with.

I tried this code, can you check it if it is correct?

UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 

            case 

              when time_to_sec(time(TimeIn)) < time_to_sec('05:35:00') then 0

              else time_to_sec('05:35:00') - time_to_sec(time(TimeIn))

            end +

            case 

              when time_to_sec(time(TimeOut)) > time_to_sec('13:35:00') then 0

              else time_to_sec(time(TimeOut)) - time_to_sec('13:35:00')

            end

           ) WHERE shift = 2;

UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 

            case 

              when time_to_sec(time(TimeIn)) < time_to_sec('21:35:00') then 0

              else time_to_sec('21:35:00') - time_to_sec(time(TimeIn))

            end +

            case 

              when time_to_sec(time(TimeOut)) > time_to_sec('05:35:00') then 0

              else time_to_sec(time(TimeOut)) - time_to_sec('05:35:00')

            end

           ) WHERE shift = 1;

UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 

            case 

              when time_to_sec(time(TimeIn)) < time_to_sec('13:35:00') then 0

              else time_to_sec('13:35:00') - time_to_sec(time(TimeIn))

            end +

            case 

              when time_to_sec(time(TimeOut)) > time_to_sec('21:35:00') then 0

              else time_to_sec(time(TimeOut)) - time_to_sec('21:35:00')

            end

           ) WHERE shift = 3;

UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 

            case 

              when time_to_sec(time(TimeIn)) < time_to_sec('08:00:00') then 0

              else time_to_sec('08:00:00') - time_to_sec(time(TimeIn))

            end +

            case 

              when time_to_sec(time(TimeOut)) > time_to_sec('17:00:00') then 0

              else time_to_sec(time(TimeOut)) - time_to_sec('17:00:00')

            end

           ) WHERE shift = 4;

Thank you so much

[WingedPanther]

Actually, I can't. I would need to know your table structure, database type, etc. Have you run some tests to verify it?

[newphpcoder]

here is my table structure:

table- attendance
EMP_ID varchar
DateAtt date
TimeIn datetime
TimeOut datetime
TotalHours time
Rendered time
OT time

I test it and only 05:35 - 13:35 has correct output.
I need to solved it because its urgent.

Thank you so much

[WingedPanther]

I need to know your database type, too. Oracle, SQL Server, Access, MySQL, Firebird, PostGreSQL, other? Each one has slightly different syntax.

For those with incorrect output, what are they displaying?

[newphpcoder]

my database type is mysql

the data is from the upload .xml file

Thank you

[WingedPanther]

For those with incorrect output, what are they displaying?

[newphpcoder]

output in rendered???

Here is my syntax for importing data from .xml to database and I also the updating and insert data based on the import attendance.

<?php

$data = array();


$con = mysql_connect("localhost", "root","");

if (!$con) { 

  die(mysql_error());

}

$db = mysql_select_db("payroll", $con);

if (!$db) { 

  die(mysql_error());

}


$sql = "select * from attendance";

$result =  mysql_query($sql, $con);

if (!$result) {

    die(mysql_error());

}

$total = mysql_num_rows($result);

if ($total > 0) {

    $sql = "delete from attendance";

    $result =  mysql_query($sql, $con);

    if (!$result) {

        die(mysql_error());

    }

}


function add_employee($EMP_NO, $Date, $TimeIn, $TimeOut)

  {

      global $data; 

      

      $con = mysql_connect("localhost", "root","");

      if (!$con){ die(mysql_error());}

      $db = mysql_select_db("payroll", $con);

      if (!$db) { 

          die(mysql_error());

      }


      $EMP_NO = $EMP_NO;

      $Date = $Date;

      $Date = substr($Date,0,-13);

      $TimeIn = $TimeIn;

      $TimeOut = $TimeOut;

  



     $sql = "INSERT INTO attendance (EMP_NO, DateAtt, TimeIn, TimeOut) VALUES ('$EMP_NO', '$Date', '$TimeIn', '$TimeOut')";

      mysql_query($sql, $con);

      


      $data []= array('EMP_NO' => $EMP_NO, 'DateAtt' => $Date, 'TimeIn' => $TimeIn, 'TimeOut' => $TimeOut);

      

  }

  

  if ( $_FILES['file']['tmp_name'] )

  {

      $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );

         

      $rows = $dom->getElementsByTagName( 'Row' );

      global $last_row;

      $last_row = false;

      $first_row = true;

      foreach ($rows as $row)

      {

          if ( !$first_row )

          {

            

              $EMP_NO = "";

              $Date = "";

              $TimeIn = "";

              $TimeOut = "";


              

              

              $index = 1;

              $cells = $row->getElementsByTagName( 'Cell' );

          

              foreach( $cells as $cell )

              { 

                  $ind = $cell->getAttribute( 'Index' );

                  if ( $ind != null ) $index = $ind;

                  

                  if ( $index == 1 ) $EMP_NO = $cell->nodeValue;

                  if ( $index == 2 ) $Date = $cell->nodeValue;

                  if ( $index == 3 ) $TimeIn = $cell->nodeValue;

                  if ( $index == 4 ) $TimeOut = $cell->nodeValue;

                  $index += 1;

              }


             if ($EMP_NO=='' and $Date=='' and $TimeIn=='' and $TimeOut=='') {

                    $last_row = true;

              }      

              else {


                    add_employee($EMP_NO, $Date, $TimeIn, $TimeOut);



              }      

          }

          if ($last_row==true) {

              $first_row = true;

          }     

          else {

              $first_row = false;

          }

      }

  }

 

 //Update Total Hours  

$result = mysql_query("UPDATE attendance SET TotalHours = sec_to_time(unix_timestamp(TimeOut) - unix_timestamp(TimeIn))") 

 or die(mysql_error());  

 

  $result = mysql_query("UPDATE attendance SET TotalHours = sec_to_time(unix_timestamp(TimeIn) - unix_timestamp(TimeOut)) WHERE Shift = 1") 

 or die(mysql_error());  

//Update Rendered 

    $result = mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 

            case 

              when time_to_sec(time(TimeIn)) < time_to_sec('05:35:00') then 0

              else time_to_sec('05:35:00') - time_to_sec(time(TimeIn))

            end +

            case 

              when time_to_sec(time(TimeOut)) > time_to_sec('13:35:00') then 0

              else time_to_sec(time(TimeOut)) - time_to_sec('13:35:00')

            end

           )")

           or die(mysql_error());

   

   $result = mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 

            case 

              when time_to_sec(time(TimeIn)) < time_to_sec('21:35:00') then 0

              else  time_to_sec('21:35:00') - time_to_sec(time(TimeIn))

            end +

            case 

                when time_to_sec(time(TimeOut)) > time_to_sec('05:35:00') then 0

                else time_to_sec('05:35:00') - time_to_sec(time(TimeOut))

                end

           )")

           or die(mysql_error());  

           

 

 //Update OT

 $result = mysql_query("UPDATE attendance SET OT = sec_to_time(time_to_sec(TotalHours) - time_to_sec(Rendered))") 

 or die(mysql_error());  

 

 //Insert Sum of Total hours

  $result = mysql_query("INSERT INTO earnings(EMP_NO, Hours) SELECT EMP_NO, sec_to_time(SUM(time_to_sec(Rendered))) FROM attendance GROUP BY EMP_NO") 

 or die(mysql_error()); 

 


  ?>

  

  <html>

  <body>

  <table>

  <tr>

      <th>Employee Attendance</th>

  </tr>


  <?php foreach( $data as $row ) { ?>

  <tr>

  <td><?php echo( $row['EMP_NO'] ); ?></td> 

  <td><?php echo( $row['DateAtt'] ); ?></td> 

  <td><?php echo( $row['TimeIn'] ); ?></td>

  <td><?php echo( $row['TimeOut'] ); ?></td>

  </tr>

  <?php } ?>

  </table>

  </body>

 </html>

I have the problem in 21:35:00 - 05:35:00
like for example:

timein 2011-10-25 21:25:00
timeout 2011-10-26 04:35:00
the output using the query:
rendered: 09:00:00
it should be: 07:00:00 because he timeout early...

and when I add this query for shifts: 13:35:00 - 21:35:00 some output in 05:35:00- 13:35:00 , and 21:35:00-05:35:00 is wrong:

$result = mysql_query("UPDATE attendance SET Rendered = sec_to_time(time_to_sec('08:00:00') + 

             case 

              when time_to_sec(time(TimeIn)) < time_to_sec('13:35:00') then 0

              else time_to_sec('13:35:00') - time_to_sec(time(TimeIn))

            end +

            case 

              when time_to_sec(time(TimeOut)) > time_to_sec('21:35:00') then 0

              else time_to_sec('21:35:00') - time_to_sec(time(TimeOut))

            end 

           )") 

 or die(mysql_error()); 

Thank you

---------- Post added at 05:57 AM ---------- Previous post was at 05:15 AM ----------

I really don't know what would be the solution in my problem in shifts.

Thank you so much for your help....

[WingedPanther]

What does this give you?

SELECT Rendered, TimeIn, TimeOut, shift from attendance;