Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Расчёт остатков на складе / 16 сообщений из 16, страница 1 из 1
20.06.2017, 14:52
    #39474791
Ilya Stepanov
Ilya Stepanov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Добрый день/вечер.

Имеется БД на Firebird 2.5, IBExpert для работы с ней.
База данных по мебельным механизмам, подразумевается, что пользоваться ей будет посредник, который закупает и перепродает.
Имеется таблица
Jornal_Operations
структура:
 

ID_Producer - Поставщик.
ID_Customer - Покупатель.
ID_Operation - Приход/Расход (1/2)
Данные в таблице:
 

Запрос на подсчет стоимости покупки/продажи той же таблицы:
 


Проблема с подсчетом остатков на складе.
1 Вариант моих действий:
Создал представления на приход(Arrive) и расход(Costs)
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE OR ALTER VIEW ARRIVE(
    ID_FURNITURE_DETAIL,
    NAME_OPERATION,
    COUNT_FURNITURE_DETAIL)
AS
SELECT FURNITURE_DETAILS.ID_FURNITURE_DETAIL, OPERATIONS.NAME_OPERATION, JOURNAL_OPERATIONS.COUNT_FURNITURE_DETAIL
FROM FURNITURE_DETAILS INNER JOIN (OPERATIONS INNER JOIN JOURNAL_OPERATIONS ON OPERATIONS.ID_OPERATION=JOURNAL_OPERATIONS.ID_OPERATION) ON FURNITURE_DETAILS.ID_FURNITURE_DETAIL=JOURNAL_OPERATIONS.ID_FURNITURE_DETAIL
WHERE ((OPERATIONS.id_operation=1))
;

CREATE OR ALTER VIEW COSTS(
    ID_FURNITURE_DETAIL,
    NAME_OPERATION,
    COUNT_FURNITURE_DETAIL)
AS
SELECT FURNITURE_DETAILS.ID_FURNITURE_DETAIL, OPERATIONS.NAME_OPERATION, JOURNAL_OPERATIONS.COUNT_FURNITURE_DETAIL
FROM FURNITURE_DETAILS INNER JOIN (OPERATIONS INNER JOIN JOURNAL_OPERATIONS ON OPERATIONS.ID_OPERATION=JOURNAL_OPERATIONS.ID_OPERATION) ON FURNITURE_DETAILS.ID_FURNITURE_DETAIL=JOURNAL_OPERATIONS.ID_FURNITURE_DETAIL
WHERE ((OPERATIONS.id_operation=2))
;




Далее написал запрос на поиск остатков, с использованием этих представлений:
Код: sql
1.
2.
3.
select furniture_details.id_furniture_detail, FURNITURE_DETAILS.name_furniture_detail, arrive.count_furniture_detail - costs.count_furniture_detail
from (furniture_details  left join arrive ON furniture_details.id_furniture_detail = arrive.id_furniture_detail)
                         left join costs ON furniture_details.id_furniture_detail = costs.id_furniture_detail


И тут всплывает проблема:
Запрос выше, подсчитывает остаток только тех позиций, на которые был и приход и расход, остальные null.
 
Журнал операций:



Запрос остатка:




2 Вариант действий после этого, считать по другому, избавиться от null и без представлений:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select
furniture_details.id_furniture_detail,  furniture_details.name_furniture_detail,
(
    select coalesce(sum(journal_operations.count_furniture_detail * journal_operations.price_furniture_detail), 0)
    from journal_operations
    journal_operations where journal_operations.id_furniture_detail = furniture_details.id_furniture_detail
)
from furniture_details



То есть я получаю общую стоимость(приход+расход) и вычитаю расход и на этом моменте я сел в лужу, потому что не знаю как разобраться с первым вариантом и со вторым.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 15:06
    #39474804
Ivan_Pisarevsky
Ivan_Pisarevsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
без опорных остатков(например на первое число каждого месяца) вся схема "садится в лужу".

избавления от нуллов это мелкотехнический вопрос, здесь есть концептуальные промахи, их потом не исправить, их надо продумывать сразу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 15:14
    #39474814
rdb_dev
rdb_dev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ilya Stepanov, в таблицу операций добавьте поле DIRECTION SMALLINT CHECK(VALUE = 1 OR VALUE = -1) и тогда, к примеру, "остаток на конец периода" будет выглядеть примерно так:
Код: sql
1.
2.
3.
4.
5.
SELECT FDTL.ID_FURNITURE_DETAIL, Sum(Coalesce(JOP.COUNT_FURNITURE_DETAIL * OPS.DIRECTION, 0)) AS BALANCE
  FROM FURNITURE_DETAILS FDTL
    LEFT JOIN JOURNAL_OPERATIONS JOP ON JOP.ID_FURNITURE_DETAIL = FDTL.ID_FURNITURE_DETAIL
    LEFT JOIN OPERATIONS OPS ON OPS.ID_OPERATION = JOP.ID_OPERATION
  WHERE JOP.TMSTMP BETWEEN ? AND ?

С остальным разберетесь по аналогии
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 15:16
    #39474817
rdb_dev
rdb_dev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ilya Stepanov, и да, Иван прав - регистры остатков must have.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 15:18
    #39474819
Ilya Stepanov
Ilya Stepanov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ivan_Pisarevsky,

Я понимаю, это.
Но я сейчас еще в колледже учусь и пробую в родительскую фирму сделать такой мелкософт, и самому поучиться хоть на пробах и ошибках. На данный момент, грубо говоря подразумевается, что сначала идет закупка, а потом реализация, поэтому и нужны остатки просто как они есть, без дат, хотя хотелось бы узнать, как это реализовать для следующих этапов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 15:23
    #39474824
Ilya Stepanov
Ilya Stepanov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
rdb_dev,

Прошу прощение, за свое недопонимание, но как и где используется поле Direction, о котором вы написали в коде, который вы проложили?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 16:05
    #39474887
Ivan_Pisarevsky
Ivan_Pisarevsky
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ilya Stepanovнужны остатки просто как они есть, без дат,остаток без даты, как чемодан без ручки. :)

Ilya Stepanovсейчас еще в колледже учусь и пробуюВ свое время писал дипломный проект, так им до сих пор пользуются, хотя почти 20 лет прошло. Грозились много раз от него избавиться, но до сих пор иногда приходят с челобитной типа поправить печатный шаблончик или микроскопической косячок выправить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 16:14
    #39474899
Ilya Stepanov
Ilya Stepanov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ivan_Pisarevsky,

Вы сможете пояснить мне по поводу сообщения ?
20577776

Что и как происходит в запросе. Я попробовал сейчас его выполнить, у меня в столбце Balance, 0 полностью.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 16:21
    #39474908
rdb_dev
rdb_dev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ilya Stepanovrdb_dev,
Прошу прощение, за свое недопонимание, но как и где используется поле Direction, о котором вы написали в коде, который вы проложили?
Код: sql
1.
Sum(Coalesce(JOP.COUNT_FURNITURE_DETAIL * OPS.DIRECTION, 0))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 16:25
    #39474911
rdb_dev
rdb_dev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ilya StepanovЧто и как происходит в запросе. Я попробовал сейчас его выполнить, у меня в столбце Balance, 0 полностью.Для операции "приход" в поле DIRECTION поставь 1, а для операции "расхода" поставь -1. Я полагал, что это очевидно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 16:27
    #39474917
Ilya Stepanov
Ilya Stepanov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
rdb_dev,
Это я уже понял, для чего нужно поле direction? И почему именно value = 1 or value = -1 ?
Код: sql
1.
2.
3.
4.
5.
6.
SELECT FDTL.ID_FURNITURE_DETAIL, Sum(Coalesce(JOP.COUNT_FURNITURE_DETAIL * OPS.DIRECTION, 0)) AS BALANCE
  FROM FURNITURE_DETAILS FDTL
    LEFT JOIN JOURNAL_OPERATIONS JOP ON JOP.ID_FURNITURE_DETAIL = FDTL.ID_FURNITURE_DETAIL
    LEFT JOIN OPERATIONS OPS ON OPS.ID_OPERATION = JOP.ID_OPERATION
   WHERE JOP.date_journal_operation BETWEEN '01.06.2017' AND '30.06.2017'
group by fdtl.id_furniture_detail



Вот в итоге запрос, но выдает он:

ID_FURNITURE_DETAIL | BALANCE
1 | 0
2 | 0
3 | 0
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 16:29
    #39474919
rdb_dev
rdb_dev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ilya Stepanov, 20578041
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 16:34
    #39474928
Ilya Stepanov
Ilya Stepanov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
rdb_dev,
То есть в таблице операций у меня по ключу 1 это приход, по ключу 2 это расход.
 

и все равно по нулям баланс
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 16:36
    #39474933
rdb_dev
rdb_dev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ilya Stepanov, попробуй отобрать все записи - без WHERE
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 16:52
    #39474945
Ilya Stepanov
Ilya Stepanov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
rdb_dev,

Я жуткий тормоз, но все же, должно быть так?
 

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.06.2017, 17:21
    #39474969
Ilya Stepanov
Ilya Stepanov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт остатков на складе
Ilya Stepanov,

Всё, доделал запрос с помощью case when, вместо direction.

Всем спасибо за советы и помощь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Расчёт остатков на складе / 16 сообщений из 16, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]