Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Location
    Birmingham, AL USA
    Posts
    1

    Unanswered: time-clock application - query to calculate hours worked

    I have created a time-clock applications where employees can clock in and out throughout the day. The data is stored in a SQL Server table in the following format:

    Employee DateTime TimeType
    -------- ------------------ ---------
    Bob 10/1/2003 8:00 AM IN
    Bob 10/1/2003 11:30 AM OUT
    Bob 10/1/2003 12:30 PM IN
    Bob 10/1/2003 5:00 PM OUT

    I want to write a view that will display the number of hours an employee worked on any given day. Eg. in the example above, Bob worked 3.5 hours in the morning and 4.5 hours in the afternoon, for a total of 8 hours on 10/1/2003. I am not sure how to setup a select statement to do this though.

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Checkout Datediff in Books Online

    DATEDIFF ( datepart , startdate , enddate )

    Though it looks like U may have 2 do a bit of formating with Your Datetimes

    Then the only way I can C of doing this is with a Cursor populated in Employee DateTime Order in a stored Procedure

    DateDiff the 1st Value to the Next Value & Increment a Variable with the result until Employee Changes when U can get your totals

    don't forget to err trap Out's without In's etc

    Can anyone think of a way of doing this without a Cursor ?

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Getting there...but I'm stuck...

    I need to figure out how to get it where the in values is less than the MIN(of the out)

    Code:
    CREATE TABLE myTable99 (Employee varchar(10), DayWorked DateTime,  TimeType char(3))
    GO
    
    INSERT INTO myTable99 (Employee, DayWorked, TimeType)
    SELECT 'Bob', '10/1/2003 8:00 AM',  'IN'  UNION ALL
    SELECT 'Bob', '10/1/2003 11:30 AM', 'OUT' UNION ALL
    SELECT 'Bob', '10/1/2003 12:30 PM', 'IN'  UNION ALL
    SELECT 'Bob', '10/1/2003 5:00 PM',  'OUT'
    GO
    
        SELECT  i.Employee, i.DayWorked, o.DayWorked 
          FROM myTable99 i
    INNER JOIN myTable99 o 
    	ON i.Employee = o.Employee 
           AND DAY(i.DayWorked) = DAY(o.DayWorked)
         WHERE i.TimeType = 'IN'
           AND o.TimeType = 'OUT'
           AND i.DayWorked < o.DayWorked
    I'm SURE this has been done before.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    You might check what I posted at http://www.dbforums.com/t920991.html. It is very close to what you want.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Paul Young
    You might check what I posted at http://www.dbforums.com/t920991.html. It is very close to what you want.
    I gotta take a look at that....

    This is severe corner cutting....

    Code:
    CREATE TABLE #in  (RowId int IDENTITY(1,1), Employee varchar(10), DayWorked DateTime,  TimeType char(3))
    CREATE TABLE #out (RowId int IDENTITY(1,1), Employee varchar(10), DayWorked DateTime,  TimeType char(3))
    
    INSERT INTO #in (Employee, DayWorked, TimeType) 
    SELECT Employee, DayWorked, TimeType
    FROM myTable99
    WHERE TimeType = 'IN'
    ORDER BY DayWorked
    
    INSERT INTO #out (Employee, DayWorked, TimeType) 
    SELECT Employee, DayWorked, TimeType
    FROM myTable99
    WHERE TimeType = 'OUT'
    ORDER BY DayWorked
    
    SELECT Employee, DATEPART(dy,i_DayWorked), SUM(Hours_Worked)
    FROM (
    	SELECT i.Employee, i.DayWorked as i_DayWorked , o.DayWorked, DATEDIFF(hh,i.DayWorked, o.DayWorked) AS Hours_Worked
    	  FROM #in i INNER JOIN #out o ON i.RowId = o.RowId
    	) as xxx
    GROUP BY Employee, DATEPART(dy,i_DayWorked)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •