How to hours, calculate day , overtime from in and OUT time

Question

0.00/5 (No votes)

See more:

i have IN Time and Out Time ,in which i am calculating Hours from IN and Out Time,Now i want that when hours =8 then Day will be 1 and if Hours is more then to 8hours(for example :12hours minus from 8hours =4hours then 4hours multiply to 100rate)

i want store procedure ...

What I have tried:

SELECT     INTime, Outtime, DATEDIFF(Hour, INTime, Outtime) AS Hours
if
Hours = 8
FROM         EmployeeAttedance

Posted 25-Dec-18 1:02am

Member 12314309

Updated 25-Dec-18 4:21am

Add a Solution

Comments

OriginalGriff 25-Dec-18 7:16am

And?
What have you tried, and what happened when you tried it?
What help do you need?

Member 12314309 25-Dec-18 7:25am

i tried this (SELECT INTime, Outtime, DATEDIFF(Hour, INTime, Outtime) AS Hours
FROM EmployeeAttedance)
Now i want day calculate if Hour=8 then day will be 1

OriginalGriff 25-Dec-18 7:44am

Yes, I guessed that from your question...

And what have *you* tried to actually do that?

Member 12314309 25-Dec-18 7:54am

check this Declare
@Hou int
@Hours int
Set @Hou=8

SELECT INTime, Outtime, DATEDIFF(Hour, INTime, Outtime) AS Hours,
if
@Hours= @Hou then Day="1"

FROM EmployeeAttedance

OriginalGriff 25-Dec-18 8:01am

And? What happened when you tried it?

Member 12314309 25-Dec-18 8:13am

it is not executing and giving error

OriginalGriff 25-Dec-18 9:23am

That's helpful.
And particular error or just "+++OUT OF CHEESE ERROR AT 14, TREACLE MINE ROAD, ANKH MORPORK+++"?

And what have you done to find out why it's giving an error?

#realJSOP 25-Dec-18 9:58am

This is getting painful to watch...

OriginalGriff 25-Dec-18 10:58am

I know the feeling...

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)

MadMyche · Accepted Answer · 2018-12-25T04:21:00

Solution 1

I will fix the SQL statement that you have

SQL

DECLARE   @punches TABLE ( PunchIn time, PunchOut time )
INSERT @punches VALUES ('07:58', '16:15')

SELECT	PunchIn, PunchOut
	,	[Hours] = DateDiff(Hour, PunchIn, PunchOut) 
	,	[Days] = CASE WHEN  DateDiff(Hour, PunchIn, PunchOut) >= 8 THEN 1 ELSE 0 END
FROM @punches

I must let you know that there are a few potential flaws in your logic.
1- If someone works a "double-shift" for 16 hours, would this be 2 days?
2- What about someone working an overnight shift?
3- If someone did work 1 day, did you want the hours reduced by 8?

Posted 25-Dec-18 4:21am

MadMyche

Comments

#realJSOP 25-Dec-18 14:07pm

It doesn't matter how many shifts they work. What matters is how many HOURS they work in a week. Anything over 40 hours is considered overtime. Maybe his instructor didn't explain the problem well enough.

MadMyche 25-Dec-18 17:58pm

My goal was to give a basic fix for the OP's problem and some situations to think about.
And 40 hours is not the only definition of overtime. Several US states have it as 8 hour shifts.

Member 12314309 26-Dec-18 8:41am

thanks for help...my futher point is that if Hours is more then to 8 hours like 12hours then it get minus from standard duty hours which is 8 hours and multiply to standard OT rate which is 100 rate (12hour - 8hours=4hours) 4hours*100=400 which is Overtime amount =400

MadMyche 26-Dec-18 13:09pm

As my previous reply stated, I was just helping with your SQL error. I also knew your question was short sighted and that more was going to be needed; hence the concerns in my answer. e storing the punch times in the DB. Your application should retrieve that information and do the actual calculations and reporting.