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

Материал из GedeminWiki
(Различия между версиями)
Перейти к: навигация, поиск
(Новая страница: «Журнал-ордер строится в два этапа. Сначала с помощью EXECUTE BLOCK подготавливаются исходные д…»)
 
Строка 1: Строка 1:
−
Журнал-ордер строится в два этапа. Сначала с помощью EXECUTE BLOCK подготавливаются исходные данные, затем в набор данных вставляются строки с итоговыми значениям по группам, чтобы на экране мы увидели такую картину:
+
[[Журнал-ордер]] строится в два этапа. Сначала с помощью EXECUTE BLOCK подготавливаются исходные данные, затем в набор данных вставляются строки с итоговыми значениям по группам, чтобы на экране мы увидели такую картину:
  
 
http://gsbelarus.com/gs/images/gs/2016/jo-01.png
 
http://gsbelarus.com/gs/images/gs/2016/jo-01.png
 +
 +
Запрос для вышеприведенного журнала с группировкой по клиентам, затем по месяцам, выглядит так:
 +
 +
<syntaxhighlight lang="sql">
 +
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
 +
</syntaxhighlight>

Версия 19:05, 2 декабря 2016

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

jo-01.png

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

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
Источник — «https://gsbelarus.com:80/gs/wiki/index.php?title=%D0%3F%D1%81%D0%BF%D0%BE%D0%BB%D1%8C%D0%B7%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5_SQL_window_functions_%D0%B4%D0%BB%D1%8F_%D0%BF%D0%BE%D1%81%D1%82%D1%80%D0%BE%D0%B5%D0%BD%D0%B8%D1%8F_%D0%B6%D1%83%D1%80%D0%BD%D0%B0%D0%BB%D0%B0-%D0%BE%D1%80%D0%B4%D0%B5%D1%80%D0%B0&oldid=7038»
Персональные инструменты
Пространства имён

Варианты
Действия
Навигация
Инструменты