F-corr Trick: Sort time by half-hour

Nathan Rector

Natec Systems



Have you ever needed to sort and break on times in half-hour increments? If you have ever needed to run sorts on when transaction was processed, you have probably run across this need.

The time correlative naturally allows you to sort by the hour, but what if you need to sort by half- hour increments or 15 minute increments? For example, how many retail cash transactions have been processed in the first few 30 minute increments?

Here is little correlative that does it for you.



This looks like a complex correlative, but when broken apart, it is very simple. Let's start by looking at what the correlative actually builds to get the result. Then I will break down each part of it so you can see how the end result was achieved.

time = "05:25"

if time.mins < 30 then pos = 1

if time.mins < 60 then pos = 2

result = "05:0005:30"[pos * "6",5]

Let's look at the first part of the correlative:

33;(MT);(G0:1);":00";:;(ML(#5)) = 05:00

33;(MT);(G0:1);":30";:;(ML(#5)) = 05:30

33;(MT);(G0:1);":00";:;(ML(#5));33;(MT);(G0:1);":30";:;(ML(#5));: = 05:0005:30

Now let's take a look at the actual selection part of the correlative:

33;(MT);(G1:1);"30";[ = if minutes < 30 then pos = 1

33;(MT);(G1:1);"30";[;"6";* = (if minutes < 30 then pos = 1) * "6"

;"5";[] = "05:0005:30"[pos * "6";5]


Time Half.Hour

04:33PM 16:30 CH00257311A

12:01PM 12:00 CH00257429A

01:18PM 13:00 CH00257488A

03:20PM 15:00 CH00423530

11:03AM 11:00 CH00454741

10:40AM 10:30 CH00257665A

09:36AM 09:30 CH00257134A

09:17AM 09:00 CH00454682

02:57PM 14:30 CH00257783A

01:06PM 13:00 CH00485480

01:20PM 13:00 CH00257494A

02:42PM 14:30 CH00257553A

10:44AM 10:30 CH00257671A

09:51AM 09:30 CH00257140A

03:13PM 15:00 CH00257789A

04:45PM 16:30 CH00257317A

12:17PM 12:00 CH00257435A

03:33PM 15:30 CH00485540

10:02AM 10:00 CH00257146A

01:15PM 13:00 CH00257736A

This correlative can be expanded to allow you to sort and break on by any number of minute

increments with a little modification.