I have a table Events in LibreOffice Base with a Firebird database (version 3.0.8) that records how many times an event occurs. Example below:

I would like to create a query to output the number of days from today since the 3rd event occurred. In the example above, the third event to date would be 22-09-30.

I assume the code would look something like:

SELECT "Date"
WHERE DATEDIFF(DAY, CURRENT_DATE, DATE '30-09-2022') AS "Third Last Event"
FROM "Events"

However, DATE '30-09-2022' is not a fixed value. I am just using it as an example of what the third event would be in the above example's case. Given that new rows would be added to this table and more values would be added to EventCount, it would change on a regular basis.

What would I have to replace DATE '30-09-2022' with, so that I could run the query and have it return the value in the Date column that corresponds with the third EventCount from CURRENT_DATE?

CodePudding user response：

It is not clear if Nth event is supposed to be from now or from beginning but assuming the former you can use a simple procedure:

create procedure DaysSinceNthEvent(n integer) returns (days integer) as
  declare c integer;
  declare dd date;
  declare total integer;
begin
  total = 0;
  for select d, EventsCount from Events order by d desc into dd, c do
    begin
      total = total   c;
      if (total >= n) then
        begin
          days = current_date - dd;
          suspend;
          exit;
        end
    end
end

fiddle

CodePudding user response：

You can use the SUM window function to calculate a running total, and then find which row has the first equal or higher running total. Then you can use datediff (in my example I switched the position of current_date because I liked it better for the name I gave my column - days_ago):

select 
  event_date,
  event_count,
  event_count_running_total,
  datediff(day, event_date, current_date) days_ago
from (
  select 
    event_date, 
    event_count, 
    sum(event_count) over (order by event_date desc) event_count_running_total
  from events
)
where event_count_running_total >= 3
order by event_date desc
fetch first row only

https://dbfiddle.uk/bGwQtI2v

With Firebird 4.0, using window frames would allow for a (slightly) different solution:

select 
  event_date,
  event_count,
  event_count_running_total,
  event_count_running_prev,
  datediff(day, event_date, current_date) days_ago
from (
  select 
    event_date, 
    event_count, 
    sum(event_count) over (order by event_date desc) event_count_running_total, 
    sum(event_count) over (order by event_date desc rows between unbounded preceding and 1 preceding) event_count_running_prev
  from events
)
where 3 between event_count_running_prev and event_count_running_total

https://dbfiddle.uk/r9q0nmHj