December 11, 2010 at 3:37 am
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)
December 11, 2010 at 5:54 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1033289-391-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 11, 2010 at 6:42 am
if shorttime in hh:mm format then just used:
select CAST((cast((datediff(minute,[EmpSIN],[EmpSOut])-datediff(minute,[FirstIn],[FirstOut]))/60 as varchar(10))+ ':' + cast((datediff(minute,[EmpSIN],[EmpSOut])-datediff(minute,[FirstIn],[FirstOut]))% 60 as varchar(10))) AS TIME),
December 11, 2010 at 1:45 pm
Bharat Panthee (12/11/2010)
if shorttime in hh:mm format then just used:select CAST((cast((datediff(minute,[EmpSIN],[EmpSOut])-datediff(minute,[FirstIn],[FirstOut]))/60 as varchar(10))+ ':' + cast((datediff(minute,[EmpSIN],[EmpSOut])-datediff(minute,[FirstIn],[FirstOut]))% 60 as varchar(10))) AS TIME),
Heh... I guess "No replies to this thread please" doesn't mean anything to some folks. 😉
Take a look at Gail's post where she identifies the "other" post. The reason why us old-timers do this is to keep the answers and discussions for a given problem on a single post. That way, it's easier for others trying to find a similar answer, it keeps the discussion all in one place, and it keeps people from answering a question that's already been answered. It would be much appreciated if you'd honor such requests to post on the other thread in the future. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply