Lean SQLing ;-)

[datanibbler]

Hi,

is there any way I can make this rather complex expression (within a LOAD statement) simpler?

if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'0730','07:30',
  if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1000','10:00',
   if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1200','12:00',
    if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1400','14:00',
     if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1600','16:00',
      if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'1830','18:30',
        if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'2030','20:30',
          if(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'2230','22:30','22:30+')))))))) as Interval_2H,

The difficult thing is that these are actually not regular 2hr-intervals as the name suggests - see the 30min delay between 7:30 am and 10am and the one between 4pm and 6:30pm - so I cannot use the BUCKET method proposed by Henric Cronstroem, can I?

I will try - but that is an independent thing - to store the times in some central place and just load them into the app as variables.

First, however, I would like to get all this a bit leaner and more elegant since this is part of a report that is ultimately supposed to run every 10min.

Thanks a lot!

Best regards,

DataNibbler

[marcus_malinow]

I'd possibly do this using an inline load for the intervals, and an intervalmatch.

For example

Intervals:

LOAD * INLINE [

From, To, InterValName

00:00, 07:30, 07:30

etc...

];

INTERVALMATCH (END_TIME) LOAD From, To RESIDENT Intervals;

[hic · Accepted solution]

I would also use IntervalMatch.

But why do you use concatenation of hour and minute? The expression

   If(num(hour(END_TIME),'00') & num(minute(END_TIME),'00')<'0730','07:30')

can be written

   If(END_TIME<'07:30','07:30')

which simplifies your expression a lot...

HIC

[datanibbler]

Thanks!

That is just the sort of thing I was looking for.

I cannot test it right now, we're experiencing major server troubles today, but I will edit the code accordingly. Every little helps in this case.

Best regards,

DataNibbler

[marcus_malinow]

Really don't know why I bother sometimes...

[datanibbler]

Might be because you're one of the nice guys 😉

That's how it is - in other online Communities you spend some time waiting for any answers, here it's a race for who answers first 😉