14,640,736 members
Rate this:
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
Updated 25-Dec-18 4:21am
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

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

Rate this:

## 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.

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

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.