none
How to calculate Hours based on Columns of nvarchar(4) type which contains Military Time e.g. 0800,1300 and so on..

    Question

  • Hi,

    I have this raw data from a Table which contains the clock In / Out Info of an Employee as shown in Figure 1.  There is the Date,ShiftCode,ShiftTimeIn & ShiftTimeOut Columns which shows the Date this Employee is suppose to come and each [Date], an Employee is Assigned a ShiftCode which also has a fix ShiftTimeIn & ShiftTimeOut of 8 Hours.

    The Columns with the firstword 'Actual' are the time these guys were clocked in by some staff in the office.  Notice that the ActualDateTimeOut,ActualTimeOut and TotalHrs are Empty since these guys normally doesn't have to clock out unless there is an overtime needed. 

    Now what I'm hoping to accomplish is... to calculate the number of hours each of these guys have worked even with the ActualDateTimeOut,ActualTimeOut column is null... if you look at the first row.... 

    Empnum F229 is suppose to report on 2012-03-05 at 0700 which he did as shown in the same row  in ActualDateTimeIn & ActualTimeIn since they are not null.... and since normally these guys don't clock out... we can always assume that this guy's ActualDateTimeOut is on the Same day '2012-03-05' and and ActualTimeOut of 1900 based on the ShiftTimeOut... so the first thing I wanna accomplish is...to calculate the Hours...

    Correct me If i'm wrong... I guess there is a need to fill up the empty columns first before calculating...

    Figure 1.

    But what about those shifts at Night where the Employees are clocked out the next Day as show in the Red Box Below.

    Figure 2.

    So what I'm hoping to accomplish is something like this as shown in Figure 3.

    Figure 3.

    Thanks in Advance


    Wednesday, April 04, 2012 3:49 AM

Answers

  • Here's a first draft solution, but is dependant on answers to some questions I've asked...

    if object_id('Timesheet','U') is not null drop table Timesheet
    go
    create table Timesheet (
    	empnum varchar(10),
    	[DATE] char(10),
    	ShiftCode char(2),
    	ShiftTimeIn nvarchar(4),
    	ShiftTimeOut nvarchar(4),
    	ActualDateTimeIn nvarchar(10),
    	ActualTimeIn nvarchar(4),
    	ActualDateTimeOut nvarchar(10),
    	ActualTimeOut nvarchar(4),
    	TotalHours int)
    
    insert into Timesheet select 'F2299','2012-03-05','A1','0700','1900','2012-03-05','0628',null,null,0
    insert into Timesheet select 'F2455','2012-03-05','A3','0800','2000','2012-03-05','0751',null,null,0
    insert into Timesheet select 'P0199','2012-03-05','A3','0800','2000','2012-03-05','0758',null,null,0
    insert into Timesheet select '864','2012-03-05','A3','0800','2000','2012-03-05','0648',null,null,0
    insert into Timesheet select 'F3062','2012-03-05','B3','2000','0800','2012-03-05','0800',null,null,0
    insert into Timesheet select 'F382','2012-03-05','B1','1900','0700','2012-03-05','1838',null,null,0
    insert into Timesheet select 'F2233','2012-03-05','B1','1900','0700','2012-03-05','1843',null,'',0
    insert into Timesheet select '821','2012-03-05','A3','0800','2000','2012-03-05','0749',null,null,0
    
    
    select 
    		empnum ,
            DATE ,
            ShiftCode ,
            ShiftTimeIn ,
            ShiftTimeOut ,
            ActualDateTimeIn ,
            ActualTimeIn ,
            case 
    			when isnull(ActualDateTimeIn,'') = '' --not signed in
    			then convert(datetime, [DATE] + ' ' +left(ShiftTimeIn,2)+':'+right(ShiftTimeIn,2), 120)
    			else convert(datetime, ActualDateTimeIn + ' ' +left(ActualTimeIn,2)+':'+right(ActualTimeIn,2), 120)
    		end as DeemedStartTime,
            case
    			when isnull(ActualDateTimeOut,'') = '' --not signed out
    			then 
    				case 
    					when ShiftTimeOut < ShiftTimeIN -- a date boundary has been crossed
    					then dateadd(dd,1,convert(datetime, [DATE] + ' ' +left(ShiftTimeOut,2)+':'+right(ShiftTimeOut,2), 120))
    					else convert(datetime, [DATE] + ' ' +left(ShiftTimeOut,2)+':'+right(ShiftTimeOut,2), 120)
    				end
    		end as DeemedEndTime,
            datediff(hh,
    				case 
    					when isnull(ActualDateTimeIn,'') = '' --not signed in
    					then convert(datetime, [DATE] + ' ' +left(ShiftTimeIn,2)+':'+right(ShiftTimeIn,2), 120)
    					else convert(datetime, ActualDateTimeIn + ' ' +left(ActualTimeIn,2)+':'+right(ActualTimeIn,2), 120)
    				end,
    				case
    					when isnull(ActualDateTimeOut,'') = '' --not signed out
    					then 
    						case 
    							when ShiftTimeOut < ShiftTimeIN -- a date boundary has been crossed
    							then dateadd(dd,1,convert(datetime, [DATE] + ' ' +left(ShiftTimeOut,2)+':'+right(ShiftTimeOut,2), 120))
    							else convert(datetime, [DATE] + ' ' +left(ShiftTimeOut,2)+':'+right(ShiftTimeOut,2), 120)
    						end
    				end
    				)
    from Timesheet
    
    

    This query shows how to derive the start times and end times, and then uses those to work out a datediff of hours.

    Hope this goes some way to helping.

    • Proposed as answer by Naomi NModerator Thursday, April 05, 2012 12:26 AM
    • Marked as answer by KJian_ Tuesday, April 10, 2012 6:40 AM
    Wednesday, April 04, 2012 9:38 AM

All replies

  • Hi Jason,

    Try to use below query

    update employee
    set ActualTimeout = Shifttimeout
    , TotalHrs = datediff(hour,CAST(stuff(@ShifttimeIN,3,0,':') as time),CAST(stuff(@Shifttimeout,3,0,':') as time))
    , ActualDateTimeout = DATEADD(hour,datediff(hour,CAST(stuff(@ShifttimeIN,3,0,':') as time),CAST(stuff(@Shifttimeout,3,0,':') as time)),ActualDateTimeIn)
    where ActualDateTimeOuit is NUll and ActualTimeOut is Null
    

    But here total hours are no baled on actual times, please revert if you want this.

    Also, this query will work on SQL 2008 and later..


    - Chintak (My Blog)

    Wednesday, April 04, 2012 5:42 AM
  • A couple of questions...

    You mentioned a fixed shift time of 8 hours, and yet all the examples show 12 hours.

    How long is an employee deemed to have worked, in hours, if they do partial hours - for example F2299 has worked from 0628 to 1900 - which is 12 hours 32 minutes - is that 12 hours, 12.5 hours, 13 hours? Or does an early start not count?

    Wednesday, April 04, 2012 9:20 AM
  • Here's a first draft solution, but is dependant on answers to some questions I've asked...

    if object_id('Timesheet','U') is not null drop table Timesheet
    go
    create table Timesheet (
    	empnum varchar(10),
    	[DATE] char(10),
    	ShiftCode char(2),
    	ShiftTimeIn nvarchar(4),
    	ShiftTimeOut nvarchar(4),
    	ActualDateTimeIn nvarchar(10),
    	ActualTimeIn nvarchar(4),
    	ActualDateTimeOut nvarchar(10),
    	ActualTimeOut nvarchar(4),
    	TotalHours int)
    
    insert into Timesheet select 'F2299','2012-03-05','A1','0700','1900','2012-03-05','0628',null,null,0
    insert into Timesheet select 'F2455','2012-03-05','A3','0800','2000','2012-03-05','0751',null,null,0
    insert into Timesheet select 'P0199','2012-03-05','A3','0800','2000','2012-03-05','0758',null,null,0
    insert into Timesheet select '864','2012-03-05','A3','0800','2000','2012-03-05','0648',null,null,0
    insert into Timesheet select 'F3062','2012-03-05','B3','2000','0800','2012-03-05','0800',null,null,0
    insert into Timesheet select 'F382','2012-03-05','B1','1900','0700','2012-03-05','1838',null,null,0
    insert into Timesheet select 'F2233','2012-03-05','B1','1900','0700','2012-03-05','1843',null,'',0
    insert into Timesheet select '821','2012-03-05','A3','0800','2000','2012-03-05','0749',null,null,0
    
    
    select 
    		empnum ,
            DATE ,
            ShiftCode ,
            ShiftTimeIn ,
            ShiftTimeOut ,
            ActualDateTimeIn ,
            ActualTimeIn ,
            case 
    			when isnull(ActualDateTimeIn,'') = '' --not signed in
    			then convert(datetime, [DATE] + ' ' +left(ShiftTimeIn,2)+':'+right(ShiftTimeIn,2), 120)
    			else convert(datetime, ActualDateTimeIn + ' ' +left(ActualTimeIn,2)+':'+right(ActualTimeIn,2), 120)
    		end as DeemedStartTime,
            case
    			when isnull(ActualDateTimeOut,'') = '' --not signed out
    			then 
    				case 
    					when ShiftTimeOut < ShiftTimeIN -- a date boundary has been crossed
    					then dateadd(dd,1,convert(datetime, [DATE] + ' ' +left(ShiftTimeOut,2)+':'+right(ShiftTimeOut,2), 120))
    					else convert(datetime, [DATE] + ' ' +left(ShiftTimeOut,2)+':'+right(ShiftTimeOut,2), 120)
    				end
    		end as DeemedEndTime,
            datediff(hh,
    				case 
    					when isnull(ActualDateTimeIn,'') = '' --not signed in
    					then convert(datetime, [DATE] + ' ' +left(ShiftTimeIn,2)+':'+right(ShiftTimeIn,2), 120)
    					else convert(datetime, ActualDateTimeIn + ' ' +left(ActualTimeIn,2)+':'+right(ActualTimeIn,2), 120)
    				end,
    				case
    					when isnull(ActualDateTimeOut,'') = '' --not signed out
    					then 
    						case 
    							when ShiftTimeOut < ShiftTimeIN -- a date boundary has been crossed
    							then dateadd(dd,1,convert(datetime, [DATE] + ' ' +left(ShiftTimeOut,2)+':'+right(ShiftTimeOut,2), 120))
    							else convert(datetime, [DATE] + ' ' +left(ShiftTimeOut,2)+':'+right(ShiftTimeOut,2), 120)
    						end
    				end
    				)
    from Timesheet
    
    

    This query shows how to derive the start times and end times, and then uses those to work out a datediff of hours.

    Hope this goes some way to helping.

    • Proposed as answer by Naomi NModerator Thursday, April 05, 2012 12:26 AM
    • Marked as answer by KJian_ Tuesday, April 10, 2012 6:40 AM
    Wednesday, April 04, 2012 9:38 AM