Click here to Skip to main content
14,643,391 members
Rate this:
Please Sign up or sign in to vote.
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
Comments
#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.

1 solution

Rate this:
Please Sign up or sign in to vote.

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:
   
Comments
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)




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