iBase.ru Forum

Код: Выделить всё

CREATE VIEW NEW_VIEW(
    PREPARAT_ID,
    A_DATE,
    AD_AMOUNT)
AS
SELECT
AD.PREPARAT_ID,
A.DATE_INPUT,
AD.AMOUNT
FROM ARRIVAL_DET AD
LEFT JOIN ARRIVAL A ON (AD.arrival_ID = A.ID)

UNION ALL

SELECT ED.PREPARAT_ID, E.DATE_OUT, -ED.AMOUNT
FROM EXPENSE_DET ED
LEFT JOIN EXPENSE E ON (ED.EXPENSE_ID = E.ID)

Код: Выделить всё

SELECT 
     P.ID as "Код",
     P.Name as "Препарат",

     (SELECT SUM(NW.AD_AMOUNT) FROM NEW_VIEW NW
      where (NW.PREPARAT_ID = P.id) and (NW.A_DATE <= '05.05.2006')
      GROUP BY NW.PREPARAT_ID) AS "Сальдо на начало",

     (SELECT SUM(NW.AD_AMOUNT) FROM NEW_VIEW NW
      where (NW.PREPARAT_ID = P.id) and (NW.AD_AMOUNT > 0)
      GROUP BY NW.PREPARAT_ID) AS "Приход",

     (SELECT (SUM(NW.AD_AMOUNT) * -1) FROM NEW_VIEW NW
      where (NW.PREPARAT_ID = P.id) and (NW.AD_AMOUNT < 0)
      GROUP BY NW.PREPARAT_ID) AS "Расход",

     (SELECT SUM(NW.AD_AMOUNT) FROM NEW_VIEW NW
      where (NW.PREPARAT_ID = P.id) and (NW.A_DATE < '01.06.2006')
      GROUP BY NW.PREPARAT_ID) AS "Сальдо на конец"

FROM PREPARATS P

Код: Выделить всё

SET TERM ^ ;

CREATE PROCEDURE SP_OBOROTKA (
    BEGINDATE DATE,
    ENDDATE DATE)
RETURNS (
    PREPARAT_ID INTEGER,
    A_DATE DATE,
    AD_AMOUNT DOUBLE PRECISION,
    PRICE_RETAIL DOUBLE PRECISION)
AS
begin
  FOR SELECT
    AD.PREPARAT_ID,
    A.DATE_INPUT,
    AD.AMOUNT,
    AD.PRICE_RETAIL
  FROM ARRIVAL_DET AD
  LEFT JOIN ARRIVAL A ON (AD.arrival_ID = A.ID)
  where (a.DATE_INPUT >= :BEGINDATE) AND (A.DATE_INPUT < :ENDDATE)

  UNION ALL

  SELECT
    ED.PREPARAT_ID,
    E.DATE_OUT,
    -ED.AMOUNT,
    -ED.PRICE_RETAIL
  FROM EXPENSE_DET ED
  LEFT JOIN EXPENSE E ON (ED.EXPENSE_ID = E.ID)
  where (e.DATE_OUT >= :BEGINDATE) AND (e.DATE_OUT < :ENDDATE)

  into :PREPARAT_ID, :A_DATE, :AD_AMOUNT, :PRICE_RETAIL
  DO suspend;
end^

SET TERM ; ^

GRANT SELECT ON ARRIVAL_DET TO PROCEDURE SP_OBOROTKA;

GRANT SELECT ON ARRIVAL TO PROCEDURE SP_OBOROTKA;

GRANT SELECT ON EXPENSE_DET TO PROCEDURE SP_OBOROTKA;

GRANT SELECT ON EXPENSE TO PROCEDURE SP_OBOROTKA;

GRANT EXECUTE ON PROCEDURE SP_OBOROTKA TO SYSDBA;