Using calculated columns:

Inform and RetrieVe are great tools for performing calculations on data. There are times when calculations cannot be performed on the data contained in single records, but instead requires the data from more than one record to complete some calculation. For example, if the database contains records denoting the begin and end times of events and one is tasked with determining the total time BETWEEN events, there is no method for performing this that doesn't require some programming.
Lately instead of writing one-off programs for such tasks I have been using I-types to call subroutines. Below is an example:

Note: All programming displayed on this site is for illustrative purposes only. Use at your own risk.

Here is a sample set of my test data containing events and their respective start date, start time, and end time:

---

>LIST EVENTS WITH DATE GE "01/01/2016" BY DATE BY START.TIME BY @ID DATE START.TIME END.TIME HDR.SUP COL.SPCS SAMPLE 10

EVENT. DATE.... START... END.....

160001 01/22/16 15:07:28 15:13:35

160002 01/22/16 15:13:39 15:14:31

160003 01/22/16 15:29:57 15:32:03

160004 01/22/16 15:32:22 15:34:39

160005 01/22/16 16:04:44 16:06:04

160006 01/27/16 11:21:34 11:22:13

160007 02/19/16 16:35:24 16:36:55

160008 04/01/16 13:27:55 13:32:58

160009 04/01/16 13:29:23 13:33:04

160010 04/04/16 08:22:41 08:57:05

Sample of 10 records listed.

>

---

A new I-type that will output the elapsed time between the current and previous event will be useful if different queries (selection criteria, sort criteria, etc.) will be run. For example:

---

>LIST DICT EVENTS "DOWNTIME" F1 F2 F3 F4 F5 F6 F7

Field}Name. DOWNTIME

F1......... I Calculate time between calls

F2......... SUBR('DOWNTIME',DATE,START.TIME,END.TIME)

F3.........

F4......... Downtime}Days:HH:MM:SS

FORMAT..... 13R

F6......... S

F7.........

1 records listed.

>

---

The corresponding subroutine that is referenced in the above I-type looks like the following:

---

subroutine downtime(out,begin.date,begin.time,end.time)

common /downtime/ prev.end.date,prev.end.time

out=""

if @RECCOUNT <= 1 then ;* Reinitialize when new query

   prev.end.date=end.date

   prev.end.time=end.time

end else

   numdays=(begin.date-prev.end.date) ;* Number of days

   if begin.date > prev.end.date then ;* If spans midnight

      new.time=86400-prev.end.time    ;* Previous end time to midnight

      end.time.orig=end.time

      prev.end.time=new.time

      out=numdays:":":oconv(prev.end.time,"MTS")

      prev.end.time=end.time.orig

      prev.end.date=end.date

   end else

      if begin.time > prev.end.time then

         numhrs=begin.time-prev.end.time

      end else numhrs=0

      out=numdays:":":oconv(numhrs,"MTS")

      prev.end.date=end.date

      prev.end.time=end.time

   end
end

return

end

---

Rerunning the same query but with the new I-type produces the elapsed time between events (with overlapping events indicating zero downtime):

---

>LIST EVENTS WITH DATE GE "01/01/2016" BY DATE BY START.TIME BY @ID DATE START.TIME END.TIME DOWNTIME HDR.SUP COL.SPCS SAMPLE 10

                                  Downtime
EVENT. DATE.... START... END..... Days:HH:MM:SS

160001 01/22/16 15:07:28 15:13:35    0:00:00:00

160002 01/22/16 15:13:39 15:14:31    0:00:00:03

160003 01/22/16 15:29:57 15:32:03    0:00:15:25

160004 01/22/16 15:32:22 15:34:39    0:00:00:19

160005 01/22/16 16:04:44 16:06:04    0:00:30:05

160006 01/27/16 11:21:34 11:22:13    5:07:53:55

160007 02/19/16 16:35:24 16:36:55   23:12:37:47

160008 04/01/16 13:27:55 13:32:58   42:07:23:04

160009 04/01/16 13:29:23 13:33:04    0:00:00:00

160010 04/04/16 08:22:41 08:57:05    3:10:26:56

Sample of 10 records listed.

>

---

Notice that there is no downtime for the first incident as there is no prior or previous event.