Click here to Skip to main content
14,640,736 members
Rate this:
Please Sign up or sign in to vote.
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
Hours = 8
FROM         EmployeeAttedance
Updated 25-Dec-18 4:21am
OriginalGriff 25-Dec-18 7:16am
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,
@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

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

Solution 1

I will fix the SQL statement that you have
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?
#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 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.

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