Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > Firebird, Interbase > Запрос "Приход-Расход"


Автор: Akella 11.5.2006, 08:04
FB 2.0 BDS2006.
Есть база, в базе есть такие таблицы: приход(№, дата), товары(код ID, наименование), расход(№, дата).
Таблицы приход и расход имею детальные таблицы, в детальных таблицах есть поля: код товара (ID), наименование, пару текстовых полей, количество.

Так вот, уважаемые, помогите составить запрос, а то я сам что-то не могу допереть. Хотелось бы такое увидеть в результате:

Цитата

КодТовара:ОстатокНаНачало:СколькоПришло:СколькоУшло:ОстатокНаКонец

Что-то вроде обооротки....

На сколько это будет тяжело?
Может ещё какой-то таблицы-двух(вспомогательных не хватает) или поля не хватает, а?  

Автор: Balu 11.5.2006, 10:16
1. Создать вьюху:

Код

SELECT ID_Детали, Дата_Документа, Quantity
FROM Приход_Детали
LEFT JOIN Приход ON (Приход_Детали.ID_Документа = Приход.ID_Документа)

UNION ALL

SELECT ID_Детали, Дата_Документа, -Quantity
FROM Расход_Детали
LEFT JOIN Расход ON (Расход_Детали.ID_Документа = Расход.ID_Документа)


2. По этой вьюхе два запроса:

Сальдо на начало:
Код

SELECT ID_Детали, SUM(Quantity)
FROM Вьюха
WHERE Дата_Документа < Начало периода
GROUP BY ID_Детали


Сальдо по рабочему периоду
Код

SELECT ID_Детали, SUM(Quantity)
FROM Вьюха
WHERE Дата_Документа BETWEEN Начало_Периода AND Конец_Периода
GROUP BY ID_Детали
 

Автор: Dynamic 11.5.2006, 11:34
Цитата(Akella @  11.5.2006,  08:04 Найти цитируемый пост)
в базе есть такие таблицы: приход(№, дата), товары(код ID, наименование), расход(№, дата).


мне казалось всегда, что приход и расход выгоднее держать в одной таблычке, но пока сам не проверишь....... 

Автор: Balu 11.5.2006, 14:19
Цитата(Dynamic @  11.5.2006,  07:34 Найти цитируемый пост)
мне казалось всегда, что приход и расход выгоднее держать в одной таблычке, но пока сам не проверишь.......  

Ой не всегда это возможно. У меня есть система где приход и расход одинаковы по структуре только в ID товара, и кол-ве и все. Остальное все разное. Так смысл мне извращаться и пихать все в одно место?

Хотя согласен, для большинства "настольных" систем проще хранить это в одной таблице  

Автор: Akella 11.5.2006, 14:43
не знаю пока, может буду переделывать, посмотрю, как и что получается с разными таблицами 

Автор: Alex 11.5.2006, 19:08
Считай в процедуре 

Автор: Akella 12.5.2006, 10:20
считать в процедуре каждое наименование из справочника товаров? или что считать в процедуре?

мне нужна таблица в виде оборотки 

Автор: Akella 12.5.2006, 15:58
В итоге получилась такая каша
Код

CREATE VIEW VW_OBOROTKA(
    PREPARAT_ID,
    A_DATE,
    AD_AMOUNT,
    PRICE_RETAIL)
AS
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)

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)

это вроде временной таблицы

Цитата

PREPARAT_ID  A_DATE  AD_AMOUNT PRICE_RETAIL  
-------------------------------------------------------------------
          5  06.05.2006     100,000         1,850  
          2  06.05.2006      20,000         1,863  
          1  05.05.2006     244,000        16,315  
          1  10.05.2006      16,000         1,890  
          7  06.05.2006     500,000         0,490  
          8  12.05.2006     100,000         1,663  
          7  06.05.2006   1 200,000         1,768  
          1  06.05.2006   1 800,000       245,072  
          8  14.05.2006      10,000        23,925  
          7  14.05.2006   1 000,000        26,985  
          2  06.05.2006      50,000        25,337  
          7  05.05.2006      11,000        13,675  
          7  10.05.2006     -60,000        -0,490  
          7  09.05.2006    -100,000        -0,490  
          7  10.05.2006     -56,000        -3,600  
          7  10.05.2006    -335,000        -0,490  
          7  09.05.2006     -10,000        -0,490  
          7  14.05.2006  -2 000,000       -16,000  
          5  09.05.2006    -144,000        -1,850  
          1  06.05.2006  -1 960,000        -2,000  



а теперь из этой таблицы выгребаем данные

Код

SELECT 
     P.ID,
     P.Name,
--Количество и сумма (остаток на начало)
     (SELECT SUM(O.AD_AMOUNT) FROM VW_OBOROTKA O
      where (O.PREPARAT_ID = P.id) and (O.A_DATE < :DateBegin)
      GROUP BY O.PREPARAT_ID) AS AmountOnBegin,

     (SELECT SUM(O.PRICE_RETAIL) FROM VW_OBOROTKA O
      where (O.PREPARAT_ID = P.id) and (O.A_DATE < :DateBegin)
      GROUP BY O.PREPARAT_ID) AS PriceOnBegin,

--Количество и сумма (обороты по приходу)
     (SELECT SUM(O.AD_AMOUNT) FROM VW_OBOROTKA O
      where (O.PREPARAT_ID = P.id) and (O.AD_AMOUNT > 0)and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd)
      GROUP BY O.PREPARAT_ID) AS ArrivalAmount,

     (SELECT SUM(O.PRICE_RETAIL) FROM VW_OBOROTKA O
      where (O.PREPARAT_ID = P.id) and (O.PRICE_RETAIL > 0)and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd)
      GROUP BY O.PREPARAT_ID) AS ArrivalPrice,

--Количество и сумма (обороты по расходу)
     (SELECT -SUM(O.AD_AMOUNT) FROM VW_OBOROTKA O
      where (O.PREPARAT_ID = P.id) and (O.AD_AMOUNT < 0) and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd)
      GROUP BY O.PREPARAT_ID) AS ExpenseAmount,

     (SELECT -SUM(O.PRICE_RETAIL) FROM VW_OBOROTKA O
      where (O.PREPARAT_ID = P.id) and (O.PRICE_RETAIL < 0) and (O.A_DATE >= :DateBegin) and (O.A_DATE <= :DateEnd)
      GROUP BY O.PREPARAT_ID) AS ExpensePrice,

--Количество и сумма (остаток на конец)
     (SELECT SUM(O.AD_AMOUNT) FROM VW_OBOROTKA O
      where (O.PREPARAT_ID = P.id) and (O.A_DATE <= :DateEnd)
      GROUP BY O.PREPARAT_ID) AS AmountOnEnd,

     (SELECT SUM(O.PRICE_RETAIL) FROM VW_OBOROTKA O
      where (O.PREPARAT_ID = P.id) and (O.A_DATE <= :DateEnd)
      GROUP BY O.PREPARAT_ID) AS PriceOnEnd

FROM PREPARATS P


всё вроде правильно, спасибо Балу (+)  smile  за помощь  smile 
Добавлено @ 16:00 
вот ещё неплохая статейка по теме:
http://www.ibase.ru/devinfo/temptables.htm  

Автор: Akella 15.5.2006, 10:13
продолжаю тему:
как из этой вьюхи сделать процедуру? хочу передавать туда, как параметры дату начальную и конечную, а процедура должна вернуть  соответственно набор записей из четырёх полей, а то получается что вьюха выгребает данные за весь период - скорость падает... smile  

Автор: Akella 15.5.2006, 12:42
вот переделал, тепь всё быстрее будет, т.к. процедура будет выбирать только данные за определённый период, а не весь приход и расход лопатить
Код

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;
 

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)