ReSQL.ru
     
powered by simpleCommunicator - 2.0.39     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Учет трех остатков
10 сообщений из 10, страница 1 из 1
Учет трех остатков
    #39948226
mrbungle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mrbungle
Гость
Доброго дня. Вопрос по учету нескольких остатков.
Есть следующая таблица с суммарными оборотами поступлений и списаний за день (dt).

Код: 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.
if object_id('tempdb..#t') is not null drop table #t
GO

CREATE TABLE #t (
	[dt] [int] NULL,
	[in_1] [float] NULL,
	[in_2] [float] NULL,
	[in_3] [float] NULL,
	[out_A] [float] NULL,
	[out_B] [float] NULL,
	[outbal_1] [float] NULL,
	[outbal_2] [float] NULL,
	[outbal_3] [float] NULL,
	[OUTBAL] [float] NULL
)
GO
INSERT #t  VALUES 
 (1, 100, 100, 1000, 0, 0, 100, 100, 1000, 1200)
,(2, 0, 0, 0, 250, 50, 0, 0, 900, 900)
,(3, 500, 300, 0, 400, 0, 100, 300, 900, 1300)
,(4, 0, 0, 0, 200, 100, 0, 200, 800, 1000)
,(5, 1000, 0, 0, 0, 1100, 900, 0, 0, 900)
,(6, 0, 0, 0, 300, 200, 400, 0, 0, 400)
GO
select * from #t



Нужно найти значения трех категорий остатков (искомые значение даны):
- outbal_1
- outbal_2
- outbal_3

И соответствующие им три вида поступлений:
- in_1
- in_2
- in_3

Есть определенная логика списаний ( out_A и out_B ) из остатков.
Списания out_A списываются из остатков в следующей последовательности: outbal_1 , outbal_2 , outbal_3 . Т.е. если в остатке outbal_1 не хватает средств на out_A , то недостающая сумма списывается с outbal_2 . Если же на outbal_2 не хватает, то оставшаяся недостающая сумма списывается с outbal_3
У списаний out_B такая же логика, только приоритет источников в обратном порядке: outbal_3, outbal_2, outbal_1 .

Я думал считать остатки используя рекурсию и через case when, но выходит довольно запутанно из-за большого количества case when..
Как-то проще это делается?)
...
Рейтинг: 0 / 0
16.04.2020, 22:09
| Ответить | Цитировать | Написать  
Учет трех остатков
    #39948242
nullin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nullin
Гость
mrbungle, нерекурсивные решения рекурсивных вещей, когда возможно - сложнее в разы. К тому-же во float хранить?

Вам будет проще собраться с силами и сделать рекурсией. Надеюсь данных не миллионы.
Только к примеру - вычислить только outbal_1(от out_A) нерекурсивно - четыре уровня вложенности.
...
Рейтинг: 0 / 0
17.04.2020, 03:02
| Ответить | Цитировать | Написать  
Учет трех остатков
    #39948266
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Осподе, милосердный! Доколе?!!!

Кто придумал эту структуру данных, эту жуткую жуть?

"если в остатке outbal_1 не хватает средств на out_A, то недостающая сумма списывается с outbal_2. Если же на outbal_2 не хватает, то оставшаяся недостающая сумма списывается с outbal_3"
Списывается "в пределах СТРОКИ" или "СО ВСЕХ ПРЕДЫДУЩИХ строк"?
...
Рейтинг: 0 / 0
17.04.2020, 08:10
| Ответить | Цитировать | Написать  
Учет трех остатков
    #39948277
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
aleks222
Кто придумал эту структуру данных, эту жуткую жуть?
Да, модель огонь! :-)

Очевидно, клонировали любимую эксельную таблицу, в которой "считалось всё".
...
Рейтинг: 0 / 0
17.04.2020, 09:02
| Ответить | Цитировать | Написать  
Учет трех остатков
    #39948288
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
mrbungle, out_B рассчитывается независимо, или с учетом того, что было списано в out_A? и да, списание в пределах одной строки либо накопительный итог по всему что было?
...
Рейтинг: 0 / 0
17.04.2020, 09:54
| Ответить | Цитировать | Написать  
Учет трех остатков
    #39948291
mrbungle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mrbungle
Гость
Shakill
mrbungle, out_B рассчитывается независимо, или с учетом того, что было списано в out_A? и да, списание в пределах одной строки либо накопительный итог по всему что было?


1. С учетом списания out_A.
2. Накопительный итог.
...
Рейтинг: 0 / 0
17.04.2020, 10:17
| Ответить | Цитировать | Написать  
Учет трех остатков
    #39948330
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
mrbungle
Shakill
mrbungle, out_B рассчитывается независимо, или с учетом того, что было списано в out_A? и да, списание в пределах одной строки либо накопительный итог по всему что было?


1. С учетом списания out_A.
2. Накопительный итог.

ну да, рекурсия и кейсы
что-то такое

Код: 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.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
with cte as (
	select 
		dt = convert(int, 0),
		in_1 = convert(float, 0),
		in_2 = convert(float, 0),
		in_3 = convert(float, 0),
		out_A = convert(float, 0),
		out_B = convert(float, 0),
		outbal_1 = convert(float, 0),
		outbal_2 = convert(float, 0),
		outbal_3 = convert(float, 0),
		OUTBAL = convert(float, 0)
	union all
	select 
		t1.dt,
		t1.in_1,
		t1.in_2,
		t1.in_3,
		t1.out_A,
		t1.out_B,
		outbal_1 = fin.k1,
		outbal_2 = fin.k2,
		outbal_3 = fin.k3,
		OUTBAL = fin.k1 + fin.k2 + fin.k3
	from cte as t0
	join #t as t1 on t1.dt = t0.dt + 1	
	cross apply (values(t0.outbal_1 + t1.in_1, t0.outbal_2 + t1.in_2, t0.outbal_3 + t1.in_3)) as run(k1, k2, k3)
	cross apply (values(
		case when t1.out_A >= run.k1 then run.k1 else t1.out_A end,
		case when t1.out_A <= run.k1 then 0 when t1.out_A < run.k1 + run.k2 then t1.out_A - run.k1 else run.k2 end,
		case when t1.out_A <= run.k1 + run.k2 then 0 when t1.out_A <= run.k1 + run.k2 + run.k3 then t1.out_A - run.k1 - run.k2 end
	)) as getA(k1, k2, k3)
	cross apply (values(run.k1 - getA.k1, run.k2 - getA.k2, run.k3 - getA.k3)) as mid(k1, k2, k3)
	cross apply (values(
		case when t1.out_B >= mid.k3 then mid.k3 else t1.out_B end,
		case when t1.out_B <= mid.k3 then 0 when t1.out_B < mid.k3 + mid.k2 then t1.out_B - mid.k3 else mid.k2 end,
		case when t1.out_B <= mid.k3 + mid.k2 then 0 when t1.out_B <= mid.k3 + mid.k2 + mid.k1 then t1.out_B - mid.k3 - mid.k2 end
	)) as getB(k3, k2, k1)
	cross apply (values(mid.k1 - getB.k1, mid.k2 - getB.k2, mid.k3 - getB.k3)) as fin(k1, k2, k3)
)
select t.*
from cte as t
where t.dt <> 0
...
Рейтинг: 0 / 0
17.04.2020, 12:20
| Ответить | Цитировать | Написать  
Учет трех остатков
    #39948345
Фотография a_voronin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
a_voronin
Участник
Shakill,

CASEы выглядят громоздко, но работают как правило быстро.

Рекурсия тут на реальных данных, скорее всего ляжет.

Надо идти в цикле по дням и фикчировать результат по каждому дню.
...
Рейтинг: 0 / 0
17.04.2020, 13:07
| Ответить | Цитировать | Написать  
Учет трех остатков
    #39948352
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
a_voronin,

ну это уже от объемов зависит. дней не так уж и много )
можно да, копить агрегаты
или через native процедуру на лету считать
...
Рейтинг: 0 / 0
17.04.2020, 13:39
| Ответить | Цитировать | Написать  
Учет трех остатков
    #39948404
mrbungle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mrbungle
Гость
Shakill,

спасибо, кажется то что нужно!

На проде данных не особо много будет.
И расчет будет производится один раз в день.
...
Рейтинг: 0 / 0
17.04.2020, 16:13
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Учет трех остатков
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru             Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=39948288&tid=1686212]:
 0ms
get settings:
 12ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 255ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 2ms
others: 262ms
total:  621ms
созд. / загр.: 621ms
x
x
Закрыть


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