Comments by Member 13210334 (Top 1 by date)

USE [test]
GO
/****** Object: StoredProcedure [dbo].[GetWorkingHours] Script Date: 19-05-2017 12:07:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkingHours]
@Day date
AS
BEGIN
SET NOCOUNT ON;

with EmpIn as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%IN%'
),
EmpOut as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%OUT%'
)
select
e1.EmpId as 'EmpId',
e1.Date as 'In',
e2.Date as 'Out',
datediff(MINUTE, e1.[Date], e2.[Date]) as 'TotalHours'
from EmpIn e1
full outer join EmpOut e2 on e1.RowNum = e2.RowNum
end