-1

I have a Table Name as EmpLog

EmpLog Table

  1. ID
  2. EmpId
  3. Status
  4. ActionDateTime

Id - Primary Key

EmpID - Employee code. // example N100 ,N101, N200

Status - IN / OUT

ActionDateTime - Time with Date. Exact occurrence of IN or OUT log entry.

Click for table structure

I want to execute a stored procedure with particular Date. it should return the result for total number of work hours for each employeeid in the table.

we may have multiple IN/OUT for a same employee on the selected date.

see the output format image Expected Output result

if any IN / OUT Missing it should show for that employee. example odd IN or OUT entry for the selected date it should show missing of either IN or Out.

if there is No IN and Out it should show N/A

Nehru
  • 11
  • 3

1 Answers1

1

Assuming Each IN's have and appropriate OUT and in sequence then you can use lag in SQL Server 2012 to get this as below:

select empcode, sum(hrs) as TotalWorkHours from (
select *,Hrs = datediff(MINUTE,ActionDate, lag(ActionDate, 1, ActionDate) over(partition by empcode order by ActionDate desc))/60.0 from employee
where statuses = 'in'
) a
group by empcode

Sample input query:

create table employee (id int identity(1,1), empcode int, [statuses] varchar(5), ActionDate datetime)

insert into employee ( empcode, statuses, ActionDate) values
  (2,'in', dateadd(hour, -20, getdate()) )
, (2,'out', dateadd(hour, -19, getdate()))
, (2,'in', dateadd(hour, -18, getdate()) )
, (2,'out', dateadd(hour, -17, getdate()))
, (2,'in', dateadd(hour, -12, getdate()) )
, (2,'out', dateadd(hour, -10, getdate()))
, (3,'in', dateadd(hour, -9, getdate())  )
, (3,'out', dateadd(hour, -6, getdate()) )
, (3,'in', dateadd(hour, -4, getdate())  )
, (3,'out', dateadd(hour, -1, getdate()) )

output for this requirement:

+---------+----------------+
| empcode | TotalWorkHours |
+---------+----------------+
|       2 |       8.000000 |
|       3 |       5.000000 |
+---------+----------------+
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
  • How to get minutes along with the total work hours. and also how to display missing in and out and also wants to display list of in and out by empcode basis in same row. – Nehru May 18 '17 at 23:04
  • @Nehru, If the ask is that complex, it should be in the form of a re-worded question. Please clarify your question. – Jaaz Cole May 18 '17 at 23:29
  • I have emplog table with multiple punch in & out for the employees. i want calculate total hours employees worked on a given date. if there are any IN & OUT it should show " MISSING IN / OUT" in total column. – Nehru May 18 '17 at 23:50