get overtime hours

Question

get overtime hours

sugiarttampubolon

SSC Enthusiast

Points: 110
More actions
April 18, 2013 at 3:18 am

#274176

i am have a problem to get overtime hours
the table
EmployeeNo TimeIn TimeOut BreakHours WorkingHours
001 06:20 19:00 1 8
how query to get overtime hours
note : datatype Timein, timeout, BreakHours,WorkingHours is decimal(6,3)
OverTime = (Timein + timeout) - BreakHours - WorkingHours

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

sugiarttampubolon (4/18/2013)
i am have a problem to get overtime hours
the table
EmployeeNo TimeIn TimeOut BreakHours WorkingHours
001 06:20 19:00 1 8
how query to get overtime hours
note : datatype Timein, timeout, BreakHours,WorkingHours is decimal(6,3)
OverTime = (Timein + timeout) - BreakHours - WorkingHours

Hello and welcome to SQL Server Central!

Could you supply sample data, DDL and expected results as per this article --> http://www.sqlservercentral.com/articles/Best+Practices/61537/ ?[/url] If you do, it really helps the unpaid volunteers of this website to help solve your issue or point you in the right direction.

Thanks.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

Decimals are not a good data type to store time

You have given 06:20 and 19:00 as sample data which doesn't look like decimals

Are you sure the data type is decimal and not something else?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

sugiarttampubolon SSC Enthusiast Points: 110 More actions · Answer 3

i am sure the data is decimal(6,3)..

the detail is make a store procedure from 2 database

master_db and temporary_db but i cannot touch the source program just make stored procedure to transfer data

from temporary_db to master_db

table Overtimetransaction in temp_db

Emp_No OverTimeHours

001 2.5

tbl absent in master_db

Emp_No TimeIn TimeOut BreakHours TotalWorkingHours OverTimeHours

001 06:20 19:10 1 8

so when the data transfer i must make validation to read total from (timein + timeout - BreakHours - WorkingHoursperday)

in table absent in master_db, if same, overtimehours in temp_db transfer to tbl absent in master_db if not same not transfer

thanks...please help

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 4

sugiarttampubolon (4/18/2013)
i am have a problem to get overtime hours
the table
EmployeeNo TimeIn TimeOut BreakHours WorkingHours
001 06:20 19:00 1 8
how query to get overtime hours
note : datatype Timein, timeout, BreakHours,WorkingHours is decimal(6,3)
OverTime = (Timein + timeout) - BreakHours - WorkingHours

The formula for overtime does not look right to me either.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

sugiarttampubolon (4/18/2013)
i am sure the data is decimal(6,3)..
the detail is make a store procedure from 2 database
master_db and temporary_db but i cannot touch the source program just make stored procedure to transfer data
from temporary_db to master_db
table Overtimetransaction in temp_db
Emp_No OverTimeHours
001 2.5
tbl absent in master_db
Emp_No TimeIn TimeOut BreakHours TotalWorkingHours OverTimeHours
001 06:20 19:10 1 8
so when the data transfer i must make validation to read total from (timein + timeout - BreakHours - WorkingHoursperday)
in table absent in master_db, if same, overtimehours in temp_db transfer to tbl absent in master_db if not same not transfer
thanks...please help

Can you provide us the DDL of your "tbl absent in master_db"?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

chalam87 Right there with Babe Points: 771 More actions · Answer 6

sugiarttampubolon (4/18/2013)
i am have a problem to get overtime hours
the table
EmployeeNo TimeIn TimeOut BreakHours WorkingHours
001 06:20 19:00 1 8
how query to get overtime hours
note : datatype Timein, timeout, BreakHours,WorkingHours is decimal(6,3)
OverTime = (Timein + timeout) - BreakHours - WorkingHours

hi sugiarttampubolon,

you can't store 06:20 value in decimal datatype.

--chalam

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

batgirl (4/18/2013)
sugiarttampubolon (4/18/2013)
i am have a problem to get overtime hours
the table
EmployeeNo TimeIn TimeOut BreakHours WorkingHours
001 06:20 19:00 1 8
how query to get overtime hours
note : datatype Timein, timeout, BreakHours,WorkingHours is decimal(6,3)
OverTime = (Timein + timeout) - BreakHours - WorkingHours
The formula for overtime does not look right to me either.

I would agree. TimeOut - TimeIn gives total hours at work. Subtract BreakTime gives total hours worked, Subtract WorkingHours leaves OvertimeHours.