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

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)

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources