2. Home
  3. Articles
  4. Database
  5. MySQL
  6. Generate a Timesheet in MySQL

Generate a Timesheet in MySQL

MySQL
Hits: 3153
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:
copyraw 
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
  1.  ThisDate    ThisDay     StartTime  TimeOut  TimeIn  EndTime  TotalTimeToday TotalTimeWeek 
  2.  ----------- ----------- ---------- -------- ------- -------- -------------- ------------- 
  3.  2013-12-02  Monday      09:00      12:00    13:00   17:00    7:00           7:00 
  4.  2013-12-03  Tuesday     08:45      12:00    13:30   17:45    7:30           14:30 
  5.  2013-12-04  Wednesday   09:00      12:30    13:30   17:00    7:00           21:30 
  6.  2013-12-05  Thursday    10:00      12:15    12:45   17:15    7:45           29:15 
  7.  2013-12-06  Friday      07:00      12:00    13:00   16:30    8:30           37:45 
  8.  2013-12-07  Saturday    -          -        -       -        0:00           37:45 
  9.  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:
copyraw 
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
  1.  SELECT 
  2.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDate, 
  3.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDay 
  4.   
  5.  UNION ALL 
  6.   
  7.  SELECT 
  8.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDate, 
  9.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDay 
  10.   
  11.  UNION ALL 
  12.   
  13.  SELECT 
  14.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDate, 
  15.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDay 
  16.   
  17.  UNION ALL 
  18.   
  19.  SELECT 
  20.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDate, 
  21.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDay 
  22.   
  23.  UNION ALL 
  24.   
  25.  SELECT 
  26.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDate, 
  27.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDay 
  28.   
  29.  UNION ALL 
  30.   
  31.  SELECT 
  32.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDate, 
  33.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDay 
  34.   
  35.  UNION ALL 
  36.   
  37.  SELECT 
  38.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDate, 
  39.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDay 
  40.   
  41.   
  42.  -- yields 
  43.   
  44.  -- ThisDate    ThisDay 
  45.  -- ----------- -------------- 
  46.  -- 2013-12-02  Monday 
  47.  -- 2013-12-03  Tuesday 
  48.  -- 2013-12-04  Wednesday 
  49.  -- 2013-12-05  Thursday 
  50.  -- 2013-12-06  Friday 
  51.  -- 2013-12-07  Saturday 
  52.  -- 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
copyraw 
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
  1.  SELECT 
  2.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDate, 
  3.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDay, 
  4.      TIME( w.date_started ) AS StartTime, 
  5.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  6.  FROM 
  7.      jdlv2_joestimesheets_works w 
  8.  WHERE 
  9.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) 
  10.   
  11.   
  12.  UNION ALL 
  13.   
  14.  SELECT 
  15.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDate, 
  16.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDay, 
  17.      TIME( w.date_started ) AS StartTime, 
  18.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  19.  FROM 
  20.      jdlv2_joestimesheets_works w 
  21.  WHERE 
  22.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) 
  23.   
  24.   
  25.  UNION ALL 
  26.   
  27.  SELECT 
  28.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDate, 
  29.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDay, 
  30.      TIME( w.date_started ) AS StartTime, 
  31.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  32.  FROM 
  33.      jdlv2_joestimesheets_works w 
  34.  WHERE 
  35.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) 
  36.   
  37.   
  38.  UNION ALL 
  39.   
  40.  SELECT 
  41.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDate, 
  42.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDay, 
  43.      TIME( w.date_started ) AS StartTime, 
  44.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  45.  FROM 
  46.      jdlv2_joestimesheets_works w 
  47.  WHERE 
  48.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) 
  49.   
  50.   
  51.  UNION ALL 
  52.   
  53.  SELECT 
  54.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDate, 
  55.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDay, 
  56.      TIME( w.date_started ) AS StartTime, 
  57.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  58.  FROM 
  59.      jdlv2_joestimesheets_works w 
  60.  WHERE 
  61.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) 
  62.   
  63.   
  64.  UNION ALL 
  65.   
  66.  SELECT 
  67.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDate, 
  68.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDay, 
  69.      TIME( w.date_started ) AS StartTime, 
  70.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  71.  FROM 
  72.      jdlv2_joestimesheets_works w 
  73.  WHERE 
  74.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) 
  75.   
  76.   
  77.  UNION ALL 
  78.   
  79.  SELECT 
  80.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDate, 
  81.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDay, 
  82.      TIME( w.date_started ) AS StartTime, 
  83.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  84.  FROM 
  85.      jdlv2_joestimesheets_works w 
  86.  WHERE 
  87.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) 
  88.   
  89.   
  90.  -- yields 
  91.  -- ThisDate    ThisDay   StartTime   TimeOut 
  92.  -- 2013-12-02  Monday    09:00:00    09:30:00 
  93.  -- 2013-12-05  Thursday  11:00:00    11:50:00 
  94.  -- 2013-12-05  Thursday  10:00:00    11:30:00 
  95.  -- 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.

copyraw 
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     -           -
  1.  SELECT 
  2.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDate, 
  3.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDay, 
  4.      TIME( w.date_started ) AS StartTime, 
  5.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  6.  FROM 
  7.      jdlv2_joestimesheets_works w 
  8.  WHERE 
  9.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) 
  10.   
  11.   
  12.  UNION ALL 
  13.   
  14.  SELECT 
  15.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDate, 
  16.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDay, 
  17.      TIME( w.date_started ) AS StartTime, 
  18.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  19.  FROM 
  20.      jdlv2_joestimesheets_works w 
  21.  WHERE 
  22.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) 
  23.   
  24.   
  25.  UNION ALL 
  26.   
  27.  SELECT 
  28.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDate, 
  29.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDay, 
  30.      TIME( w.date_started ) AS StartTime, 
  31.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  32.  FROM 
  33.      jdlv2_joestimesheets_works w 
  34.  WHERE 
  35.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) 
  36.   
  37.   
  38.  UNION ALL 
  39.   
  40.  SELECT 
  41.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDate, 
  42.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDay, 
  43.      TIME( w.date_started ) AS StartTime, 
  44.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  45.  FROM 
  46.      jdlv2_joestimesheets_works w 
  47.  WHERE 
  48.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) 
  49.   
  50.   
  51.  UNION ALL 
  52.   
  53.  SELECT 
  54.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDate, 
  55.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDay, 
  56.      TIME( w.date_started ) AS StartTime, 
  57.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  58.  FROM 
  59.      jdlv2_joestimesheets_works w 
  60.  WHERE 
  61.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) 
  62.   
  63.   
  64.  UNION ALL 
  65.   
  66.  SELECT 
  67.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDate, 
  68.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDay, 
  69.      TIME( w.date_started ) AS StartTime, 
  70.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  71.  FROM 
  72.      jdlv2_joestimesheets_works w 
  73.  WHERE 
  74.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) 
  75.   
  76.   
  77.  UNION ALL 
  78.   
  79.  SELECT 
  80.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDate, 
  81.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDay, 
  82.      TIME( w.date_started ) AS StartTime, 
  83.      TIME( DATE_ADD( w.date_started , INTERVAL w.actual_duration SECOND ) ) AS TimeOut 
  84.  FROM 
  85.      jdlv2_joestimesheets_works w 
  86.  WHERE 
  87.      DATE(w.date_started)=DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) 
  88.   
  89.   
  90.  -- now re-add the day slots 
  91.   
  92.  UNION ALL 
  93.   
  94.  SELECT 
  95.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDate, 
  96.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) DAY ) ) AS ThisDay, 
  97.      '-', 
  98.      '-' 
  99.   
  100.  UNION ALL 
  101.   
  102.  SELECT 
  103.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDate, 
  104.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 1 DAY ) ) AS ThisDay, 
  105.      '-', 
  106.      '-' 
  107.   
  108.  UNION ALL 
  109.   
  110.  SELECT 
  111.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDate, 
  112.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 2 DAY ) ) AS ThisDay, 
  113.      '-', 
  114.      '-' 
  115.   
  116.  UNION ALL 
  117.   
  118.  SELECT 
  119.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDate, 
  120.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 3 DAY ) ) AS ThisDay, 
  121.      '-', 
  122.      '-' 
  123.   
  124.  UNION ALL 
  125.   
  126.  SELECT 
  127.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDate, 
  128.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 4 DAY ) ) AS ThisDay, 
  129.      '-', 
  130.      '-' 
  131.   
  132.  UNION ALL 
  133.   
  134.  SELECT 
  135.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDate, 
  136.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 5 DAY ) ) AS ThisDay, 
  137.      '-', 
  138.      '-' 
  139.   
  140.  UNION ALL 
  141.   
  142.  SELECT 
  143.      DATE( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDate, 
  144.      DAYNAME( DATE_ADD( NOW( ) , INTERVAL 0 - WEEKDAY( NOW() ) + 6 DAY ) ) AS ThisDay, 
  145.      '-', 
  146.      '-' 
  147.   
  148.   
  149.  -- yields 
  150.  -- ThisDate    ThisDay    StartTime   TimeOut 
  151.  -- ----------- ---------- ----------- ----------- 
  152.  -- 2013-12-02  Monday     09:00:00    09:30:00 
  153.  -- 2013-12-05  Thursday   11:00:00    11:50:00 
  154.  -- 2013-12-05  Thursday   10:00:00    11:30:00 
  155.  -- 2013-12-06  Friday     07:00:00    09:00:00 
  156.  -- 2013-12-02  Monday     -           - 
  157.  -- 2013-12-03  Tuesday    -           - 
  158.  -- 2013-12-04  Wednesday  -           - 
  159.  -- 2013-12-05  Thursday   -           - 
  160.  -- 2013-12-06  Friday     -           - 
  161.  -- 2013-12-07  Saturday   -           - 
  162.  -- 2013-12-08  Sunday     -           - 
Category: MySQL :: Article: 541

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

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.