Ask Your Question
0

DateDiff between records on same table

asked 2018-06-15 12:10:19 +0200

Zoarsk gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2018-06-15 22:43:45 +0200

Ratslinger gravatar image

updated 2018-06-16 19:33:02 +0200

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:

image description

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

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

edit flag offensive delete link more
0

answered 2018-06-16 08:26:12 +0200

pierre-yves samyn gravatar image

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.

Table1

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

Q3

HTH - Regards

edit flag offensive delete link more

Comments

@pierre-yves samyn 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.

Ratslinger gravatar imageRatslinger ( 2018-06-16 19:17:24 +0200 )edit

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

pierre-yves samyn gravatar imagepierre-yves samyn ( 2018-06-17 06:51:01 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-15 12:10:19 +0200

Seen: 79 times

Last updated: Jun 16 '18