MySQL: Display Users and Duration in Matrix Timesheet

: Joel Lipman; MySQL; 27 April 2012; Hits: 5520

Given Data
Activities by employees with start dates and end dates in a mySQL database.

Objective

User		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
--------------- ------- ------- ------- ------- ------- ------- ------- ---------
Me		09:00	07:30	08:00	07:00	06:00	00:00	02:00   36:00
Myself		07:30	07:30	07:30	07:30	07:00	00:00	00:00   37:00
I		03:45	03:30	03:00	03:30	00:00	00:00	00:00   23:00

User Mon Tue Wed Thu Fri Sat Sun Total
--------------- ------- ------- ------- ------- ------- ------- ------- ---------
Me 09:00 07:30 08:00 07:00 06:00 00:00 02:00 36:00
Myself 07:30 07:30 07:30 07:30 07:00 00:00 00:00 37:00
I 03:45 03:30 03:00 03:30 00:00 00:00 00:00 23:00

Method

copy raw

1. Generate query to list all valid users
2. Generate query which will total hours per week per user
3. Combine in a loop to run query against each user.

1. Generate query to list all valid users
2. Generate query which will total hours per week per user
3. Combine in a loop to run query against each user.

Post-Notes (Update 2012)

The performance of the previous version of this query was pretty abysmal and worse if you want to list users who have no associated activities (outer join but all-in-one query). The DBA responsible for the system asked me to look at ways of reducing the load on the server and the solution was to separate both tables (activities and users) rather than trying to join these, and to let PHP do a bit more of the work:

User Query

copy raw

$t_value = $the_team_id_that_i_want_to_look_at;

$user_list_query="
SELECT DISTINCT
	u.UserID,
	CONCAT(u.FirstName, ' ', u.LastName) AS 'Fullname'
FROM 
	Users u
WHERE
	u.StatusType NOT IN ('expired','guest','test account')
AND 
	u.TeamID='$t_value'
ORDER BY 
	CONCAT(u.FirstName, ' ', u.LastName)
";

$t_value = $the_team_id_that_i_want_to_look_at;
$user_list_query="
SELECT DISTINCT
u.UserID,
CONCAT(u.FirstName, ' ', u.LastName) AS 'Fullname'
FROM
Users u
WHERE
u.StatusType NOT IN ('expired','guest','test account')
AND
u.TeamID='$t_value'
ORDER BY
CONCAT(u.FirstName, ' ', u.LastName)
";

Activity Query

copy raw

$w_value = $number_of_weeks_from_now; 
// usually 0 for this week, 1 for the week before, 2 for the week before that...

$timesheet_query_per_user="
SELECT DISTINCT
	s.StaffUserID AS 'UserID',
	(
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
		FROM StaffActivities s
		WHERE WEEKDAY(s.DateTimeCreated)=0
		AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
		AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
	) AS 'Monday',
	(
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
		FROM StaffActivities s
		WHERE WEEKDAY(s.DateTimeCreated)=1
		AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
		AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
	) AS 'Tuesday',
	(
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
		FROM StaffActivities s
		WHERE WEEKDAY(s.DateTimeCreated)=2
		AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
		AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
	) AS 'Wednesday',
	(
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
		FROM StaffActivities s
		WHERE WEEKDAY(s.DateTimeCreated)=3
		AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
		AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
	) AS 'Thursday',
	(
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
		FROM StaffActivities s
		WHERE WEEKDAY(s.DateTimeCreated)=4
		AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
		AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
	) AS 'Friday',
	(
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
		FROM StaffActivities s
		WHERE WEEKDAY(s.DateTimeCreated)=5
		AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
		AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
	) AS 'Saturday',
	(
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
		FROM StaffActivities s
		WHERE WEEKDAY(s.DateTimeCreated)=6
		AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
		AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
	) AS 'Sunday',
	(
		SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
		FROM StaffActivities s
		WHERE YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
		AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
	) AS 'Total',
	(
		SELECT ROUND((SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))/26640),2) AS SecondsElapsed
		FROM StaffActivities s
		WHERE YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
		AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
	) AS 'Total Days'
FROM StaffActivities s
WHERE s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
";

// POST-NOTES: 
// 26640 is 7 hours 24 minutes (our working day).  
// 4 days times 7:30 and 1 day of 7:00 will result in 37:00 total.
// Our working week starts on Monday 00:00 and ends on Sunday 23:59.

$w_value = $number_of_weeks_from_now;
// usually 0 for this week, 1 for the week before, 2 for the week before that...
$timesheet_query_per_user="
SELECT DISTINCT
s.StaffUserID AS 'UserID',
(
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
FROM StaffActivities s
WHERE WEEKDAY(s.DateTimeCreated)=0
AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
) AS 'Monday',
(
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
FROM StaffActivities s
WHERE WEEKDAY(s.DateTimeCreated)=1
AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
) AS 'Tuesday',
(
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
FROM StaffActivities s
WHERE WEEKDAY(s.DateTimeCreated)=2
AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
) AS 'Wednesday',
(
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
FROM StaffActivities s
WHERE WEEKDAY(s.DateTimeCreated)=3
AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
) AS 'Thursday',
(
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
FROM StaffActivities s
WHERE WEEKDAY(s.DateTimeCreated)=4
AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
) AS 'Friday',
(
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
FROM StaffActivities s
WHERE WEEKDAY(s.DateTimeCreated)=5
AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
) AS 'Saturday',
(
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
FROM StaffActivities s
WHERE WEEKDAY(s.DateTimeCreated)=6
AND YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
) AS 'Sunday',
(
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))) AS SecondsElapsed
FROM StaffActivities s
WHERE YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
) AS 'Total',
(
SELECT ROUND((SUM(TIME_TO_SEC(s.EstimateTimeFinish)-TIME_TO_SEC(s.DateTimeCreated))/26640),2) AS SecondsElapsed
FROM StaffActivities s
WHERE YEARWEEK(s.DateTimeCreated)=YEARWEEK(NOW()) - $w_value
AND s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
) AS 'Total Days'
FROM StaffActivities s
WHERE s.StaffUserID='STRING_TO_REPLACE_PER_ITERATION'
";
// POST-NOTES:
// 26640 is 7 hours 24 minutes (our working day).
// 4 days times 7:30 and 1 day of 7:00 will result in 37:00 total.
// Our working week starts on Monday 00:00 and ends on Sunday 23:59.

Putting it together
Your first query should loop through the users list, within the loop run the activities query and replace the string "STRING_TO_REPLACE_PER_ITERATION" with the User ID. Something like:

copy raw

$result=mysql_query($user_list_query);
while($row=mysql_fetch_assoc($result)) {
	$this_user_id=$row['UserID'];
	$this_user_full=$row['Fullname'];
	$this_user_query=str_replace("STRING_TO_REPLACE_PER_ITERATION", $this_user_id, $timesheet_query_per_user);
	$this_mon=$this_tue=$this_wed=$this_thu=$this_fri=$this_sat=$this_sun=$this_t=$this_t2="";


	$sub_result=mysql_query($this_user_query);
	while($sub_row=mysql_fetch_assoc($sub_result)) {
		$this_mon=$sub_row['Monday'];
		$this_tue=$sub_row['Tuesday'];
		$this_wed=$sub_row['Wednesday'];
		$this_thu=$sub_row['Thursday'];
		$this_fri=$sub_row['Friday'];
		$this_sat=$sub_row['Saturday'];
		$this_sun=$sub_row['Sunday'];
		$this_t=$sub_row['Total'];
		$this_t2=$sub_row['Total Days'];

		echo $this_user_full . "\t" . $this_mon . "\t" . $this_tue . "\t" . $this_wed . "\t" . $this_thu . "\t" . $this_fri . "\t" . $this_sat . "\t" . $this_sun . "\t" . $this_t . "\t" . $this_t2;

                ...do some more here ???...
        }
}

$result=mysql_query($user_list_query);
while($row=mysql_fetch_assoc($result)) {
$this_user_id=$row['UserID'];
$this_user_full=$row['Fullname'];
$this_user_query=str_replace("STRING_TO_REPLACE_PER_ITERATION", $this_user_id, $timesheet_query_per_user);
$this_mon=$this_tue=$this_wed=$this_thu=$this_fri=$this_sat=$this_sun=$this_t=$this_t2="";
$sub_result=mysql_query($this_user_query);
while($sub_row=mysql_fetch_assoc($sub_result)) {
$this_mon=$sub_row['Monday'];
$this_tue=$sub_row['Tuesday'];
$this_wed=$sub_row['Wednesday'];
$this_thu=$sub_row['Thursday'];
$this_fri=$sub_row['Friday'];
$this_sat=$sub_row['Saturday'];
$this_sun=$sub_row['Sunday'];
$this_t=$sub_row['Total'];
$this_t2=$sub_row['Total Days'];
echo $this_user_full . "\t" . $this_mon . "\t" . $this_tue . "\t" . $this_wed . "\t" . $this_thu . "\t" . $this_fri . "\t" . $this_sat . "\t" . $this_sun . "\t" . $this_t . "\t" . $this_t2;
...do some more here ???...
}
}

Yields

copy raw

-- Note this data matches the brief as we've converted the seconds to time.
-- Using MySQL to convert rather than PHP so as not to have to deal with hour changes and the magical 23:00 hour.

-- 2nd Note: We use "WEEKDAY" instead of "DAYOFWEEK" as our working week starts on Monday (Mon=0, Tue=1, Sun=6).  

User		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total	Days
--------------- ------- ------- ------- ------- ------- ------- ------- ------- -----
Me		06:30	08:00	07:30	06:30	07:30	NULL	NULL    36:00   4.86
Myself		NULL	07:30	07:30	07:30	07:30	NULL	NULL    30:00   4.05
I		NULL	NULL	08:00	08:00	08:00	NULL	NULL    24:00   3.24

-- Note this data matches the brief as we've converted the seconds to time.
-- Using MySQL to convert rather than PHP so as not to have to deal with hour changes and the magical 23:00 hour.
-- 2nd Note: We use "WEEKDAY" instead of "DAYOFWEEK" as our working week starts on Monday (Mon=0, Tue=1, Sun=6).
User Mon Tue Wed Thu Fri Sat Sun Total Days
--------------- ------- ------- ------- ------- ------- ------- ------- ------- -----
Me 06:30 08:00 07:30 06:30 07:30 NULL NULL 36:00 4.86
Myself NULL 07:30 07:30 07:30 07:30 NULL NULL 30:00 4.05
I NULL NULL 08:00 08:00 08:00 NULL NULL 24:00 3.24

Category: MySQL :: Article: 416

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Generate Academic Calendar using MySQL (6556) Joel Lipman (MySQL)
What? We have a specific timetabling system for academic institutions and all our staff/students follow academic week...
SQL: Use CASE for Relevance column (3840) Joel Lipman (MySQL)
What? So this is a quick note to myself as I was playing with the relevance heuristics of a query. This example adds a...
MySQL Data Type Reference Table (6449) Joel Lipman (MySQL)
A data type reference table. If you're designing a database then you don't need me to tell you what this is. My...
Error 1065: Query was Empty (7168) Joel Lipman (MySQL)
What? In view of the fact that this error pops up in so many systems I take over, I have often gone down the wrong...
Country Lookup by IP address CSV (7564) Joel Lipman (MySQL)
What? So this is an article to serve as documentation for one of the data sources available to download for FREE from...
MySQL Commands to display all columns (6174) Joel Lipman (MySQL)
The scenario is that I wanted a PHP/MySQL extension created which needs to launch a query to find all columns across...
SQL Queries for Statistics (6222) Joel Lipman (MySQL)
Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all...