F-corr Trick: Sort time by half-hour
Nathan Rector
Natec Systems
nater@northcoast.com
http://www.northcoast.com/~nater/
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.
F;33;(MT);(G0:1);":00";:;(ML(#5));33;(MT);(G0:1);":30";:;(ML(#5));:
33;(MT);(G1:1);"60";[;33;(MT);(G1:1);"30";[;+;"6";*;"5";[]
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]
:LIST RET.CASH TIME.ENTRY HALF.HOUR REC.NO (I
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.