SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Adding time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Muj9
Starting Member

United Kingdom
49 Posts

Posted - 11/27/2013 :  06:34:16  Show Profile  Reply with Quote
I used this to convert the time field in to time. then into a new table.

,case when [End time] > 0 then [Event Date] + convert(datetime, left(right(('0' + convert(varchar(4), [End time])),4),2) + ':' + right(('0' + convert(varchar(4),
[End time])),2)) end as [EndTime]

on the new table i did this which output time like this
,convert(varchar(10),[EndTime],108) as [EndTime]

14:00:00
15:00:00
10:40:00

So what my problem is, i have a table below. i need to find out the total time spent in hours and mins on rooms


EventDate Room BookedTime EndTime
2013-11-06 00:00:00.000 A3 13:00:00 NULL
2013-11-06 00:00:00.000 A4 NULL 16:30:00
2013-11-06 00:00:00.000 A4 15:00:00 16:30:00


so far i have done this but not sure if it is producing the right results.


; with cte as (
select Room,
case when bookedtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime)
when Endtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime) else null end as Mins
from xxxxx
where case when bookedtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime)
when Endtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime) else null end is not null and
case when bookedtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime)
when Endtime is not null then DATEDIFF(MINUTE ,bookedtime,Endtime) else null end > 0
) select Room,
SUM(60*FLOOR(Mins) + 100*(Mins-FLOOR(Mins)) ) / 60 [HoursSpent]
from cte
group by
Room

Result
--------------------

Room HoursSpent
A1 3219
A2 2272
A3 3579
A4 4393


If it is in time how do i show in time so its not exceding 60mins
For room A2 it cant be 22 hrs abd 72mins
so how can i get the results in hours and mins????


Thnaks in Advance

Edited by - Muj9 on 11/27/2013 06:40:02

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  06:57:56  Show Profile  Reply with Quote
take the total diffrence of time in minutes using DATEDIFF and then /60 to get hours etc
ie something like

DATEDIFF(minute,bookedtime,Endtime)/60 AS Hours,DATEDIFF(minute,bookedtime,Endtime)% 60 AS Minutes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Muj9
Starting Member

United Kingdom
49 Posts

Posted - 11/27/2013 :  07:07:08  Show Profile  Reply with Quote
Absolutely brilliant. thank you very much again.
Go to Top of Page

Muj9
Starting Member

United Kingdom
49 Posts

Posted - 11/27/2013 :  07:22:49  Show Profile  Reply with Quote
;with cte as (
select
Eventdate
,Room
,DATEDIFF(MINUTE,StartTime,Endtime)%60 as [Diff_In_Mins]
,Hosp_Cancel
from dbo.FinalAngioTemplate)
select Room,sum(Diff_In_Mins) as Total_Mins from cte
group by Room


Room Total_Mins
A1 1472
A2 1433
A3 2044
A4 1396


if i use select Room,sum(Diff_In_Mins)/ 60 as Total_Mins from cte i get the hours but lose the mins how to get hours and mins ?


Room Total_Mins
A1 24
A2 23
A3 34
A4 23

Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/27/2013 :  10:43:37  Show Profile  Reply with Quote

;with cte as ( 
select 
Eventdate
,Room
,DATEDIFF(MINUTE,StartTime,Endtime)as [Diff_In_Mins]
,Hosp_Cancel
from dbo.FinalAngioTemplate)
select Room,sum(Diff_In_Mins)/60 as Total_Hrs,
sum(Diff_In_Mins)%60 AS Total_Mins
from cte 
group by Room



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Muj9
Starting Member

United Kingdom
49 Posts

Posted - 11/27/2013 :  12:06:31  Show Profile  Reply with Quote
thank you agian .
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000