TimeLogging

Hi Everyone,

I need help.

I have this set of data:

EMPID Actual Time IN/OUT Count
1 10:00 IN 1
1 10:15 OUT 2
1 12:00 IN 3
1 12:30 OUT 4

And I want this way:

EMPID IN OUT TotalMins
1 10:00 10:15 0:15
1 12:00 12:30 0:30

How do I code this in sql?

Thank you in advance

hi

i tried to do this

hope it helps

:slight_smile:
:slight_smile:

drop create data ..
drop table #abc 
go 


create table #abc 
(
EMPID	int ,
ActualTime	datetime , 
[IN/OUT] varchar(10),
aCount int 
)
go 
insert into #abc select 1,'10:00','IN',	1
insert into #abc select 1,'10:15','OUT',	2
insert into #abc select 1,'12:00','IN',	3
insert into #abc select 1,'12:30','OUT',	4
go

this SQL is long ... it can be made very short ... if you want

SQL
; WITH ctein 
     AS (SELECT * 
         FROM   #abc 
         WHERE  [in/out] = 'IN'), 
     cteout 
     AS (SELECT * 
         FROM   #abc 
         WHERE  [in/out] = 'OUT'), 
     finalcte 
     AS (SELECT a.empid, 
                a.actualtime      AS [in], 
                Min(b.actualtime) AS [out] 
         FROM   ctein a 
                JOIN cteout b 
                  ON a.empid = b.empid 
                     AND a.actualtime <= b.actualtime 
         GROUP  BY a.empid, 
                   a.actualtime) 
SELECT a.empid, 
       Cast(a.[in] AS TIME), 
       Cast(a.out AS TIME), 
       Cast(a.out - a.[in] AS TIME) 
FROM   finalcte a 

go

1 Like

Alternative:

    ,cte
  as (select empid
            ,[actual time]
            ,[in/out]
            ,row_number() over(partition by empid
                                           ,[in/out]
                                   order by [actual time]
                              )
             as rn
        from yourtable
     )
select a.empid
      ,a.[actual time] as [in]
      ,b.[actual time] as [out]
      ,dateadd(minute
              ,datediff(minute
                       ,a.[actual time]
                       ,b.[actual time]
                       )
              ,cast('00:00' as time)
              )
       as totalmins
  from cte as a
       left outer join cte as b
                    on b.empid=a.empid
                   and b.[in/out]='OUT'
                   and b.rn=a.rn
 where a.[in/out]='IN'
;
1 Like

Thank you @harishgg1 and @bitsmed for your help! :smiley:

Once you've verified that no IN / OUT is missing in the data, it's very simple to get the output:

;WITH cte_sorted AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY ActualTime) AS row_num
    FROM #abc
)
SELECT
    EMPID,
    CAST(MIN(ActualTime) AS time(0)) AS [IN],
    CAST(MAX(ActualTime) AS time(0)) AS [OUT],
    CAST(DATEADD(MINUTE, DATEDIFF(MINUTE, MIN(ActualTime), MAX(ActualTime)), 0) AS time(0)) AS TotalMins
FROM cte_sorted
GROUP BY EMPID, (row_num + 1) / 2
ORDER BY EMPID, [IN]
1 Like