Посчитать остатки на складе

@lcnet · Регистрация: 21.07.2015

Студворк — интернет-сервис помощи студентам

Приветствую!
Я начинающий аналитик и я уже всю голову сломал как мне написать правильный запрос.
Есть таблица содержащая поступления и реализации. Мне нужно посчитать остатки на складе.
Я не нашел ничего лучше, чем использовать временные таблицы и соединить их через JOIN.
У меня все корректно посчиталось кроме одной строки (в одной таблице эта строка присутствует, в другой нет.

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

Есть таблица с данными - operations_data (содержит поступления и реализации)

T-SQL
1
select top 10 * from operations_data

Code
1
2
3
4
5
6
7
8
9
10
11
dt                 tm       order_number    order_type_id   product_category_id  product_id manufacturer_id     cnt         price   selling_price
2000-07-30  09:32:00.0000000     1             1                       2          5                  2         5400          3.2          null
2000-07-30  09:55:00.0000000     1             2                       2          5                  2         2870          3.2          3.33
2000-08-04  10:10:00.0000000     2             2                       2          5                  2         2336          3.2          3.48
2000-08-04  11:04:00.0000000     3             2                       2          5                  2         26            3.2          3.58
2000-08-04  11:21:00.0000000     4             2                       2          5                  2         9             3.2          3.28
2000-08-04  12:01:00.0000000     5             2                       2          5                  2         36            3.2          3.41
2000-08-08  11:49:00.0000000     6             2                       2          5                  2         48            3.2          3.38
2000-08-08  12:29:00.0000000     7             2                       2          5                  2         7             3.2          3.58
2000-08-08  13:16:00.0000000     8             2                       2          5                  2         57            3.2          3.31
2000-08-08  13:19:00.0000000     2             1                       1          8                  1         400          16.49         null

Есть справочники: order_type (содержит типы операций и их идентификаторы) и product (содержит названия товаров и их идентификаторы)

T-SQL
1
2
select * from order_type;
select * from product;

Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
order_type_id   order_type
      1       purchase order
      2       sales invoice
 
product_id        product
      1       epoxy grouts
      2       everal aqua 10
      3       everal aqua 10 interior
      4       everal aqua 40
      5       fugen
      6       glue cm 11
      7       glue cm 17
      8       grout
      9       helmi 10
      10      helmi 30
      11      helmi primer
      12      kiva 10
      13      kiva 30
      14      kiva 70
      15      partial fill cavity slab 100
      16      partial fill cavity slab 50
      17      partial fill cavity slab 80
      18      polyurethane foam
      19      polyurethane foam premium of winter
      20      rockclose insulated dpc 20
      21      rotband
      22      uniflott

Я создал две таблицы: purch и sales, в которые поместил данные по суммарному количеству закупок и продаж

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
create table purch (
  product varchar(255),
  cnt int
)
 
create table sales (
  product varchar(255),
  cnt int
)
 
insert into purch select t2.product, sum(t1.cnt) purchase
from operations_data t1
  join product t2 on t1.product_id = t2.product_id
where t1.order_type_id = 1
  group by t2.product
 
insert into sales select t2.product, sum(t1.cnt) sales
from operations_data t1
  join product t2 on t1.product_id = t2.product_id
where t1.order_type_id = 2
  group by t2.product
 
select product 'Наименование товара', cnt 'Количество на складе' from purch 
  except
select product 'Наименование товара', cnt 'Количество на складе' from sales
  
  
select v1.product 'Наименование товара', (v1.cnt - v2.cnt) 'Количество на складе' 
  from purch v1
  left join sales v2 on v1.product = v2.product

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

Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
Наименование товара                 Количество на складе
uniflott                                          2200
 
Наименование товара                      Количество на складе
epoxy grouts                                        0
everal aqua 10                                      0
everal aqua 10 interior                             0
everal aqua 40                                      0
fugen                                               0
glue cm 11                                          0
glue cm 17                                          0
grout                                               0
helmi 10                                            0
helmi 30                                            0
helmi primer                                        0
kiva 10                                             0
kiva 30                                             0
kiva 70                                             0
partial fill cavity slab 100                        0
partial fill cavity slab 50                         0
partial fill cavity slab 80                         2
polyurethane foam                                   0
polyurethane foam premium of winter                 0
rockclose insulated dpc 20                          0
rotband                                             0
uniflott                                          null

Для товара uniflott должно быть количество 2200

Добавлено через 2 часа 54 минуты
Разобрался, использовал функцию ISNULL, в результате запрос получился такой:

T-SQL
1
2
3
4
5
select v1.product 'Наименование товара', 
  (v1.cnt - isnull(v2.cnt,0)) 'Количество на складе' 
  from purch v1
  left join sales v2 on v1.product = v2.product
where (v1.cnt - isnull(v2.cnt,0)) != 0

@iLinks · 26.06.2025, 18:53

lcnet, Для товара uniflott есть записи только в таблице purch и нет записей в таблице sales. При left join sales, v2.cnt равно null. Любая операция с null, дает null (арифметическая). Как вылечить, обернуть v2.cnt в обработку null с заменой на 0, например ISNULL(v2.cnt,0)

@116rus · 01.07.2025, 00:30

T-SQL
1
2
3
4
5
select product.*,isnull(total_balance.balance,0) from product
 cross apply (select sum(case when order_type_id = 1 then order_type_id end) - sum(case when order_type_id = 2 then order_type_id end) as balance 
              from operations_data
              group by product_id
              having product_id=product.product_id) as tbl_balance

примерно так, одним запросом

@Ludwig Valentin · 10.07.2025, 07:58

грубо
приход

T-SQL
1
select into #prihod_rashod product_id, cnt * price as prihod from operations_data where order_type_id = 1 group by product_id

расход

T-SQL
1
select into #prihod_rashod product_id,cnt * price as rashod from operations_data where order_type_id = 2 group by product_id

разницу в таблицу по

T-SQL
1
2
 truncate table ostatki
select into ostatki  product_id, (prihod  - rashod) as ostatki from #prihod_rashod group_by product_id

Добавлено через 2 часа 43 минуты
Я показал идею, дальше надо отлаживать.
Поместить SQL в хранимую процедуру.

@Аватар · 10.07.2025, 09:09

T-SQL
1
2
3
4
select product_id,sum(case when order_type_id = 1 then cnt else -cnt end) ostat
  from operations_data
  group by product_id
  where  dt<='20250704' -- остаток на 4 июля. убрать where если остаток по всему движению

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

@Andrey-MSK · 10.07.2025, 11:48

Сообщение от Ludwig Valentin

T-SQL
1
truncate table ostatki

Выполнение данного оператора требует чуть-ли не админских прав, как минимум - владелец БД...

@katamoto · 10.07.2025, 12:49

Сообщение от Andrey-MSK

Выполнение данного оператора требует чуть-ли не админских прав, как минимум - владелец БД...

Да ладно... Права на ALTER таблицы должно хватить, емнип. В крайнем случае - db_ddladmin

@uaggster · 10.07.2025, 13:15

Обертку сделайте. Хранимку, которая будет запускаться AS OWNER, и в ней - что хотите.

Новые блоги и статьи Все статьи Все блоги /
Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .	Поиск "дружественных имён" СОМ портов Argus19 22.11.2025 Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .	Сколько Государство потратило денег на меня, обеспечивая инсулином. Programma_Boinc 20.11.2025 Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .
Ломающие изменения в C#.NStar Alpha Etyuhibosecyu 20.11.2025 Уже можно не только тестировать, но и пользоваться C#. NStar - писать оконные приложения, содержащие надписи, кнопки, текстовые поля и даже изображения, например, моя игра "Три в ряд" написана на этом. . .	Мысли в слух kumehtar 18.11.2025 Кстати, совсем недавно имел разговор на тему медитаций с людьми. И обнаружил, что они вообще не понимают что такое медитация и зачем она нужна. Самые базовые вещи. Для них это - когда просто люди. . .	Создание Single Page Application на фреймах krapotkin 16.11.2025 Статья исключительно для начинающих. Подходы оригинальностью не блещут. В век Веб все очень привыкли к дизайну Single-Page-Application . Быстренько разберем подход "на фреймах". Мы делаем одну. . .	Фото: Daniel Greenwood kumehtar 13.11.2025	Расскажи мне о Мире, бродяга kumehtar 12.11.2025 — Расскажи мне о Мире, бродяга, Ты же видел моря и метели. Как сменялись короны и стяги, Как эпохи стрелою летели. - Этот мир — это крылья и горы, Снег и пламя, любовь и тревоги, И бескрайние. . .

@iLinks 799 / 457 / 237 Регистрация: 03.01.2017 Сообщений: 1,337
	26.06.2025, 18:53
	lcnet, Для товара `uniflott` есть записи только в таблице `purch` и нет записей в таблице `sales`. При `left join sales`, `v2.cnt` равно null. Любая операция с null, дает null (арифметическая). Как вылечить, обернуть `v2.cnt` в обработку `null` с заменой на 0, например `ISNULL(v2.cnt,0)` 0

@uaggster 1302 / 356 / 97 Регистрация: 14.10.2022 Сообщений: 1,084
	10.07.2025, 13:15
	Обертку сделайте. Хранимку, которая будет запускаться AS OWNER, и в ней - что хотите. 0