Adding time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Adding time

Author

Topic

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-11-27 : 06:34:16

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 06:57:56

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

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-11-27 : 07:07:08

Absolutely brilliant. thank you very much again.

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-11-27 : 07:22:49

;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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-27 : 10:43:37

[code]
;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

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-11-27 : 12:06:31

thank you agian .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources