Count days since 3rd event from today

Question

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:

Date	EventCount
22-04-01	15
22-09-30	10
22-10-01	1
22-10-04	1

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?

It is unclear what you're trying to achieve or asking here. If you want to use a parameter, use a parameter (`?`). If you want some kind of logic to derive a different date, describe the logic that would be needed and what problems you have implementing said logic. — Mark Rotteveel, Oct 31 '22 at 12:15
@MarkRotteveel I've likely used incorrect terminology. I've edited the title and post. Does this make more sense? — hiigaran, Oct 31 '22 at 12:55
Please clarify, if you want to select the fourth to twelfth event, would that also be `22-09-30`, and the thirteenth would be `22-04-01`? — Mark Rotteveel, Oct 31 '22 at 13:11
@MarkRotteveel that would be correct. Since the third event falls within one of the 10 in `22-09-30`, that date would be selected. — hiigaran, Oct 31 '22 at 13:35
I have rolled back your last edit as it is moving goal posts of an already answered question. I have provided a solution in the comment on my answer, but otherwise you should ask a new question. — Mark Rotteveel, Oct 31 '22 at 15:47
Fair enough, I apologise for that. I've gone ahead and accepted your solution, as it worked in the context of my original question. — hiigaran, Nov 01 '22 at 02:51

Mark Rotteveel · Accepted Answer · 2022-10-31T14:38:25.290

1

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

edited Oct 31 '22 at 14:38

answered Oct 31 '22 at 14:30

Mark Rotteveel

100,966
191
140
197

Apolgies for not mentioning some crucial info. There's an additional column `EventType`. I've used your first set of code which works with only one event, so I attempted to modify it by adding "EventType" under each `SELECT` clause. When I do that, `event_count_running_total` still totals up everything regardless of the `EventType` value. I also attempted to add a `GROUP BY "EventType"` line in place of the `FETCH` line so it would show the days since 3rd for each EventType, resulting in a `Token unknown, GROUP` error. Question edited. Or should I just submit a new question at this point? – hiigaran Oct 31 '22 at 15:37
1

@hiigaran If I understand you correctly, you need to add `partition by "EventType"` to the `over(..)` claus. So: `sum(event_count) over (partition by "EventType" order by event_date desc) event_count_running_total`, however this will then add another complication in selecting each of the first rows. You can solve this by using the `row_number()` window function and another derived table. For example, something like https://dbfiddle.uk/VxEyKhXs – Mark Rotteveel Oct 31 '22 at 15:45
That should be plenty for me to continue on my own and figure out the rest. Looks like the basics are already working. I'll have to do some research on how to use and understand `PARTITION` and `OVER`, but that's for me to worry about. I appreciate your patience. – hiigaran Nov 01 '22 at 02:50

score 0 · Answer 2 · answered Oct 31 '22 at 12:43

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

Count days since 3rd event from today

2 Answers2