time difference in hh.mm

Question

time difference in hh.mm

pnasz

SSC Rookie

Points: 44
More actions
December 11, 2010 at 3:14 am

#230426

I have one computed column Shorthours.
In this computed column i want to display the result in hh.mm
empid FirstIn FirstOut EMPSIN EMPSOut hrworked shorthr
100 08:35:00 18:01:00 08:00:00 18:00:00 9.26 0.74
firstin,firstout is time in and timeout for emp
EMPSIN,EMPSOUt is expected timein and timeout for emp
firstin,firstout,empsin,empsout are in 00:00:00 format so to get the shorthr in time format hh.mm
i am getting the answer 0.74 (answer should be 1.14) using the formula below :
help needed
(case when (0)>((CONVERT([float],datediff(minute,[EmpSIN],[EmpSOut])/(60),(0))+CONVERT([float],(datediff(minute,[EmpSIN],[EmpSOut])%(60))/(100.0),(0)))-(CONVERT([float],datediff(minute,[FirstIn],[FirstOut])/(60),(0))+CONVERT([float],(datediff(minute,[FirstIn],[FirstOut])%(60))/(100.0),(0)))) then (0) else (CONVERT([float],datediff(minute,[EmpSIN],[EmpSOut])/(60),(0))+CONVERT([float],(datediff(minute,[EmpSIN],[EmpSOut])%(60))/(100.0),(0)))-(CONVERT([float],datediff(minute,[FirstIn],[FirstOut])/(60),(0))+CONVERT([float],(datediff(minute,[FirstIn],[FirstOut])%(60))/(100.0),(0))) end)

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Please provide ddl (CREATE TABLE...) script and dml (INSERT INTO...) script which when run will create and populate a table with a few rows of representative data.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

WayneS SSC Guru Points: 95409 More actions · Answer 2

Employee was expected to work for 10 hrs (1800-0800), but actually worked for 9.26, leaving a shortage of .74 hours. What's the issue here?

(Actually, worked will be 9.566666 (work 9:34: 34/60=.566666666666666666667), so your hrworked calculation is wrong. This means a shortage of .43)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 3

pnasz (12/11/2010)
I have one computed column Shorthours.
In this computed column i want to display the result in hh.mm
empid FirstIn FirstOut EMPSIN EMPSOut hrworked shorthr
100 08:35:00 18:01:00 08:00:00 18:00:00 9.26 0.74
firstin,firstout is time in and timeout for emp
EMPSIN,EMPSOUt is expected timein and timeout for emp
firstin,firstout,empsin,empsout are in 00:00:00 format so to get the shorthr in time format hh.mm
i am getting the answer 0.74 (answer should be 1.14) using the formula below :
help needed
(case when (0)>((CONVERT([float],datediff(minute,[EmpSIN],[EmpSOut])/(60),(0))+CONVERT([float],(datediff(minute,[EmpSIN],[EmpSOut])%(60))/(100.0),(0)))-(CONVERT([float],datediff(minute,[FirstIn],[FirstOut])/(60),(0))+CONVERT([float],(datediff(minute,[FirstIn],[FirstOut])%(60))/(100.0),(0)))) then (0) else (CONVERT([float],datediff(minute,[EmpSIN],[EmpSOut])/(60),(0))+CONVERT([float],(datediff(minute,[EmpSIN],[EmpSOut])%(60))/(100.0),(0)))-(CONVERT([float],datediff(minute,[FirstIn],[FirstOut])/(60),(0))+CONVERT([float],(datediff(minute,[FirstIn],[FirstOut])%(60))/(100.0),(0))) end)

What are the datatypes of the time columns?

Also, since you're new to this forum, you should probably set aside a little time and read the article at the first link in my signature line below. It WILL help you get better answers more quickly.

I'll also state that your formula above can be greatly simplified... but we need to know what the datatypes are first AND it would be really helpful if you posted some sample data in the readily consumable format that's outlined in the article I suggested that you study and follow.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 4

Never mind... someone posted an answer on the cross post at http://www.sqlservercentral.com/Forums/Topic1033293-145-1.aspx even though Gail asked folks to post here.

Moving on.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)