Использование SQL window functions для построения журнала-ордера

Журнал-ордер строится в два этапа. Сначала с помощью EXECUTE BLOCK подготавливаются исходные данные, затем в набор данных вставляются строки с итоговыми значениям по группам, чтобы на экране мы увидели такую картину:

Запрос для вышеприведенного журнала с группировкой по клиентам, затем по месяцам, выглядит так:

EXECUTE BLOCK(
  datebegin DATE=:BEGINDATE,  
  dateend DATE=:ENDDATE
)
RETURNS(
  c0 VARCHAR(180),
  NAME0 VARCHAR(180),
  s0 VARCHAR(180),
  c1 VARCHAR(180),
  NAME1 VARCHAR(180),
  s1 VARCHAR(180),
  sortfield INTEGE,
  ncu_begin_debit NUMERIC(15,2),
  ncu_begin_credit NUMERIC(15,2),
  ncu_debit NUMERIC(15,2),
  ncu_credit NUMERIC(15,2),
  ncu_end_debit NUMERIC(15,2),
  ncu_end_credit NUMERIC(15,2),
  curr_begin_debit NUMERIC(15,2),
  curr_begin_credit NUMERIC(15,2),
  curr_debit NUMERIC(15,2),
  curr_credit NUMERIC(15,2),
  curr_end_debit NUMERIC(15,2),
  curr_end_credit NUMERIC(15,2),
  eq_begin_debit NUMERIC(15,2),
  eq_begin_credit NUMERIC(15,2),
  eq_debit NUMERIC(15,2),
  eq_credit NUMERIC(15,2),
  eq_end_debit NUMERIC(15,2),
  eq_end_credit NUMERIC(15,2)
)
AS
  DECLARE VARIABLE varncubegin dcurrency;
  DECLARE VARIABLE varcurrbegin dcurrency;
  DECLARE VARIABLE vareqbegin dcurrency;
  DECLARE VARIABLE varncudebit dcurrency;
  DECLARE VARIABLE varncucredit dcurrency;
  DECLARE VARIABLE varcurrdebit dcurrency;
  DECLARE VARIABLE varcurrcredit dcurrency;
  DECLARE VARIABLE vareqdebit dcurrency;
  DECLARE VARIABLE vareqcredit dcurrency;
  DECLARE VARIABLE varncuend dcurrency;
  DECLARE VARIABLE varcurrend dcurrency;
  DECLARE VARIABLE vareqend dcurrency;
  DECLARE VARIABLE wasmovement INTEGER;
  DECLARE VARIABLE closedate DATE;
  DECLARE VARIABLE temp_c0 VARCHAR(180);
BEGIN
  closedate=CAST((CAST('17.11.1858'AS DATE)+GEN_ID(gd_g_entry_balance_date,0))AS DATE);
  temp_c0='';
 
  FOR
    SELECT
    m.ncu,
    m.curr,
    m.eq,
    c0.ID,
    SUBSTRING(c0.NAME FROM 1 FOR 180)AS c0,
    m.dateparam_c1,
    m.dateparam_c1,
    m.dateparam_c1,
    m.debitncu,
    m.creditncu,
    m.debitcurr,
    m.creditcurr,
    m.debiteq,
    m.crediteq
  FROM
  (
    SELECT
      COALESCE(SUM(en.debitncu-en.creditncu),0)AS ncu,
      COALESCE(SUM(en.debitcurr-en.creditcurr),0)AS curr,
      COALESCE(SUM(en.debiteq-en.crediteq),0)AS eq
      ,en.USR$GS_CUSTOMER,EXTRACT(MONTH FROM en.entrydate)AS dateparam_c1
      ,COALESCE(SUM(en.debitncu_m),0)AS debitncu,
      COALESCE(SUM(en.creditncu_m),0)AS creditncu,
      COALESCE(SUM(en.debitcurr_m),0)AS debitcurr,
      COALESCE(SUM(en.creditcurr_m),0)AS creditcurr,
      COALESCE(SUM(en.debiteq_m),0)AS debiteq,
      COALESCE(SUM(en.crediteq_m),0)AS crediteq
    FROM
      (
      SELECT
      bal.debitncu,
      bal.creditncu,
      bal.debitcurr,
      bal.creditcurr,
      bal.debiteq,
      bal.crediteq
      ,bal.USR$GS_CUSTOMER,CAST(NULL AS DATE)AS entrydate
      ,CAST(0 AS NUMERIC(15,2))AS debitncu_m,
      CAST(0 AS NUMERIC(15,2))AS creditncu_m,
      CAST(0 AS NUMERIC(15,2))AS debitcurr_m,
      CAST(0 AS NUMERIC(15,2))AS creditcurr_m,
      CAST(0 AS NUMERIC(15,2))AS debiteq_m,
      CAST(0 AS NUMERIC(15,2))AS crediteq_m
      FROM
      ac_entry_balance bal
      WHERE
      bal.accountkey IN(393008)AND
      bal.companykey+0 IN(153583337,146866007,153408611)
      UNION ALL
      SELECT
      e1.debitncu,
      e1.creditncu,
      e1.debitcurr,
      e1.creditcurr,
      e1.debiteq,
      e1.crediteq
      ,e.USR$GS_CUSTOMER,CAST(NULL AS DATE)AS entrydate
      ,CAST(0 AS NUMERIC(15,2))AS debitncu_m,
      CAST(0 AS NUMERIC(15,2))AS creditncu_m,
      CAST(0 AS NUMERIC(15,2))AS debitcurr_m,
      CAST(0 AS NUMERIC(15,2))AS creditcurr_m,
      CAST(0 AS NUMERIC(15,2))AS debiteq_m,
      CAST(0 AS NUMERIC(15,2))AS crediteq_m
      FROM
      ac_entry e
      LEFT JOIN ac_entry e1 ON e1.id=e.id AND e1.entrydate>=:closedate AND e1.entrydate<:datebegin
      WHERE
      e.accountkey IN(393008)AND
      e.entrydate>=:closedate AND
      e.entrydate<=:dateend AND
      e.companykey+0 IN(153583337,146866007,153408611)
      UNION ALL
      SELECT
      CAST(0 AS NUMERIC(15,4))AS debitncu,
      CAST(0 AS NUMERIC(15,4))AS creditncu,
      CAST(0 AS NUMERIC(15,4))AS debitcurr,
      CAST(0 AS NUMERIC(15,4))AS creditcurr,
      CAST(0 AS NUMERIC(15,4))AS debiteq,
      CAST(0 AS NUMERIC(15,4))AS crediteq,
      em.USR$GS_CUSTOMER,em.entrydate
      ,SUM(IIF(emc.issimple=0,emc.creditncu,em.debitncu))AS debitncu_m,
      SUM(IIF(emc.issimple=0,emc.debitncu,em.creditncu))AS creditncu_m,
      SUM(IIF(emc.issimple=0,emc.creditcurr,em.debitcurr))AS debitcurr_m,
      SUM(IIF(emc.issimple=0,emc.debitcurr,em.creditcurr))AS creditcurr_m,
      SUM(IIF(emc.issimple=0,emc.crediteq,em.debiteq))AS debiteq_m,
      SUM(IIF(emc.issimple=0,emc.debiteq,em.crediteq))AS crediteq_m
      FROM
      ac_entry em
      LEFT JOIN ac_entry emc ON emc.recordkey=em.recordkey AND emc.accountpart<>em.accountpart
      WHERE
      em.accountkey IN(393008)AND
      em.companykey+0 IN(153583337,146866007,153408611)
      AND em.entrydate>=:datebegin AND em.entrydate<=:dateend
      AND NOT EXISTS(
      SELECT
      e_cm.id
      FROM
      ac_entry e_cm JOIN ac_transaction tr ON e_cm.transactionkey=tr.id AND tr.isinternal=1
      WHERE
      e_cm.recordkey=em.recordkey AND
      e_cm.accountpart<>em.accountpart AND
      e_cm.accountkey+0=em.accountkey AND
      (em.debitncu=e_cm.creditncu AND
      em.creditncu=e_cm.debitncu AND
      em.debitcurr=e_cm.creditcurr AND
      em.creditcurr=e_cm.debitcurr)AND
      em.USR$GS_CUSTOMER=e_cm.USR$GS_CUSTOMER+0)
      GROUP BY
      em.USR$GS_CUSTOMER,em.entrydate
    )en
  GROUP BY
  en.USR$GS_CUSTOMER,EXTRACT(MONTH FROM en.entrydate)
  )m
  LEFT JOIN GD_CONTACT c0 ON c0.ID=m.USR$GS_CUSTOMER
  ORDER BY
    c0.NAME,m.dateparam_c1
  INTO
    :varncubegin,:varcurrbegin,:vareqbegin
    ,:c0,:NAME0,:s0
    ,:c1,:NAME1,:s1
    ,:varncudebit,:varncucredit,
    :varcurrdebit,:varcurrcredit,
    :vareqdebit,:vareqcredit
  DO BEGIN
    sortfield=1;
    wasmovement=0;
    IF(varncubegin IS NULL)THEN
    varncubegin=0;
    IF(varcurrbegin IS NULL)THEN
    varcurrbegin=0;
    IF(vareqbegin IS NULL)THEN
    vareqbegin=0;
    IF(varncudebit IS NULL)THEN
    varncudebit=0;
    IF(varncucredit IS NULL)THEN
    varncucredit=0;
    IF(varcurrdebit IS NULL)THEN
    varcurrdebit=0;
    IF(varcurrcredit IS NULL)THEN
    varcurrcredit=0;
    IF(vareqdebit IS NULL)THEN
    vareqdebit=0;
    IF(vareqcredit IS NULL)THEN
    vareqcredit=0;
    IF((temp_c0=c0))THEN
    BEGIN
    varncubegin=varncuend;
    varcurrbegin=varcurrend;
    vareqbegin=vareqend;
    END ELSE BEGIN
    temp_c0=c0;
    END
    ncu_debit=CAST((varncudebit/1)AS NUMERIC(15,2));
    ncu_credit=CAST((varncucredit/1)AS NUMERIC(15,2));
    curr_debit=CAST((varcurrdebit/1)AS NUMERIC(15,2));
    curr_credit=CAST((varcurrcredit/1)AS NUMERIC(15,2));
    eq_debit=CAST((vareqdebit/1)AS NUMERIC(15,2));
    eq_credit=CAST((vareqcredit/1)AS NUMERIC(15,2));
    varncuend=varncubegin+(varncudebit-varncucredit);
    varcurrend=varcurrbegin+(varcurrdebit-varcurrcredit);
    vareqend=vareqbegin+(vareqdebit-vareqcredit);
    IF(varncubegin>0)THEN
    BEGIN
    ncu_begin_debit=CAST((varncubegin/1)AS NUMERIC(15,2));
    ncu_begin_credit=0;
    END
    ELSE
    BEGIN
    ncu_begin_debit=0;
    ncu_begin_credit=-CAST((varncubegin/1)AS NUMERIC(15,2));
    END
    IF(varncuend>0)THEN
    BEGIN
    ncu_end_debit=CAST((varncuend/1)AS NUMERIC(15,2));
    ncu_end_credit=0;
    END
    ELSE
    BEGIN
    ncu_end_debit=0;
    ncu_end_credit=-CAST((varncuend/1)AS NUMERIC(15,2));
    END
    IF(varcurrbegin>0)THEN
    BEGIN
    curr_begin_debit=CAST((varcurrbegin/1)AS NUMERIC(15,2));
    curr_begin_credit=0;
    END
    ELSE
    BEGIN
    curr_begin_debit=0;
    curr_begin_credit=-CAST((varcurrbegin/1)AS NUMERIC(15,2));
    END
    IF(varcurrend>0)THEN
    BEGIN
    curr_end_debit=CAST((varcurrend/1)AS NUMERIC(15,2));
    curr_end_credit=0;
    END
    ELSE
    BEGIN
    curr_end_debit=0;
    curr_end_credit=-CAST((varcurrend/1)AS NUMERIC(15,2));
    END
    IF(vareqbegin>0)THEN
    BEGIN
    eq_begin_debit=CAST((vareqbegin/1)AS NUMERIC(15,2));
    eq_begin_credit=0;
    END
    ELSE
    BEGIN
    eq_begin_debit=0;
    eq_begin_credit=-CAST((vareqbegin/1)AS NUMERIC(15,2));
    END
    IF(vareqend>0)THEN
    BEGIN
    eq_end_debit=CAST((vareqend/1)AS NUMERIC(15,2));
    eq_end_credit=0;
    END
    ELSE
    BEGIN
    eq_end_debit=0;
    eq_end_credit=-CAST((vareqend/1)AS NUMERIC(15,2));
    END
    IF((ncu_debit<>0)OR(ncu_credit<>0)OR(CAST((varncubegin/1)AS NUMERIC(15,2))<>0)
    )THEN
    BEGIN
    wasmovement=1;
    SUSPEND;
    END
  END
END