Hi, I have a Base DB at work (HSQ) which has a table populated with thousands of records, one of the fields is a Date field. I need to find some way of querying the difference between each successive record, so “DateDiff” I guess. But how can I compare between record 1 and 2, then 2 and 3 etc etc.
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 @PYS by changing date on rec id #4 to 03/15/10. Using routine by Toussi here is result:
Attached is modified sample from @PYS and includes query RecordDayDiff and appropriate Views created for this.
Sample ---- 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 DateDiffBetweenRecords.odb):
HTH - Regards
@PYS I DO think this your solution is very good but it isn’t correct for the question presented. Q1 deals with dates which are ‘Greater than’ which may not be case. For example, changing rec id #4 to 03/15/10 will result in it being last in the list and incorrect difference from the actual previous record. It then is not calculating the difference between itself & the actual last record. Routine by Toussi accounts for this as it goes strictly by sequence of records.
Hi @Ratslinger,
It’s been a while that I use sequences. I wanted to propose simpler because, but you’re right, I misinterpreted this:
querying the difference between each successive
Regards