What?
This article is to remind me how to create a blank weekly timesheet which reads the duration of events from a database and auto-completes your timesheet.

Why?
I'm being tasked to work with EPM (Microsoft Enterprise Project Management) more and more. Similar systems have popped out that support some form of time recording and activity logging. The example below however is within a LAMP/MySQL environment but the SQL basics are here to help me adapt it to whatever environment people keep throwing at me.

What I want: 
ThisDate    ThisDay     StartTime  TimeOut  TimeIn  EndTime  TotalTimeToday TotalTimeWeek
----------- ----------- ---------- -------- ------- -------- -------------- -------------
2013-12-02  Monday      09:00      12:00    13:00   17:00    7:00           7:00
2013-12-03  Tuesday     08:45      12:00    13:30   17:45    7:30           14:30
2013-12-04  Wednesday   09:00      12:30    13:30   17:00    7:00           21:30
2013-12-05  Thursday    10:00      12:15    12:45   17:15    7:45           29:15
2013-12-06  Friday      07:00      12:00    13:00   16:30    8:30           37:45
2013-12-07  Saturday    -          -        -       -        0:00           37:45
2013-12-08  Sunday      03:00      04:00    -       -        4:00           41:45
Some Requirements;
  • StartTime will be the earliest someone starts work that day.
  • EndTime will be the latest someone works that day.
  • Time is recorded by tasks with an actual duration in seconds (eg. "Monday morning, I worked 45 minutes on setting up a Joomla 2.5.x demo site; I started at 9am and finished at 9:45.", "Monday morning, I also worked on creating a template for the site I just made; I started at 9:45 and finished at 12:00")
  • The final system needs to specify every day of the week and divide all the days activities into the morning and afternoon slots.
  • All days and time slots must be listed irrespective of whether any time has been associated to them.
  • We need to be able to specify any date, and the system returns the timesheet for the week that date occurs in.

How?
The example below shows how to do this when your weeks being/commence on Monday. It also uses the current date in the form of NOW() but this example has to work with any given date.

First of all: Get this week's dates and days: 
SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDay

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDay

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDay

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDay

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDay

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDay

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDay


-- yields

-- ThisDate    ThisDay
-- ----------- --------------
-- 2013-12-02  Monday
-- 2013-12-03  Tuesday
-- 2013-12-04  Wednesday
-- 2013-12-05  Thursday
-- 2013-12-06  Friday
-- 2013-12-07  Saturday
-- 2013-12-08  Sunday
Brilliant! We have the first two columns of what we want to do.

Now we need the same again but this time we want the morning slots
SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) )


-- yields
-- ThisDate    ThisDay   StartTime   TimeOut
-- 2013-12-02  Monday    09:00:00    09:30:00
-- 2013-12-05  Thursday  11:00:00    11:50:00
-- 2013-12-05  Thursday  10:00:00    11:30:00
-- 2013-12-06  Friday    07:00:00    09:00:00
This example is using a small table with very little sample data so do not expect it to reconcile with the requirement example. Note that we have two events on the Thursday.

A few things we have to deal with still:
  • We have lost the rows with days that have no events.
  • We need to group by days so that each day only outputs one row (see Thursday).
  • We want the earliest time and the latest time per morning and afternoon slot.

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) )


UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDay,
	TIME( w.date_started ) AS StartTime,
	TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut
FROM
	jdlv2_joestimesheets_works w
WHERE
	DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) )


-- now re-add the day slots

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDay,
	'-',
	'-'

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDay,
	'-',
	'-'

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDay,
	'-',
	'-'

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDay,
	'-',
	'-'

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDay,
	'-',
	'-'

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDay,
	'-',
	'-'

UNION ALL

SELECT
	DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDate,
	DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDay,
	'-',
	'-'


-- yields
-- ThisDate    ThisDay    StartTime   TimeOut
-- ----------- ---------- ----------- -----------
-- 2013-12-02  Monday     09:00:00    09:30:00
-- 2013-12-05  Thursday   11:00:00    11:50:00
-- 2013-12-05  Thursday   10:00:00    11:30:00
-- 2013-12-06  Friday     07:00:00    09:00:00
-- 2013-12-02  Monday     -           -
-- 2013-12-03  Tuesday    -           -
-- 2013-12-04  Wednesday  -           -
-- 2013-12-05  Thursday   -           -
-- 2013-12-06  Friday     -           -
-- 2013-12-07  Saturday   -           -
-- 2013-12-08  Sunday     -           -