I had a go at this. As I said in my comment I hope this isn't homework as I found it quite difficult to get all of the information you wanted.
The first problem to deal with is the missing information. The best way to handle that is to generate a sequence of expected entries e.g.
SELECT DISTINCT EmpCode, 'IN' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day
UNION ALL
SELECT DISTINCT EmpCode, 'OUT' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day
That gave me these results:
EmpCode Status
emp100 IN
emp102 IN
emp103 IN
emp100 OUT
emp102 OUT
emp103 OUT
If I then query against those results, LEFT JOINing to the table I will get at least one entry for every employee for Status = IN and at least one entry for every employee for Status = OUT. I'll also take that opportunity to create an In and Out
column/i> and RANK each record by Employee by date
DECLARE @Day DATE = '2017-05-18'
if OBJECT_ID('tempdb..#shifts') is not null drop table #shifts
;with Expected as
(
SELECT DISTINCT EmpCode, 'IN' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day
UNION ALL
SELECT DISTINCT EmpCode, 'OUT' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day
)
select Id, AE.EmpCode, AE.[Status],ActionDatetime,
CASE WHEN AE.[Status] = 'IN' THEN ActionDatetime ELSE CAST(NULL AS datetime) END AS InTime,
CASE WHEN AE.[Status] = 'IN' THEN CAST(NULL AS datetime) ELSE ActionDatetime END AS OutTime
, RANK() OVER (PARTITION BY AE.EmpCode, AE.[Status] ORDER BY AE.EmpCode, ActionDatetime) rn
into #shifts
FROM Expected AE
LEFT OUTER JOIN InOut actual ON AE.EmpCode = actual.EmpCode AND AE.[Status]=actual.[Status]
WHERE Id IS NOT NULL AND CAST(ActionDatetime AS Date) = @Day
gives me the results
Id EmpCode Status ActionDateTime InTime OutTime rn
1 emp100 IN 2017-05-18 10:00 2017-05-18 10:00 NULL 1
2 emp100 OUT 2017-05-18 15:00 NULL 2017-05-18 15:00 1
3 emp100 IN 2017-05-18 17:00 2017-05-18 17:00 NULL 2
4 emp100 OUT 2017-05-18 20:00 NULL 2017-05-18 20:00 2
5 emp102 IN 2017-05-18 06:00 2017-05-18 06:00 NULL 1
6 emp102 OUT 2017-05-18 10:00 NULL 2017-05-18 10:00 1
7 emp103 IN 2017-05-18 07:00 2017-05-18 07:00 NULL 1
8 emp103 IN 2017-05-18 10:00 2017-05-18 10:00 NULL 2
We can tidy those results up by using GROUP BY
if OBJECT_ID('tempdb..#shifts2') is not null drop table #shifts2
SELECT EmpCode, rn, MIN(id) as Id,
MAX(InTime) as InTime, MAX(OutTime) AS OutTime
, CASE WHEN MAX(InTime) IS NULL or MAX(OutTime) IS NULL THEN 'Error' ELSE '' END AS Notes
INTO #shifts2
FROM #shifts
GROUP BY EmpCode, rn
Which gives me the much tidier results:
empCode rn Id TimeIn TimeOut Notes
emp100 1 1 2017-05-18 10:00 2017-05-18 15:00
emp100 2 3 2017-05-18 17:00 2017-05-18 20:00
emp102 1 5 2017-05-18 06:00 2017-05-18 10:00
emp103 1 7 2017-05-18 07:00 NULL Error
emp103 2 8 2017-05-18 10:00 NULL Error
Note you can ignore the message "Warning: Null value is eliminated by an aggregate or other SET operation."
At this point you could choose to PIVOT the columns TimeIn and TimeOut but I chose to use the LEAD function instead
;WITH res AS
(
SELECT rn,EmpCode, InTime AS InTime1, OutTime as OutTime1,
LEAD(InTime) OVER (PARTITION BY EmpCode ORDER BY Id) AS InTime2,
LEAD(OutTime) OVER (PARTITION BY EmpCode ORDER BY Id) AS OutTime2,
Notes
FROM #shifts2
)
SELECT EmpCode,InTime1,OutTime1,InTime2,OutTime2,
isnull(DATEDIFF(hh, InTime1,OutTime1),0) + isnull(DATEDIFF(HH, InTime2, OutTime2),0) AS TotalHours
, Notes
FROM res
WHERE rn = 1
Results:
emp100 2017-05-18 10:00 2017-05-18 15:00 2017-05-18 17:00 2017-05-18 20:00 8
emp102 2017-05-18 06:00 2017-05-18 10:00 NULL NULL 4
emp103 2017-05-18 07:00 NULL 2017-05-18 10:00 NULL 0 Error