SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


(Newbie) Time and Attendance 3 Row Query for Clock In, Clock Out and Hours Worked (Retrieving Wrong...


(Newbie) Time and Attendance 3 Row Query for Clock In, Clock Out and Hours Worked (Retrieving Wrong Clock In Record)

Author
Message
Ron Hurley
Ron Hurley
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 22
Hello and Thanks for looking at this,

I am not a developer, so my apologies if my information is provided incorrectly. I will try to be as thorough as possible.

Platform: SQL Server Express 2008 R2 with Tools / Windows 7 Home Premium (64-Bit)

I have a small project that requires a SQL Script to retrieve Time and Attendance with Total Number of Hours for a single day.

(Rules)
I need to show the 1st Clock In and the Last Clock Out for each day, then Calculate Total Number of Hours


-- ***********************************************
-- I need to get to this
-- ***********************************************

Employee Number Clock In Clock Out Hours Worked
----------------- --------------------- --------------------- -------------
AD101 2012-10-05 07:36:00 2012-10-05 12:05:00 4.483333

-- ***********************************************
-- Not this
-- ***********************************************

Employee Number Clock In Clock Out Hours Worked
----------------- --------------------- --------------------- -------------
AD101 2012-10-05 12:07:00 2012-10-05 12:05:00

-- ***********************************************
-- Notice that the Clock In should be 07:36:00 not 12:07:00
-- This would also get me to the correct Hours Worked
-- ***********************************************


I have a three row table "Sampling" as displayed below.

-- ==== Create and Populate 3 Row Test_Table with Sample Data

CREATE TABLE [Test_Table]
( [ID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED -- PK
, [Date] [smalldatetime] NOT NULL -- Date using SmallDateTime Type YYYY-MM-DD HH:MM:00
, [Employee Number] [varchar](50) NOT NULL -- Employee can contain alphanumeric
, [Clock In or Out] [varchar](50) NOT NULL ) -- Record Type; "Clock In" or "Clock Out" Record Only

SET DATEFORMAT DMY

SET IDENTITY_INSERT [Test_Table] ON

INSERT INTO [Test_Table]
( [ID]
, [Date]
, [Employee Number]
, [Clock In or Out] )
SELECT '3','Oct 5 2012 7:36AM','AD101','Clock In' UNION ALL
SELECT '4','Oct 5 2012 12:05PM','AD101','Clock Out' UNION ALL
SELECT '5','Oct 5 2012 12:07PM','AD101','Clock In'

-- ==== End Create and Populate



Here is a full table query.

-- ***********************************************
-- Select ALL from Test_Table
-- ***********************************************

SELECT [ID]
, [Date]
, [Employee Number]
, [Clock In or Out]
FROM [Test_Table]
ORDER BY [Date]



On this day, there were two "Clock In" and only one "Clock Out" record in the Test_Table

ID Date Employee Number Clock In or Out
-- -------------------- ----------------- ---------------
3 2012-10-05 07:36:00 AD101 Clock In
4 2012-10-05 12:05:00 AD101 Clock Out
5 2012-10-05 12:07:00 AD101 Clock In


I used some of the code in another FORUM Posting pertaining to Clock In and Clock Out to get to this point.
It works great as long as there is only ONE Clock In and ONE Clock Out Record for a day.
But if there happens to be more than ONE of either, that's when the output is incorrect.

-- ***********************************************
-- Select from Test_Table with conditions
-- ***********************************************

SELECT [Employee Number]
, [Clock In]
, [Clock Out]
, (CASE WHEN CAST([Clock In] as SmallDateTime) = '1900-01-01 00:00:00'
THEN ''
WHEN CAST([Clock Out] as SmallDateTime) = '1900-01-01 00:00:00'
THEN ''
WHEN CAST([Clock In] as SmallDateTime) < CAST([Clock Out] as SmallDateTime)
THEN CAST(CONVERT(DECIMAL,DATEDIFF(MINUTE,CAST([Clock In] as SmallDateTime),CAST([Clock Out] as SmallDateTime)))/60 as Varchar)
ELSE '' END) [Hours Worked]
FROM (
SELECT [Employee Number]
, MAX(CASE WHEN [Clock In or Out] = 'Clock In' -- I believe this is the area where I am having the problem
THEN CAST([Date] as SmallDateTime) -- I have tried MIN(CASE WHEN... but that does not seem to work for me either
ELSE '' END) [Clock In]
, MAX(CASE WHEN [Clock In or Out] = 'Clock Out'
THEN CAST([Date] as SmallDateTime)
ELSE '' END) [Clock Out]
FROM (
SELECT [Employee Number]
, [Clock In or Out]
, CAST(CONVERT(VARCHAR(10),[Date],111) as Date) [Clock In or Out Date]
, [Date]
FROM [Test_Table]
) TableA
GROUP BY [Employee Number]
, [Clock In or Out Date]
) TableB




-- ***********************************************
-- Output is not what I was trying for.
-- Notice that the Clock In Time is 12:07:00
-- I need it to be the other record which is 07:36:00
-- ***********************************************

Employee Number Clock In Clock Out Hours Worked
----------------- --------------------- --------------------- -------------
AD101 2012-10-05 12:07:00 2012-10-05 12:05:00



-- ***********************************************
-- This is what I was expecting, but have been unable to get to
-- ***********************************************

Employee Number Clock In Clock Out Hours Worked
----------------- --------------------- --------------------- -------------
AD101 2012-10-05 07:36:00 2012-10-05 12:05:00 4.483333



Thanks for any assistance.

RH
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4164 Visits: 7865
Good job on the test data and description...:-D



SELECT
[Employee Number],
X.[Dated],
MIN(CASE [Clock In or Out] WHEN 'Clock In' THEN CONVERT(DATETIME,[Date]) ELSE CONVERT(DATETIME,'31 dec 2199') END) AS [Clock In],
MAX(CASE [Clock In or Out] WHEN 'Clock In' THEN 0 ELSE [Date] END) AS [Clock Out]
FROM [Test_Table]
CROSS APPLY (
-- This just works out the date part without the time
SELECT DATEADD(DAY,datediff(DAY,0,[Date]),0) AS [Dated]
) AS X
GROUP BY
[Employee Number],
X.[Dated]




MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Ron Hurley
    Ron Hurley
    Grasshopper
    Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

    Group: General Forum Members
    Points: 14 Visits: 22
    Thank You Mister Magoo!!!

    This worked PERFECTLY!!! I have extrapolated the code you provided and used it within my base code, ran it against 100k+ records and with only a little bit of formatting got the exact results I was expecting. I really appreciate your help with this.

    Thanks Again from a Newbie!!!

    RH
    mister.magoo
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

    Group: General Forum Members
    Points: 4164 Visits: 7865
    Thanks for the feedback, glad to help.

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search