[RESOLVED] How do I combine queries on single table to get desired output?

[sunshine66]

Hi,

I want to output data from a table in a specific format, but I don't know how to to do it.

The format I want to output (in an HTML table) is:

Name1 SunTotal MonTotal TueTotal WedTotal ThrTotal FriTotal SatTotal TOTAL

The records in the Timesheets table in the database are:
EmployeeName
WeekEnding (date)
Date
Hours
PayType

Currently, I can only show the Total Weekly Hours for each Employee (as EmployeeName, Total_Hours)

This is my query:

$query2 = "SELECT EmployeeName, Date, SUM(Hours) AS Total_Hours FROM Timesheets WHERE WeekEnding='$Saturday' AND (PayType='1' || PayType='01') GROUP BY EmployeeName";

I can take the value of $Saturday and determine the dates for each day, but I do not know the syntax to combine all the queries to get the results i want

/***********************************************/
// sum total hours for the each day of the week for each employee 
// HOW DO I UPDATE THIS QUERY SO THAT I'M PULLING DAILY TOTALS AND NOT WEEKLY TOTALS? AND CAN DISPLAY IN COLUMNS VS. ROWS?

$query = "SELECT EmployeeName, Date, SUM(Hours) AS Total_Hours FROM Timesheets WHERE WeekEnding='$sat1' AND (PayType='1' || PayType='01') GROUP BY EmployeeName";

$result = mysql_query($query) or die("Error in retrieving record from database (Timesheets table). Contact Tech Support. Error: " . mysql_error() );

while($line = mysql_fetch_array($result))
{
	$Total_Hours 	= $line["Total_Hours"];
	$Date 		= $line["Date"];
	$Employee	= $line["EmployeeName"];

// convert date to day # and day of week
$Datestamp = strtotime($Date);
$Day = date("D", $Datestamp);

//convert day hours	
$Day1Hrs ="0";  $Day2Hrs ="0";  $Day3Hrs ="0";  $Day4Hrs ="0";  $Day5Hrs ="0";  $Day6Hrs ="0";  $Day7Hrs ="0";
 if ( $DayNum == "1") { $Day1Hrs = $Hours ; } else  { $Day1Hrs = "0" ; } 
 if ( $DayNum == "2") { $Day2Hrs = $Hours ; } else  { $Day2Hrs = "0" ; } 
 if ( $DayNum == "3") { $Day3Hrs = $Hours ; } else  { $Day3Hrs = "0" ; } 
 if ( $DayNum == "4") { $Day4Hrs = $Hours ; } else  { $Day4Hrs = "0" ; } 
 if ( $DayNum == "5") { $Day5Hrs = $Hours ; } else  { $Day5Hrs = "0" ; } 
 if ( $DayNum == "6") { $Day6Hrs = $Hours ; } else  { $Day6Hrs = "0" ; } 
 if ( $DayNum == "7") { $Day7Hrs = $Hours ; } else  { $Day7Hrs = "0" ; } 


$tableinfo2 .= "<tr bgcolor=$color>
<td nowrap >$Employee</td>
<td nowrap >$Day1Hours</td>	
<td nowrap >$Day2Hours</td>	
<td nowrap >$Day3Hours</td>	
<td nowrap >$Day4Hours</td>	
<td nowrap >$Day5Hours</td>	
<td nowrap >$Day6Hours</td>	
<td nowrap >$Day7Hours</td>	
<td nowrap align=center>$Total_Hours</td>		
</tr>\n";

}

// code continues with print statements to display output in HTML table

[tomhath]

Use a CASE statement, you'll need one for reach day of the week. Something like this (untested):

SELECT EmployeeName, Date, SUM(Hours) AS Total_Hours,
	SUM (case when DAYOFWEEK(Date) = 1 then Hours else 0 end) as Sunday_Hours ,
	SUM (case when DAYOFWEEK(Date) = 2 then Hours else 0 end) as Monday_Hours 
FROM Timesheets 
WHERE WeekEnding='$Saturday' AND (PayType='1' || PayType='01') 
GROUP BY EmployeeName

[sunshine66]

Cool... I'll play around with it and see if I can get what i need. Thanks for the tip!

[sunshine66]

i got the WHEN condition figured out, but there's something wrong still

$query = "SELECT EmployeeName, Date, SUM(Hours) AS Total_Hours, 
SUM (CASE WHEN Date = $Day1Date THEN Hours ELSE 0 END) as Sunday_Hours,
SUM (CASE WHEN Date = $Day2Date THEN Hours ELSE 0 END) as Monday_Hours
FROM Timesheets 
WHERE WeekEnding='$Saturday' AND (PayType='1' || PayType='01') 
GROUP BY EmployeeName " 
or die("Error in query Error: " . mysql_error() );

$result = mysql_query($query) or die("Error: " . mysql_error() );

Results in an syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CASE WHEN Date = 2008-01-31 THEN Hours ELSE 0 END) as Sunday_Hours, SUM (C' at line 2

i have no clue what the proper syntax is.

help!

[sunshine66]

okay got the syntax. complete query is:

$query = "
SELECT EmployeeName, Date, SUM(Hours) AS Total_Hours, 
    SUM(CASE WHEN Date = $Day1Date THEN Hours ELSE 0 END) as Sun_Hours, 
    SUM(CASE WHEN Date = $Day2Date THEN Hours ELSE 0 END) as Mon_Hours,
    SUM(CASE WHEN Date = $Day3Date THEN Hours ELSE 0 END) as Tue_Hours,
    SUM(CASE WHEN Date = $Day4Date THEN Hours ELSE 0 END) as Wed_Hours,
    SUM(CASE WHEN Date = $Day5Date THEN Hours ELSE 0 END) as Thr_Hours,
    SUM(CASE WHEN Date = $Day6Date THEN Hours ELSE 0 END) as Fri_Hours,
    SUM(CASE WHEN Date = $Day6Date THEN Hours ELSE 0 END) as Sat_Hours		
FROM Timesheets 
WHERE WeekEnding='$sat1' AND BatchNum='$Batch' AND (PayType='1' || PayType='01') 
GROUP BY EmployeeName " 
or die("Error in query Error: " . mysql_error() );
$result = mysql_query($query) 
or die("Contact Tech Support. Error in retrieving summary record: " . mysql_error() );

print "query = $query<br>";

query = SELECT EmployeeName, Date, SUM(Hours) AS Total_Hours,
SUM(CASE WHEN Date = 2008-01-31 THEN Hours ELSE 0 END) as Sun_Hours,
SUM(CASE WHEN Date = 2008-02-01 THEN Hours ELSE 0 END) as Mon_Hours,
SUM(CASE WHEN Date = 2008-02-02 THEN Hours ELSE 0 END) as Tue_Hours,
SUM(CASE WHEN Date = 2008-02-03 THEN Hours ELSE 0 END) as Wed_Hours, SUM(CASE WHEN Date = 2008-02-04 THEN Hours ELSE 0 END) as Thr_Hours,
SUM(CASE WHEN Date = 2008-02-05 THEN Hours ELSE 0 END) as Fri_Hours,
SUM(CASE WHEN Date = 2008-02-06 THEN Hours ELSE 0 END) as Sat_Hours
FROM Timesheets
WHERE WeekEnding='2008-02-09' AND BatchNum='' AND (PayType='1' || PayType='01')
GROUP BY EmployeeName

However, I am getting 0 for Sun - Sat hours, instead of 8
The Employee and Total Hours prints out correctly

while($line = mysql_fetch_array($result))
{
	$Total_Hours= $line["Total_Hours"];
	$Employee	= $line["EmployeeName"];
	$Day1Hours 	= $line["Sun_Hours"];
	$Day2Hours 	= $line["Mon_Hours"];
	$Day3Hours 	= $line["Tue_Hours"];
	$Day4Hours 	= $line["Wed_Hours"];
	$Day5Hours 	= $line["Thr_Hours"];
	$Day6Hours 	= $line["Fri_Hours"];
	$Day7Hours 	= $line["Sat_Hours"];
}

[tomhath]

Shouldn't you have quotes around the dates?

SUM(CASE WHEN Date = '$Day1Date'

instead of

SUM(CASE WHEN Date = $Day1Date

[sunshine66]

Yup! That did the trick!

Here is the final query:

$query = "
SELECT EmployeeName, Date, SUM(Hours) AS Total_Hours, 

SUM(CASE WHEN Date = '$Day1Date' AND (PayType='1' || PayType='01') THEN Hours ELSE NULL END) as Sun_Hours,
SUM(CASE WHEN Date = '$Day2Date' AND (PayType='1' || PayType='01') THEN Hours ELSE NULL END) as Mon_Hours,
SUM(CASE WHEN Date = '$Day3Date' AND (PayType='1' || PayType='01') THEN Hours ELSE NULL END) as Tue_Hours,
SUM(CASE WHEN Date = '$Day4Date' AND (PayType='1' || PayType='01') THEN Hours ELSE NULL END) as Wed_Hours,
SUM(CASE WHEN Date = '$Day5Date' AND (PayType='1' || PayType='01') THEN Hours ELSE NULL END) as Thr_Hours,
SUM(CASE WHEN Date = '$Day6Date' AND (PayType='1' || PayType='01') THEN Hours ELSE NULL END) as Fri_Hours,
SUM(CASE WHEN Date = '$Day7Date' AND (PayType='1' || PayType='01') THEN Hours ELSE NULL END) as Sat_Hours,		

SUM(CASE WHEN Date = '$Day1Date' AND (PayType='2' || PayType='02') THEN Hours ELSE NULL END) as Sun_OT,
SUM(CASE WHEN Date = '$Day2Date' AND (PayType='2' || PayType='02') THEN Hours ELSE NULL END) as Mon_OT,
SUM(CASE WHEN Date = '$Day3Date' AND (PayType='2' || PayType='02') THEN Hours ELSE NULL END) as Tue_OT,
SUM(CASE WHEN Date = '$Day4Date' AND (PayType='2' || PayType='02') THEN Hours ELSE NULL END) as Wed_OT,
SUM(CASE WHEN Date = '$Day5Date' AND (PayType='2' || PayType='02') THEN Hours ELSE NULL END) as Thr_OT,
SUM(CASE WHEN Date = '$Day6Date' AND (PayType='2' || PayType='02') THEN Hours ELSE NULL END) as Fri_OT,
SUM(CASE WHEN Date = '$Day7Date' AND (PayType='2' || PayType='02') THEN Hours ELSE NULL END) as Sat_OT		

FROM Timesheets 
WHERE WeekEnding='$Day7Date' AND BatchNum='$Batch'  

GROUP BY EmployeeName "