Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Change syntax from Update Case Statement to Update Union Statement

syntax

  • Please log in to reply
12 replies to this topic

#1 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 10 November 2011 - 05:30 PM

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
  • 0

#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 10 November 2011 - 06:44 PM

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

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 10 November 2011 - 10:44 PM

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

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

Thank you
  • 0

#4 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 11 November 2011 - 08:30 AM

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.
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#5 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 13 November 2011 - 03:42 PM

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
  • 0

#6 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 14 November 2011 - 08:18 AM

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

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#7 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 14 November 2011 - 03:59 PM

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
  • 0

#8 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 15 November 2011 - 09:01 AM

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?
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#9 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 15 November 2011 - 03:59 PM

my database type is mysql

the data is from the upload .xml file

Thank you
  • 0

#10 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 15 November 2011 - 07:15 PM

For those with incorrect output, what are they displaying?
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#11 newphpcoder

newphpcoder

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 527 posts

Posted 15 November 2011 - 09:57 PM

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....
  • 0

#12 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 16 November 2011 - 12:39 PM

What does this give you?

SELECT Rendered, TimeIn, TimeOut, shift from attendance;

  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/






Also tagged with one or more of these keywords: syntax

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download