Запрос "Приход-Расход"

Akella · Сообщение **Akella** » 11 май 2006, 10:27

Уважаемые Гуру и не только, обращаюсь ко всем, кто в силах помочь. Не поможете ли мне с запросом, сам не могу сообразить:
может ещё какого поля не хвататет, может ещё 1-2 вспомогательные таблицы нужно создать, а? Не ругайтось только, а?

FB 2.0
Есть база, в базе есть такие таблицы: приход(№, дата), товары(код ID, наименование), расход(№, дата).
Таблицы приход и расход имею детальные таблицы, в детальных таблицах есть поля: код товара (ID), наименование, количество, цена и ещё пару текстовых полей.
Так вот, помогите составить запрос, а то я сам что-то не могу допереть. Хотелось бы такое увидеть в результате:

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

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

Заранее спасибо, буду признателен за любую помощь

Dimitry Sibiryakov

Если бы ты держал приход и расход в одной таблице, было бы проще. А так отдельно сичтается приход на начало, расход на начало и их разность дает остатки. Потом приход и расход за период. Складываем, вычитаем - получаем остатки на конец периода.

Dimitry Sibiryakov

Кстати держать наименование в таблице детализации хоть и удобно иногда, но чаще вызывает жуткие проблемы с расхождением остатков (если они группируются по названию).

Ivan_Pisarevsky

Dimitry Sibiryakov писал(а):Если бы ты держал приход и расход в одной таблице, было бы проще.

C точки зрения мировой революции незаметно...

пишешь ХП примерно следующего вида:

В цикле отбираешь приход и расход (кол-во в расходе множишь на (-1), либо доп. поле +1 и -1, соответственно) обычным юнионом оные объединяешь, ордер бай по дате, и в цикле подсчитываешь остаток
(собстно ради этого ХП и нужна, если остаток будешь считать на клиенте в том же квик(фаст)репорте то и ХП не обязательна) и выплевываешь суспендом на клиента.

Только это обычно именуется не оборотка, а карточка учета ТМЦ.

Ivan_Pisarevsky

Тфу ты... чтот совсем плохой стал, тебе действительно оборотка нужна...

Ivan_Pisarevsky

Тогда наводящий вопрос: Остаки на начало месяца/года/недели/дня где хранишь, от чего отталкиаться?

У меня просто слехка по другому приход расход устроен, поэтому навскидку не подскажу.

Akella · Сообщение **Akella** » 11 май 2006, 16:56

Нигде не храню, есть только такая структура, пока только проектирую базу. База простая вприниципе, подразумевает собой аптечный склад.
Есть приход от сторонних оргинизаций, есть расход на свои 5-7 филиалов аптечных киосков. Вот я и подумал, что расход наверное лучше хранить в отдельной таблице, т.к. расхода на стороние организации нет как такового.

Akella · Сообщение **Akella** » 11 май 2006, 17:00

остатки разве нельзя расчитывать динамически?
У нас в Инфо-Предприятии (бух. программа) есть таблица бухопераций и привязанная к ней детальная таблица проводок. По этим таблица истроится оборотка, мож как-то так мне и сделать, а?

Получается, что нужно ещё и таблицу исходных остатков содавать? И как тогда с ней, а?

Ivan_Pisarevsky

Akella писал(а):остатки разве нельзя расчитывать динамически? ... Получается, что нужно ещё и таблицу исходных остатков содавать? И как тогда с ней, а?

Можно, но расчет будет пропорционален размеру таблицы и со временем производительнойть будет хромать на обе ноги. Есть такое понятие хранимый агрегат, точка актуализации. Ее можно двигать ночью, а днем получать основываясь на ней оборотки.

У меня сделано вообще по другому. Есть 3 основных типа первичных документов: приходка, расходка, счет на предоплату. Счета на предоплату лежат в отдельной паре таблиц и могут выписываться не глядя на остатки, потому что бывает так что мы выставляем счет на оплату клиенту, а продукцию шить еще не начинали и естественно на склае еще нет нифига. Дальше каждая группа изделий от 1 до N имеет ссылку на документ по которому она пришла и по которому ушла, все то что на остатке приписано специально обученной расходке, которая будет израсходована в махнадцать каком-то году. Сборка обороки сводится к рассовыванию групп товаров по 4 колонкам исходя из дат когда_пришло и когда_ушло, потом группирую все это дело на нужном уровне. В итоге на примерно 10000 документов (со склада идут только оптовые партиии, поэтому накладных не много) и примерно 100000 строк спецификаций оборотка собирается за пару секунд без использования хранимых агрегатов, железка на сервере более чем скромная. Таблицы разумеется имеют индексы по дате и выборка всегда едет по индексу.

Это просто еще один из возможных вариантов, лучше ты почитай какую-нидь теорию про складской учет.

Akella · Сообщение **Akella** » 12 май 2006, 17:08

Решил так:
1. делаем вьюху, как временную таблицу:

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

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

всем спасибо, всё вроде нормально работает

хорошая статейка
http://www.ibase.ru/devinfo/temptables.htm

Ivan_Pisarevsky

>всем спасибо, всё вроде нормально работает
К тому времени как наберется прилично кол-во записей в таблицах, глядишь научишься программировать без вьюх, ибо тормозов при твоем последнем раскладе не избежать.

Суть в том что во вьюху ты набираешь данные без ограничения по периоду, а потом начинаешь фильтровать. Лучше отбери при помощи ХП, отсеяв СРАЗУ ненужные данные, причем отсеивать надо обязательно пользуясь индексами.

Akella · Сообщение **Akella** » 15 май 2006, 11:02

тогда встречный вопрос?
как создать процедуру, которая вернула бы набор записей, как вьюха?

Сообщение **kdv** » 15 май 2006, 11:15

посмотри в employee.fdb/gdb, и в datadef.pdf. примеров полно. да и тут на форуме.

Akella · Сообщение **Akella** » 15 май 2006, 11:23

хотя можно сделать две глабальные (контекстные) перменные, передавать в эти переменные начальную и конечную даты и тогда можно этими глобальными переменными пользоваться во вьюхе для ограничения периода выборки

как думаете, а? так нормально? просто я не представляю как сделать процедуру из вьюхи, пытаюсь вот, но эксперт ругается на UNION, наверное UNION нельзя использовать в процедурах...

Сообщение **kdv** » 15 май 2006, 12:27

наверное UNION нельзя использовать в процедурах...

да ну?

но эксперт ругается на UNION

меня всегда радовали подобные сообщения об ошибках.

Akella · Сообщение **Akella** » 15 май 2006, 12:50

а мне не смешно, так как же быть

Сообщение **kdv** » 15 май 2006, 13:28

как быть? ДОЛБИТЬ!

если по нормальному, то сначала надо ПРОЧИТАТЬ сообщение об ошибке в IBE. потом подумать. Если не помогло - привести сообщение об ошибке здесь. А не писать "ругается" и прочие междометия.

Akella · Сообщение **Akella** » 15 май 2006, 13:39

вот, получилось, не всё сам, конечно, хвастаться не буду, но работает

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

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;

Dimitry Sibiryakov

Поскольку результат UNION ALL никак не обрабатывается (не группируется, не сортируется), то я бы развел эти запросы по разным FOR SELECT. Это может дать выйгрыш в скрости. А может и не дать...

Ivan_Pisarevsky

Еще в качестве напутствия, бывает такое понятие как "провести по красноте" то бишь сначала "ушел" а только потом "пришел", так что такую ситуацию надо обработать обязательно, имей в виду.