14,643,391 members
Rate this:
See more:
I have two table

Employee master table and another one Employee Attendance Table

No i have calculate Hours and Day on the basis of INTIME and OUTTIME ,
Now i want to calculate Overtime Hours From Hours(Hours-8Hours(standard Hours)),overtime will be calculated then i want add condition that if employee is allowed to avail OVertime then employee overtime will be calculated other wise will not calculated.Like (Overtime * 100(Overtime Rate).

`If Hours =0 then their OT will be =0 but value are coming in minus.`

What I have tried:

```SELECT Empcode,cast(Date as date) As [Date],
MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
DATEDIFF(Hour ,MIN(Date),Max(Date)) AS [Hours],
case when DATEDIFF(Hour ,MIN(Date),Max(Date)) >= 8 Then 1
when DATEDIFF(Hour ,MIN(Date),Max(Date))  = 0 then 0
when DATEDIFF(Hour ,MIN(Date),Max(Date))  >=6 then 0.5
end as [Day],
Case when DATEDIFF(Hour ,MIN(Date),Max(Date))-8 and end
as [OT],

FROM HR2 AS t
GROUP BY Empcode,CONVERT(date, date);```
Posted
Updated 3-Jan-19 22:22pm
v3
#realJSOP 3-Jan-19 6:40am

One of your classmates asked this question a couple of weeks ago.
Richard MacCutchan 3-Jan-19 7:22am

What is the question?
CHill60 3-Jan-19 8:19am

Provide some sample data and your expected results. Then explain what the problem is.
Use the Improve question link to the add the information to your question
akhter86 4-Jan-19 2:30am

Lolz,,,thanks for comment
akhter86 4-Jan-19 2:41am

@Chill60 i tried to defined you ,if you have any query please ask
CHill60 4-Jan-19 5:23am

Your question is still not any clearer. Give us some sample data - the data YOU are using, and the results that you are expecting. Only then can I work out why my solution works for me but not for you.
You should also update your question with the code you are now using.

Rate this:

## Solution 1

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:
Wendelius 3-Jan-19 11:21am

Nice!
Maciej Los 4-Jan-19 8:06am

Wow!
Caroline, you deserve for new nick: SqlPedia (Sql + encyclopedia) ;)
5ed!
CHill60 4-Jan-19 9:03am

LOL! Thanks Maciej
akhter86 5-Jan-19 0:27am

Thanks for sharing CTE

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 357 Sandeep Mewara 147 Richard MacCutchan 130 Richard Deeming 130 CPallini 102
 OriginalGriff 6,171 Sandeep Mewara 3,278 Richard MacCutchan 2,112 Richard Deeming 1,904 CPallini 1,839

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100