That bit of your query
Case when DATEDIFF(Hour ,MIN(Date),Max(Date))-8 and end
as [OT],
is not finished. But there is no need for a
CASE
, just calculate the overtime.
Your next problem is that you are trying to do everything at once, break it down so that you can see what is actually happening.
For example I used this sample data
DECLARE @HR2 AS TABLE (Empcode INT, [Date] datetime, INOUT INT)
INSERT INTO @HR2 (Empcode, [Date], INOUT) VALUES
(1, '2018-12-03 09:00:00', 1),
(1, '2018-12-03 18:00:00', 2),
(1, '2018-12-04 09:00:00', 1),
(1, '2018-12-04 17:00:00', 2)
If I run this query …
SELECT Empcode, CAST([Date] as Date) AS [Date],
CASE WHEN INOUT = 1 THEN [Date] END AS INOUT_INTIME,
CASE WHEN INOUT = 2 THEN [Date] END AS INOUT_OUTTIME
FROM @HR2
I get the following results
Date INTIME OUTTIME
1 2018-12-03 2018-12-03 09:00:00.000 NULL
1 2018-12-03 NULL 2018-12-03 18:00:00.000
1 2018-12-04 2018-12-04 09:00:00.000 NULL
1 2018-12-04 NULL 2018-12-04 17:00:00.000
NOW I can do my MIN/MAX etc on that sub-query
select Empcode, [Date], MAX(INOUT_INTIME) AS INOUT_TIME, MAX(INOUT_OUTTIME) AS INOUT_TIME
FROM
(
SELECT Empcode, CAST([Date] as Date) AS [Date],
CASE WHEN INOUT = 1 THEN [Date] END AS INOUT_INTIME,
CASE WHEN INOUT = 2 THEN [Date] END AS INOUT_OUTTIME
FROM @HR2
) SubQury
GROUP BY Empcode, [Date]
Which changes my results to look like
Date INTIME OUTTIME
1 2018-12-03 2018-12-03 09:00:00.000 2018-12-03 18:00:00.000
1 2018-12-04 2018-12-04 09:00:00.000 2018-12-04 17:00:00.000
I could add in all the other stuff as well
, DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as Hours
, CASE WHEN DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) >= 8 THEN 1
WHEN DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) = 0 THEN 0
WHEN DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) >= 6 THEN 0.5 END AS [Day],
DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) - 8 as OT
But that is starting to look ugly and I'm doing the same calculation 4 times which is not very efficient. I prefer to use Common Table Expressions … e.g. like this …
;WITH cte1 AS
(
SELECT Empcode, CAST([Date] as Date) AS [Date],
CASE WHEN INOUT = 1 THEN [Date] END AS INOUT_INTIME,
CASE WHEN INOUT = 2 THEN [Date] END AS INOUT_OUTTIME
FROM @HR2
), cte2 as
(
select Empcode, [Date], MAX(INOUT_INTIME) AS INTIME, MAX(INOUT_OUTTIME) AS OUTTIME
, DATEDIFF(Hour, MAX(INOUT_INTIME), MAX(INOUT_OUTTIME)) as [Hours]
FROM CTE1
GROUP BY Empcode, [Date]
)
select Empcode, [Date], INTIME, OUTTIME, [Hours]
, CASE WHEN [Hours] >= 8 THEN 1
WHEN [Hours] = 0 THEN 0
WHEN [Hours] >= 6 THEN 0.5 END AS [Day],
[Hours] - 8 as OT
from cte2
Now is the time you need to look at your [Day] calculation … basically you are saying if someone works 8 or more hours they have worked 1 day. If someone has worked no hours at all then they have worked 0 days. But they only get credited with working a half day if they do more than 6 hours?
Sounds a bit unfair on the guys who have worked 4 or more hours … i.e. half a standard day :laugh: