SQLTeam.com | Weblogs | Forums

Night shift Hours calculation

tsql

#1

Hi everyone,
I am stuck with an issue, I have to create an attendance system where i have an employee id and punching time. There can be multiple punches in a day . So firstly for night shift employees i can't determine his actual in and out time and thus not the duration too .
for example a person came office at 11 pm on 1st april and left at 4 am on 2nd april. in betwwen he also went out for dinner or tea . I dont have fixed no. of punches. and i need to handle 2 dates as well. So min and max logic wont work.

Can you help me into this please??


#2

It is problematic not knowing which record is punch-in. What if a person forget to punch-out when he/she comes back from lunch or goes home?
Anyway, "row_number() over(...)" would be the way I would choose.
If you need me to "dig deeper" please provide:

  • table description in form of create statement
  • sample data in form of insert statement
  • your query (what you have written so far to solve your problem)
  • expected output (from the sample data you provide)

#3

The major problem is there are multiple punch-ins and row_number wont work as punches are of 2 different dates in case of night shift .

here tktno is employeeID , date is the punching time and sno, is no. of punches in a day. I have sent you a sample data of a night shift employee.


#4

I require data in given below format -

but here as you can see the hours calculation is not correct . I want to correct it .


#5

This will give you corrected sno (I assume a shift/break >= 10 hours to be new punch-in (start of day). Hope you can do the rest.

declare @dt date=cast('2016-04-07' as date);

with cte1
  as (select punchtimedetailsid
            ,tktno
            ,hhmm
            ,tmmm
            ,hh_mm
            ,flag
            ,[date]
            ,inout
            ,cast([date] as date) as dt
            ,row_number() over(partition by tktno order by [date]) as rn
        from yourtable
       where [date]>=@dt
         and [date]<dateadd(day,2,@dt)
     )
    ,cte2
  as (select a.tktno
            ,min(a.rn) as minrn
            ,max(a.rn) as maxrn
        from (select top(2)
                     a.tktno
                    ,a.rn
                from cte1 as a
                     left outer join cte1 as b
                                  on b.tktno=a.tktno
                                 and b.rn=a.rn-1
                                 and datediff(minute,b.[date],a.[date])>600
               order by case when b.rn is null then 1 else 0 end
                       ,a.rn desc
             ) as a
       group by a.tktno
     )
select a.punchtimedetailsid
      ,a.tktno
      ,a.hhmm
      ,a.tmmm
      ,a.hh_mm
      ,a.flag
      ,a.[date]
      ,row_number() over(partition by a.tktno order by a.rn) as sno
      ,a.inout
  from cte1 as a
       inner join cte2 as b
               on b.tktno=a.tktno
              and b.minrn<=a.rn
              and b.maxrn>=a.rn
;

#6

Thankyou for your Reply!! but can you explain what are you trying to do and y have 2 added 2 to the date. ?? i am afraid , but This code is not giving desired Result.

By running your query i am getting result like this,


#7

I'm adding 2 days to get records for date he/she punched in (@dt april 7th) and records less than 2 days after (april 9th) which gives 7th and 8th.
It seems like he/she punched out at 21:55:41 and punched in again less than 10 hours after.
Now you need to adjust the minutes in this line:

and datediff(minute,b.[date],a.[date])>600

to fit your needs. Try 540 minutes (9 hours).


#8

If i change to 9 hours ,i.e. 540 it gives me following results.

that is just 1 record for a person means no time in and time out.
also another thing i noticed is its not giving me records for all the users . I dont know y ??
Can you look at the problem again?


#9

Forget what I write earlier and try this:

declare @dt date=cast('2016-04-07' as date);
declare @maxbreak int=9*60;

with cte1
  as (select punchtimedetailsid
            ,tktno
            ,hhmm
            ,tmmm
            ,hh_mm
            ,flag
            ,[date]
            ,inout
            ,cast([date] as date) as dt
            ,row_number() over(partition by tktno order by [date]) as rn
        from yourtable
       where [date]>=@dt
         and [date]<dateadd(day,2,@dt)
     )
    ,cte2
  as (select a.tktno
            ,a.rn
        from (select a.tktno
                    ,a.rn
                    ,row_number() over(partition by a.tktno
                                       order by case when b.rn is null then 1 else 0 end
                                               ,a.[date]
                                      )
                     as rn2
                from cte1 as a
                     left outer join cte1 as b
                                  on b.tktno=a.tktno
                                 and b.rn=a.rn-1
                                 and datediff(minute,b.[date],a.[date])>@maxbreak
               where a.[date]>=@dt
                 and a.[date]<dateadd(day,1,@dt)
             ) as a
       where a.rn2=1
     )
    ,cte3
  as (select a.tktno
            ,a.rn
        from (select b.tktno
                    ,b.rn
                    ,row_number() over(partition by b.tktno
                                       order by case when c.rn is null then 1 else 0 end
                                               ,b.[date]
                                      )
                     as rn2
                from cte2 as a
                     inner join cte1 as b
                             on b.tktno=a.tktno
                            and b.rn>a.rn
                     left outer join cte1 as c
                                  on c.tktno=b.tktno
                                 and c.rn=b.rn-1
                                 and datediff(minute,c.[date],b.[date])>@maxbreak
             ) as a
       where a.rn2=1
     )
    ,cte4
  as (select c.punchtimedetailsid
            ,c.tktno
            ,c.hhmm
            ,c.tmmm
            ,c.hh_mm
            ,c.flag
            ,c.[date]
            ,row_number() over(partition by c.tktno order by c.rn) as sno
            ,c.inout
        from cte2 as a
             inner join cte3 as b
                     on b.tktno=a.tktno
             inner join cte1 as c
                     on c.tktno=a.tktno
                    and c.rn>=a.rn
                    and c.rn<b.rn
     )
select *
  from cte4
;

#10

Thankyou so much for your support.

But let me ask for one more favour, the query above gave me the correct sequence number. Now i need to insert this data into a table named "AttendanceDetails" , which has following structure.

here attendance_id, is auto generated, employee id is last 4 digit of tktno in the previous query, and Is present is the duration.

Can you also suggest the mechanism for this?

i can also show you the current mechanism followed , which fails in this task for night shift

ALTER PROCEDURE [dbo].[sp_attendetails]    
(    
@Attendance_Date DATE    
)    
AS    
BEGIN    
SELECT tktno,t.date into #temp1 from PunchTimeDetails t    
where date>@Attendance_Date    
    
declare @curDate Date      
set @curDate = GETDATE()   
--set @curDate='2015/3/12'   
declare @maxDate Date      
SET @maxDate =@Attendance_Date    
  --SET @maxDate ='2015/3/11'   
;with GetDates As      
(      
select 1 as counter, @maxDate as Date       
UNION ALL      
select counter + 1, DATEADD(day,counter,@maxDate)    
from GetDates      
where DATEADD(day, counter, @maxDate) <= @curDate      
)      
select Date into #tempdate from GetDates      
      
  SELECT A.tktno,B.Date,MIN(A.date)'INTime',MAX(A.date)'OUTTime'    
  INTO #Resulttemp    
  FROM #temp1 A    
  INNER JOIN #tempdate B    
  ON CONVERT(CHAR(10),A.date,103)=CONVERT(CHAR(10),B.Date,103)      
  GROUP BY B.Date,A.tktno    
     
 SELECT     
 --REPLACE(REPLACE(REPLACE(tktno,'001','1'),'010','10'),'011','11') AS 'tktno'  
 RIGHT(tktno, 4) as 'tktno'  
 ,(SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), B.InTime, 100), 7)) )AS 'INTime',    
       CASE WHEN INTime=OUTTime THEN NULL    
      ELSE      
      (SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), B.OUTTIME, 100), 7)) )     
      END AS 'OUTTime',    
      Date,    
      convert(varchar(5), sum(datediff(minute, INTime, isnull(OUTTIME, dateadd(hh, 19, DATEADD(dd, DATEDIFF(dd, 0, INTime), 0))))) / 60)     
+ ':' +    
convert(varchar(5),sum(datediff(minute, INTime, isnull(OUTTIME, dateadd(hh, 19, DATEADD(dd, DATEDIFF(dd, 0, INTime), 0))))) % 60)    
as 'TTime',    
' ' AS 'Comments',    
'101' AS 'CreateBy',    
getdate() AS 'CreateDate',    
'101' AS 'UpdatedBy',    
getdate() AS 'UpdatedDate',    
CASE WHEN B.INTime IS NULL THEN 559    
 ELSE 56    
 END AS 'PresentType'    
  FROM #Resulttemp b    
  where tktno > 999    
  GROUP BY tktno,INTime,OUTTIME,date    
     
    
 drop table #temp1     
 drop table #tempdate    
 drop table #Resulttemp    
 END

#11

Sorry for this late reply (been busy).
I belive the following query will suit your needs:

declare @attendance_date date=cast('2016-04-07' as date);
declare @maxbreak int=9*60;

with getdates(adt)
  as (select dateadd(day,-1,@attendance_date)
      union all
      select dateadd(day,1,adt)
        from getdates
       where adt<cast(current_timestamp as date)
     )
    ,cte1(tktno,[date],rn)
  as (select tktno
            ,[date]
            ,row_number() over(partition by tktno
                               order by [date]
                              )
        from yourtable
       where [date]>=dateadd(day,-1,@attendance_date)
     )
    ,cte2(tktno,[date],rn,attendance_date)
  as (select a.tktno
            ,a.[date]
            ,a.rn as rn
            ,cast(a.[date] as date)
        from cte1 as a
             left outer join cte1 as b
                          on b.tktno=a.tktno
                         and b.rn=a.rn-1
                         and datediff(minute,b.[date],a.[date])>@maxbreak
       where a.rn=1
          or b.rn is not null
      )
     ,cte3
   as (select a.attendance_date
             ,a.tktno
             ,ltrim(right(convert(varchar(20),a.[date],0),7)) as intime
             ,ltrim(right(convert(varchar(20),b.[date],0),7)) as outtime
             ,ltrim(datediff(minute,a.[date],b.[date])/60)
             +':'
             +right(100+(datediff(minute,a.[date],b.[date])%60),2)
              as ispresent
         from (select a.tktno
                     ,a.[date]
                     ,a.rn
                     ,a.attendance_date
                     ,isnull(min(b.rn)-1,max(c.rn)) as rn2
                 from cte2 as a
                      left outer join cte2 as b
                                   on b.tktno=a.tktno
                                  and b.rn>a.rn
                      inner join cte1 as c
                              on c.tktno=a.tktno
                             and c.rn>=a.rn
                group by a.tktno
                        ,a.[date]
                        ,a.rn
                        ,a.attendance_date
              ) as a
              inner join cte1 as b
                      on b.tktno=a.tktno
                     and b.rn=a.rn2
      )
select *
  from cte3
 where attendance_date>=@attendance_date
;

cte2 will nummerate records for easy joining
cte2 will find punch-in
cte3 til find punch-out and format to your specification

in order to eliminate records "belonging" to previous attention date, this query will "look" at dates - 1 day (from the @attention_date)


#12

Thankyou for all the help .

please refer the image below:

As you can see in the highlighted area for one employee data for first date is correct but not for others . And its the case with all the employees.

Any solution for this ??


#13

Please provide data for tktno 002031period 2016-04-06 till 2016-04-13


#14

The data for 002031 is :

PunchTimeDetailsId tktno hhmm tmmm hh_mm flag date sno INOUT
408045 002031 0 0 0 TD 2016-04-06 05:25:13.000 1 In/Out
408623 002031 0 0 0 TD 2016-04-06 18:15:30.000 2 In/Out
409099 002031 0 0 0 TD 2016-04-07 02:25:33.000 1 In/Out
409100 002031 0 0 0 TD 2016-04-07 02:25:37.000 2 In/Out
409132 002031 0 0 0 TD 2016-04-07 05:24:48.000 3 In/Out
409860 002031 0 0 0 TD 2016-04-07 19:17:46.000 4 In/Out
410157 002031 0 0 0 TD 2016-04-07 22:30:18.000 5 In/Out
410160 002031 0 0 0 TD 2016-04-07 22:32:06.000 6 In/Out
410164 002031 0 0 0 TD 2016-04-07 22:33:05.000 7 In/Out
410182 002031 0 0 0 TD 2016-04-07 22:56:41.000 8 In/Out
410396 002031 0 0 0 TD 2016-04-08 07:16:57.000 1 In/Out
410956 002031 0 0 0 TD 2016-04-08 18:13:50.000 2 In/Out
411116 002031 0 0 0 TD 2016-04-08 19:52:16.000 3 In/Out
411147 002031 0 0 0 TD 2016-04-08 20:17:02.000 4 In/Out
411515 002031 0 0 0 TD 2016-04-09 07:23:46.000 1 In/Out
412409 002031 0 0 0 TD 2016-04-11 18:44:15.000 1 In/Out
412431 002031 0 0 0 TD 2016-04-11 18:57:22.000 2 In/Out
412466 002031 0 0 0 TD 2016-04-11 19:17:13.000 3 In/Out
412946 002031 0 0 0 TD 2016-04-12 06:48:45.000 1 In/Out
413555 002031 0 0 0 TD 2016-04-12 18:28:56.000 2 In/Out
413771 002031 0 0 0 TD 2016-04-12 20:44:21.000 3 In/Out
413820 002031 0 0 0 TD 2016-04-12 21:12:25.000 4 In/Out
413862 002031 0 0 0 TD 2016-04-12 21:36:45.000 5 In/Out
413866 002031 0 0 0 TD 2016-04-12 21:39:00.000 6 In/Out
413868 002031 0 0 0 TD 2016-04-12 21:41:26.000 7 In/Out
413909 002031 0 0 0 TD 2016-04-12 22:33:19.000 8 In/Out
413961 002031 0 0 0 TD 2016-04-12 23:39:13.000 9 In/Out
413975 002031 0 0 0 TD 2016-04-12 23:54:42.000 10 In/Out
414130 002031 0 0 0 TD 2016-04-13 07:59:30.000 1 In/Out
414686 002031 0 0 0 TD 2016-04-13 18:32:10.000 2 In/Out
414778 002031 0 0 0 TD 2016-04-13 19:33:45.000 3 In/Out
414787 002031 0 0 0 TD 2016-04-13 19:39:14.000 4 In/Out
414875 002031 0 0 0 TD 2016-04-13 20:34:12.000 5 In/Out
414902 002031 0 0 0 TD 2016-04-13 21:00:06.000 6 In/Out
414993 002031 0 0 0 TD 2016-04-13 22:04:09.000 7 In/Out
415029 002031 0 0 0 TD 2016-04-13 22:50:49.000 8 In/Out
415228 002031 0 0 0 TD 2016-04-14 07:14:56.000 1 In/Out
415775 002031 0 0 0 TD 2016-04-14 18:28:14.000 2 In/Out
416288 002031 0 0 0 TD 2016-04-15 08:31:53.000 1 In/Out


#15

If I show your data like below, and in column "Diff" show the time difference to previous record, I have marked each punch-in and punch-out in column "Punch".
This show what worker has missed some punch-out's. Now - if you focus on DetailsID 412946 he/she has probably worked 11 hours and 31 minutes (from last record) without punckung to break/lunch. As the routine I gave you, looks at "gaps" that are over 9 hours (@maxbreak) to determine where the punch-in is, the routine misses this as one long "shift".
Does this make sence (or only to me)?
You should try out different values in @maxbreak to make it fit your data/needs, but as far as I can see, the output is exactly as I would have expected (if I were to do it by hand).

DetailsID Date                    Diff  Punch
408045    2016-04-06 05:25:13.000       Out
408623    2016-04-06 18:15:30.000 12:50 In
409099    2016-04-07 02:25:33.000 08:10 
409100    2016-04-07 02:25:37.000 00:00 
409132    2016-04-07 05:24:48.000 02:59 Out
409860    2016-04-07 19:17:46.000 13:53 In
410157    2016-04-07 22:30:18.000 03:13 
410160    2016-04-07 22:32:06.000 00:02 
410164    2016-04-07 22:33:05.000 00:01 
410182    2016-04-07 22:56:41.000 00:23 
410396    2016-04-08 07:16:57.000 08:20 Out
410956    2016-04-08 18:13:50.000 10:57 In
411116    2016-04-08 19:52:16.000 01:39 
411147    2016-04-08 20:17:02.000 00:25 Out
411515    2016-04-09 07:23:46.000 11:06 Missing punch-out
412409    2016-04-11 18:44:15.000 59:21 In
412431    2016-04-11 18:57:22.000 00:13 
412466    2016-04-11 19:17:13.000 00:20 Out
412946    2016-04-12 06:48:45.000 11:31 Missing punch-out
413555    2016-04-12 18:28:56.000 11:40 In
413771    2016-04-12 20:44:21.000 02:16 
413820    2016-04-12 21:12:25.000 00:28 
413862    2016-04-12 21:36:45.000 00:24 
413866    2016-04-12 21:39:00.000 00:03 
413868    2016-04-12 21:41:26.000 00:02 
413909    2016-04-12 22:33:19.000 00:52 
413961    2016-04-12 23:39:13.000 01:06 
413975    2016-04-12 23:54:42.000 00:15 
414130    2016-04-13 07:59:30.000 08:05 Out
414686    2016-04-13 18:32:10.000 10:33 In
414778    2016-04-13 19:33:45.000 01:01 
414787    2016-04-13 19:39:14.000 00:06 
414875    2016-04-13 20:34:12.000 00:55 
414902    2016-04-13 21:00:06.000 00:26 
414993    2016-04-13 22:04:09.000 01:04 
415029    2016-04-13 22:50:49.000 00:46 
415228    2016-04-14 07:14:56.000 08:24 Out
415775    2016-04-14 18:28:14.000 11:14 Missing punch-out
416288    2016-04-15 08:31:53.000 14:03 In