Generate a Timesheet in MySQL
- Joel Lipman
- MySQL
- Hits: 2795
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
- 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