locked
Table Design for hours worked? RRS feed

  • Question

  • I've spent hours trying to wrap my head around this, and am getting nowhere, so I'm asking for help from the experts.

    I need to store hours worked in a table. I plan to total up hours worked for given periods of time (day/week/month/year). My question is, should I create a table structure like below, or is there a better way?

                            Day1   Day2   Day3   ...   Day 363   Day 364   Day 365
    Employee1       6.75    0.00    12
    Employee2       0.00    0.00    8
    Employee3       5.00    9.00    8
    ...
    Employee50

    365 columns seems like a bad idea, but I don't know another way to store the data.

    Chris

    Wednesday, December 28, 2011 5:01 PM

Answers

  • 365 columns seems like a bad idea, but I don't know another way to store the data.


    Another approach is to create a separate table to store daily hours worked per employee as separate rows rather than columns. This will allow you to report a summary of hours worked by employee for any ad-hoc time period.  Below is an example. 

     

    CREATE TABLE dbo.Employee(
    	EmployeeID varchar(20) NOT NULL
    		CONSTRAINT PK_Employee PRIMARY KEY
    	,FirstName varchar(30) NOT NULL
    	,LastName varchar(30) NOT NULL
    	,MiddleName varchar(30) NULL
    	);
    
    CREATE TABLE dbo.EmployeeTimeCard(
    	EmployeeID varchar(20) NOT NULL
    	,DateWorked date NOT NULL
    	,HoursWorked decimal(4,2)
    	,CONSTRAINT PK_EmployeeTimeCard PRIMARY KEY(
    		EmployeeID
    		,DateWorked)
    	,CONSTRAINT FK_EmployeeTimeCard_Employee FOREIGN KEY(EmployeeID)
    		REFERENCES dbo.Employee(EmployeeID)
    	);
    GO
    
    INSERT INTO dbo.Employee VALUES('Employee1', 'Employee1FirstName', 'Employee1LastName', 'Employee1MiddleName');
    INSERT INTO dbo.Employee VALUES('Employee2', 'Employee2FirstName', 'Employee3LastName', NULL);
    INSERT INTO dbo.Employee VALUES('Employee3', 'Employee2FirstName', 'Employee3LastName', 'Employee3MiddleName');
    
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee1', '20110101', 6.25);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee1', '20110102', 0.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee1', '20110103', 12.00);
    
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee2', '20110101', 0.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee2', '20110102', 0.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee2', '20110103', 8.00);
    
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee3', '20110101', 5.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee3', '20110102', 9.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee3', '20110103', 8.00);
    GO
    
    --hours by month summary
    SELECT 
    	e.EmployeeID
    	,DATEADD(month, DATEDIFF(month, '19000101', etc.DateWorked), '19000101') AS MonthStartDate
    	,SUM(etc.HoursWorked) AS HoursWorked
    FROM dbo.Employee AS e
    JOIN dbo.EmployeeTimeCard AS etc ON
    	etc.EmployeeID = e.EmployeeID
    GROUP BY 
    	DATEADD(month, DATEDIFF(month, '19000101', etc.DateWorked), '19000101')
    	,e.EmployeeID
    ORDER BY 
    	MonthStartDate
    	,e.EmployeeID;
    

    If you don't have time card data for days not worked, you could use a calendar table and LEFT JOIN in order to report zero hours when no timecard data exists for the reporting period.  An example for such a query using the calendar table example at http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx:

     

    --hours by month summary for all of 2011
    SELECT 
    	e.EmployeeID
    	,c.FirstDateOfMonth AS MonthStartDate
    	,SUM(COALESCE(etc.HoursWorked, 0)) AS HoursWorked
    FROM dbo.Employee AS e
    CROSS JOIN dbo.Calendar AS c
    LEFT JOIN dbo.EmployeeTimeCard AS etc ON
    	etc.EmployeeID = e.EmployeeID
    	AND etc.DateWorked = c.CalendarDate
    WHERE
    	c.CalendarDate BETWEEN '20110101' AND '20111231'
    GROUP BY 
    	c.FirstDateOfMonth
    	,e.EmployeeID
    ORDER BY 
    	MonthStartDate
    	,e.EmployeeID;
    
    

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by rcditto Wednesday, December 28, 2011 7:30 PM
    Wednesday, December 28, 2011 5:51 PM
    Answerer

All replies

  • Chris,

    I definitely agree with you that 365 columns for 365 seems like a messy idea. That solution  not only leaves you with a wide table, but you would have to clear it every year ! And add a column for the Leap years with an additional day.

    It just seems intuitive from a visualization perspective, due to our familiarity with Spreadsheets

    I would recommend a table structure like this ;

    CREATE TABLE dbo.employee_time (
    emp_id INT
    work_datetime DATETIME
    work_hours DECIMAL (4,2))

    With this structure, you would end up with 1 row per employee per day ( you can put a PK or a Unique constraint on the combination of emp_id and work_datetime columns).  So your data would end up looking something like this;

    emp_id     work_datetime      work_hours

    124         12-04-2011              8.75
    128         12-04-2011              9
    124         12-05-2011              8

     

    As far as reporting on this is concerned, you can write SELECT queries that SUM up the work_hours grouped by either emp_id, or by work_datetime. For Weekly, monthly or yearly reports - you can use a combination of datepart functions to parse out the week, month of year and group by that. you will neither have to add columns for leap years, nor will you have to clear the data in thia table on an yearly basis.

    This approach will help you design tables from a data modelling perspective rather than a reporting perspective. Please free to ask if you have any more questions on this task.

    Thanks,

    Sanil

    ----------------------------------------------------------------------------------------------

    If you found this reply helpful in any way, please vote as helpful. It it helped you answer your question, please mark it as Answer.


    Sanil Mhatre
    • Edited by Sanil Mhatre Wednesday, December 28, 2011 5:48 PM work_hours would be decimals instead of INT so that fraction of hours can be entered
    Wednesday, December 28, 2011 5:46 PM
  • 365 columns seems like a bad idea, but I don't know another way to store the data.


    Another approach is to create a separate table to store daily hours worked per employee as separate rows rather than columns. This will allow you to report a summary of hours worked by employee for any ad-hoc time period.  Below is an example. 

     

    CREATE TABLE dbo.Employee(
    	EmployeeID varchar(20) NOT NULL
    		CONSTRAINT PK_Employee PRIMARY KEY
    	,FirstName varchar(30) NOT NULL
    	,LastName varchar(30) NOT NULL
    	,MiddleName varchar(30) NULL
    	);
    
    CREATE TABLE dbo.EmployeeTimeCard(
    	EmployeeID varchar(20) NOT NULL
    	,DateWorked date NOT NULL
    	,HoursWorked decimal(4,2)
    	,CONSTRAINT PK_EmployeeTimeCard PRIMARY KEY(
    		EmployeeID
    		,DateWorked)
    	,CONSTRAINT FK_EmployeeTimeCard_Employee FOREIGN KEY(EmployeeID)
    		REFERENCES dbo.Employee(EmployeeID)
    	);
    GO
    
    INSERT INTO dbo.Employee VALUES('Employee1', 'Employee1FirstName', 'Employee1LastName', 'Employee1MiddleName');
    INSERT INTO dbo.Employee VALUES('Employee2', 'Employee2FirstName', 'Employee3LastName', NULL);
    INSERT INTO dbo.Employee VALUES('Employee3', 'Employee2FirstName', 'Employee3LastName', 'Employee3MiddleName');
    
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee1', '20110101', 6.25);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee1', '20110102', 0.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee1', '20110103', 12.00);
    
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee2', '20110101', 0.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee2', '20110102', 0.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee2', '20110103', 8.00);
    
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee3', '20110101', 5.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee3', '20110102', 9.00);
    INSERT INTO dbo.EmployeeTimeCard VALUES('Employee3', '20110103', 8.00);
    GO
    
    --hours by month summary
    SELECT 
    	e.EmployeeID
    	,DATEADD(month, DATEDIFF(month, '19000101', etc.DateWorked), '19000101') AS MonthStartDate
    	,SUM(etc.HoursWorked) AS HoursWorked
    FROM dbo.Employee AS e
    JOIN dbo.EmployeeTimeCard AS etc ON
    	etc.EmployeeID = e.EmployeeID
    GROUP BY 
    	DATEADD(month, DATEDIFF(month, '19000101', etc.DateWorked), '19000101')
    	,e.EmployeeID
    ORDER BY 
    	MonthStartDate
    	,e.EmployeeID;
    

    If you don't have time card data for days not worked, you could use a calendar table and LEFT JOIN in order to report zero hours when no timecard data exists for the reporting period.  An example for such a query using the calendar table example at http://weblogs.sqlteam.com/dang/archive/2010/07/19/calendar-table-and-datetime-functions.aspx:

     

    --hours by month summary for all of 2011
    SELECT 
    	e.EmployeeID
    	,c.FirstDateOfMonth AS MonthStartDate
    	,SUM(COALESCE(etc.HoursWorked, 0)) AS HoursWorked
    FROM dbo.Employee AS e
    CROSS JOIN dbo.Calendar AS c
    LEFT JOIN dbo.EmployeeTimeCard AS etc ON
    	etc.EmployeeID = e.EmployeeID
    	AND etc.DateWorked = c.CalendarDate
    WHERE
    	c.CalendarDate BETWEEN '20110101' AND '20111231'
    GROUP BY 
    	c.FirstDateOfMonth
    	,e.EmployeeID
    ORDER BY 
    	MonthStartDate
    	,e.EmployeeID;
    
    

     

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by rcditto Wednesday, December 28, 2011 7:30 PM
    Wednesday, December 28, 2011 5:51 PM
    Answerer
  • 365? Oh, nobody works Feb 29th anyway. :)

    Are you recording hours (as in a reported timesheet) or recording start and stop time? Let's make sure we're recording the correct data (and not just how it is (currently) (supposed to be) used).

    If the former, Dan provided a possible solution with a child TABLE recording hours by date.

    If the latter, a child TABLE can be used to record work times:

    Employee_Work
    ------------------
    Employee (FK Employee)
    Start DATETIME (PK)
    Stop DATETIME

    In either case, making sure no times overlap is not simple.

    Wednesday, December 28, 2011 6:13 PM
    Answerer
  • You guys rock! I hadn't even thought about breaking out each employee into their own table. That will totally work. I am doing recorded hours as in a timesheet Brian.

    Thanks SO much

    Chris

    Wednesday, December 28, 2011 7:30 PM
  • Timesheet as a child TABLE has a PK on Employee and the date the hours were recorded for then, as Dan pointed out.
    Thursday, December 29, 2011 1:58 PM
    Answerer