DateDiff between records on same table

edit

Hello,

One item you do not state is the HSQL version used. If the embedded v1.8 (Base default) it is very old and does not offer the capabilities of newer DB's.

Please look at the following post -> HSQLDB v1.8: Help having two records per row returned by the Select statement . Fred Toussi offers what seems to be what you need - using a 'Sequence' and possibly a View. Even shows a 'Select' statement (not quite what you need but close).

Another approach there is from Sliderule Smith (also links in one of the comments).

Edit 2018-06-16:

Modified sample from @pierre-yves samyn by changing date on rec id #4 to 03/15/10. Using routine by Toussi here is result:

Attached is modified sample from @pierre-yves samyn and includes query RecordDayDiff and appropriate Views created for this.

Sample ---- C:\fakepath\Rec1VSRec2DateDiff.odb

Hi

This question reminded me this one rather old (OMG 12 years already?)

You can get the result by the sequence of 3 queries.

• The first relates the table to itself and calculates the number of days.
• The second limits the result to the smallest date.
• The third relates the first two to get the desired result

In the following example, the table contains counter index statements.

What we want (run Q3 in C:\fakepath\DateDiffBetweenRecords.odb):

HTH - Regards