VPF::Запрос "Приход-Расход" - Форум программистов

Akella

Дата 11.5.2006, 08:04 (ссылка)

(нет голосов)

Загрузка ...

Творец

Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329

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

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

Цитата

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

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

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

Это сообщение отредактировал(а) Akella - 11.5.2006, 09:29

Balu

Дата 11.5.2006, 10:16 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 18
Регистрация: 28.8.2002

Репутация: 1
Всего: 1

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 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 372
Регистрация: 5.7.2004
Где: Салехард

Репутация: 1
Всего: 15

Цитата(Akella @ 11.5.2006, 08:04

)

в базе есть такие таблицы: приход(№, дата), товары(код ID, наименование), расход(№, дата).

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

--------------------

Было бы о чем молчать, а уж что сказать – всегда найдется...

Balu

Дата 11.5.2006, 14:19 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 18
Регистрация: 28.8.2002

Репутация: 1
Всего: 1

Цитата(Dynamic @ 11.5.2006, 07:34

)

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

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

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

Это сообщение отредактировал(а) Balu - 11.5.2006, 14:19

Akella

Дата 11.5.2006, 14:43 (ссылка)

(нет голосов)

Загрузка ...

Творец

Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329

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

Alex

Дата 11.5.2006, 19:08 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Экс. модератор
Сообщений: 4147
Регистрация: 25.3.2002
Где: Москва

Репутация: 7
Всего: 162

Считай в процедуре

--------------------

Написать можно все - главное четко представлять, что ты хочешь получить в конце.

Akella

Дата 12.5.2006, 10:20 (ссылка)

(нет голосов)

Загрузка ...

Творец

Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329

считать в процедуре каждое наименование из справочника товаров? или что считать в процедуре?

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

Akella

Дата 12.5.2006, 15:58 (ссылка)

(нет голосов)

Загрузка ...

Творец

Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329

В итоге получилась такая каша

Код


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

за помощь

Добавлено @ 16:00
вот ещё неплохая статейка по теме:
http://www.ibase.ru/devinfo/temptables.htm

Это сообщение отредактировал(а) Akella - 15.5.2006, 08:18

Akella

Дата 15.5.2006, 10:13 (ссылка)

(нет голосов)

Загрузка ...

Творец

Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329

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

Akella

Дата 15.5.2006, 12:42 (ссылка)

(нет голосов)

Загрузка ...

Творец

Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: 20
Всего: 329

вот переделал, тепь всё быстрее будет, т.к. процедура будет выбирать только данные за определённый период, а не весь приход и расход лопатить

Код


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;

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Firebird, Interbase \| Следующая тема »