How to calculate overtime and amount in SQL

Question

1.00/5 (1 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 3-Jan-19 0:06am

akhter86

Updated 3-Jan-19 22:22pm

v3

Add a Solution

Comments

#realJSOP 3-Jan-19 6:40am

One of your classmates asked this question a couple of weeks ago.

akhter86 4-Jan-19 2:30am

Lolz,,,thanks for comment

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2019-01-03T03:08:00

That bit of your query

SQL

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

SQL

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 …

SQL

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

SQL

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

SQL

, 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 …

SQL

;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: