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)
 Can this be done in SQL

Author  Topic 

mole999
Starting Member

49 Posts

Posted - 2014-09-21 : 10:39:46
in Excel one way is =IFERROR(MROUND(IF(J3>K3,J3-K3,L3-J3),"0:30"),"")

column Event (time), column Start (time), column End (time)

Start and End contain time as a shift so can run over midnight

Event is a time between Start and Finish output appears in Hours_OnDuty, the output is rounded to 30 minute windows

I'm not sure how to attempt the comparison and get a displayable result, I would prefer to do it in SQL, currently looking at 19,000 rows

thoughts, references or pointers would be appreciated



Mole

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-21 : 17:19:51
Basically, you'll want a case statement like this, assuming J = Event, K = Start and L = End

Note the query below could be refactored into one expression. I broke it out using two cross applies to help make it more readable.


declare @Event TIME = '12:13';
declare @Start TIME = '12:01';
declare @End TIME = '13:00';

select ev, st, en, c.diff, e.diff_rounded
from (values (@Event, @Start, @End)) v(ev, st, en)

cross apply (
select case
when ev > st then datediff(minute, st, ev)
else datediff(minute, ev, en)
end
) c(diff)

cross apply (
select 30*cast((c.diff+30)/60 as int)
) e(diff_rounded)
Go to Top of Page

mole999
Starting Member

49 Posts

Posted - 2014-09-22 : 00:47:47
thank you very much, i will have to play with this so i can understand how this work

Mole
Go to Top of Page

mole999
Starting Member

49 Posts

Posted - 2014-09-28 : 05:12:21
quote:
Originally posted by gbritton

Basically, you'll want a case statement like this, assuming J = Event, K = Start and L = End

Note the query below could be refactored into one expression. I broke it out using two cross applies to help make it more readable.


declare @Event TIME = '12:13';
declare @Start TIME = '12:01';
declare @End TIME = '13:00';

select ev, st, en, c.diff, e.diff_rounded
from (values (@Event, @Start, @End)) v(ev, st, en)

cross apply (
select case
when ev > st then datediff(minute, st, ev)
else datediff(minute, ev, en)
end
) c(diff)

cross apply (
select 30*cast((c.diff+30)/60 as int)
) e(diff_rounded)




I fianlly managed to get the middle bit to work
(as you can see not held as a time in the database)
  
case
when convert(datetime,RTC.time_1) > convert(datetime,RTC.shift_times) then datediff(minute, convert(datetime,RTC.shift_times), convert(datetime,RTC.time_1))
else datediff(minute, convert(datetime,RTC.time_1), convert(datetime,RTC.shift_times_2 ))
end
as c


I can make no sense of the cross apply though, I would still like to get 30 minute rounding in sql rather than excel if I can

Mole
Go to Top of Page
   

- Advertisement -