Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Учет трех остатков / 10 сообщений из 10, страница 1 из 1
16.04.2020, 22:09
    #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
| Ответить | Цитировать | Написать  
17.04.2020, 03:02
    #39948242
nullin
nullin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Учет трех остатков
mrbungle, нерекурсивные решения рекурсивных вещей, когда возможно - сложнее в разы. К тому-же во float хранить?

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

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

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

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


1. С учетом списания out_A.
2. Накопительный итог.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.04.2020, 12:20
    #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, 13:07
    #39948345
a_voronin
a_voronin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Учет трех остатков
Shakill,

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

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

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

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

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

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


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