Row_number over partition at Version < Firebird 3

firebirdwindow functions

I´m currently at the end.
Given facts:

ID      ID2      FROM                TO                 COSTS
4985  501671  2013-07-25 00:00:00  2013-12-30 00:00:00   17.8
4985  501671  2013-09-05 00:00:00  2013-10-24 00:00:00    1.1   
4985  501671  2013-10-25 00:00:00  2013-10-25 00:00:00    1.61  
4985  501671  2013-11-04 00:00:00  2013-12-23 00:00:00    1.9   
4985  501671  2013-12-30 00:00:00  2014-01-10 00:00:00    2.7   
4985  501671  2014-01-06 00:00:00  2014-01-10 00:00:00   18.8

Now, I want to sum fields, which are in the same time, eg.

Row 1 -> from 2013-07-25 till 2013-09-04 and Costs 17.8 because than
17.8 must be added with 1.1 Row 2 -> from 2013-09-05 till 2013-10-24 18.80 (17.8 + 1.1) because 1.1 and 17.8 are in the thame time.

Normally, I will row_number over partition by but these function is not implemented in Firebird 2.5 🙁

Does anybody know an alternative function to solve this problem?

Best Answer

Hard to tell what you want (a precise question gets a precise answer, a vague question at best gets a lucky guess). In Firebird 2.5 an answer may be something like:

EXECUTE BLOCK RETURNS (ID INTEGER, ID2 INTEGER, FROM DATE, TO DATE, 
                        COSTS DECIMAL(9,2), MySUM(DECIMAL(9,2))
AS
DECLARE VARIABLE FROMDATE DATE;
DECLARE VARIABLE TODATE DATE;
BEGIN
  FROMDATE = NULL;
  TODATE = NULL;
  MySUM = 0;
  FOR SELECT ID, ID2, FROM, TO, COSTS
      FROM MyTable
      INTO :ID, :ID2, :FROM, :TO, :COSTS DO
  BEGIN
    IF (FROMDATE <= FROM and TODATE >= TO) THEN
    BEGIN
      MySUM = MySUM + COSTS;
      SUSPEND;
    END
    ELSE
    BEGIN
      MySUM = COSTS;
      FROMDATE = FROM;
      TODATE = TO;
    END
    SUSPEND;
  END
END

By the way, I don't think the above would quite compile since 'FROM' and 'TO' are not names I would recommend as names of columns.

Still, HTH, Set

Related Question