firebird 2.5 selecting records with date closest to today

Question

I have query result/table with columns: id, date. Like:

id , date
0001 , 2012.01.20
0001 , 2014.10.12
0001 , null
0001 , 2017.05.21
0001 , 2017.08.15
0002 , null
0002 , 2013.06.05
0002 , 2017.08.11
0003 , null
0004 , 2011.12.25
0005 , 2017.12.10
0006 , null
0006 , 2013.04.23
.
.
.
etc...
It's an example - in real world there are couple thousand of id's and over one and half million records. How to find records with closest to today future date (id's 0001, 0002, 0005) and these only with null (id 0003), past dates (id 0004) or null and past dates (id 0006) replace with some text. The result should look like:

id , date
0001 , 2017.05.21
0002 , 2017.08.11
0003 , 'replaced text'
0004 , 'replaced text'
0005 , 2017.12.10
0006 , 'replaced text'
.
.
etc...
I hope this example shows exactly what I need.
Thank you for any clues.

SO is not a 'code for me'-service. What have you tried and where are you stuck? — Mark Rotteveel

Val Marinov Val Marinov · Accepted Answer · 2017-03-18T19:10:43

It seem the table has no primary key. Do not use field names as type name, like "DATE".

Anyway, here is an example how to do that you want, no claims that it is best.

Note that if there is many records,this procedure will be slow, so make sure the correct indexes are placed.

SET TERM ^ ;

create or alter procedure TEMP_TEST_PROC (
    IDATE date)
returns (
    OID varchar(4),
    DATE_BEFORE date,
    DATE_AFTER date,
    CLOSEST_DATE date,
    OTEXT varchar(32))
as
begin
  for select distinct id from test_table_wo_pk
  into :oid
  do
  begin
    date_before = null;
    date_after = null;
    /* get closest past date*/
    select first 1 t."DATE" from test_table_wo_pk t
    where ((t."DATE" <= :idate) and (t.id = :oid))
    order by t."DATE" desc
    into
      :date_before;

    /* get closest future date*/
    select first 1 t."DATE" from test_table_wo_pk t
    where (t."DATE" >= :idate) and (t.id = :oid)
    order by t."DATE"
    into
      :date_after;

    /* bonus - get closest future or past date */

    ... You may check date_before, date_after for NULL here, and set closest_date value here....

    if ((datediff(day,:date_before,:idate)) < (datediff(day,:idate,date_after))) then
      closest_date = :date_before;
    else
      closest_date = :date_after;

    /* set text column */
    if (:date_after is not null) then
      otext = :date_after;
    else
      otext = 'replased text';
    suspend;
  end
end^

SET TERM ; ^

The result:

firebird 2.5 selecting records with date closest to today

1 Answers