Гость
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Списание расходов по счетам / 3 сообщений из 3, страница 1 из 1
09.11.2021, 12:36
    #40110267
alazanskiy
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Списание расходов по счетам
Наверняка задача уже много раз решалась, не могу найти ответ.
Суть: Есть N счетов, с порядковыми номерами, каждый день на них есть приходы. Так же каждый день есть общая сумма списания с этих счетов. Необходимо списывать эту сумму со счетов по очереди по их порядковым номерам.
В данный момент там цикл по дате, хотелось бы заменить на один запрос.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
DECLARE @MoneyIn TABLE(IncomeDate smalldatetime, IncomeAccount int, IncomeValue int)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220101', 1, 100)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220101', 2, 0)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220101', 3, 200)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220102', 1, 50)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220102', 2, 300)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220102', 3, 120)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220103', 1, 0)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220103', 2, 0)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220103', 3, 0)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220104', 1, 100)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220104', 2, 2600)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220104', 3, 0)

DECLARE @MoneyOut TABLE(OutcomeDate smalldatetime, OutcomeValue int)
INSERT INTO @MoneyOut(OutcomeDate, OutcomeValue) VALUES('20220101', 180)
INSERT INTO @MoneyOut(OutcomeDate, OutcomeValue) VALUES('20220102', 300)
INSERT INTO @MoneyOut(OutcomeDate, OutcomeValue) VALUES('20220103', 1000)
INSERT INTO @MoneyOut(OutcomeDate, OutcomeValue) VALUES('20220104', 200)



В первый день должно списаться с первого счёта всё (100), с третьего 80
Во второй день с первого 50, со второго 250
ну и так далее

Вот что пробую, но мозги уже совсем закипели
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SELECT i.IncomeDate, i.IncomeAccount, i.IncomeValue 
	,SUM(i.IncomeValue) OVER(PARTITION BY i.IncomeAccount ORDER BY i.IncomeDate) AS TotalIncomeForDateByAccount
	,SUM(i.IncomeValue) OVER(ORDER BY i.IncomeDate, i.IncomeAccount) AS IncomeRunTotal
	,itotal.IncomeTotal
	,o.OutcomeValue
	,ototal.OutcomeTotal 
	,CASE WHEN OutcomeTotal >= SUM(i.IncomeValue) OVER(ORDER BY i.IncomeDate, i.IncomeAccount) THEN --тут понятно, употребить нужно всё значение
			i.IncomeValue 
		ELSE
			OutComeTotal -  SUM(i.IncomeValue) OVER(ORDER BY i.IncomeDate, i.IncomeAccount ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) END 
			--вот тут результат неправильный после первой же даты
			--нужно бы использовать это же поле в виде SUM(OutcomeF) с ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING или придумать какую-то формулу, но она не даётся
	 AS OutcomeF 				
FROM @MoneyIn i
	JOIN @MoneyOut o ON i.IncomeDate = o.OutcomeDate
	CROSS APPLY (SELECT SUM(IncomeValue) FROM @MoneyIn ism WHERE ism.IncomeDate <= i.IncomeDate) itotal(IncomeTotal)
	CROSS APPLY (SELECT SUM(OutcomeValue) FROM @MoneyOut os WHERE os.OutcomeDate <= i.IncomeDate) ototal(OutcomeTotal)
ORDER BY i.IncomeDate, i.IncomeAccount


Может кто подаст идею, куда двигаться? Заранее спасибо
...
Рейтинг: 0 / 0
09.11.2021, 13:19
    #40110281
HandKot
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Списание расходов по счетам
alazanskiy,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
select	 x.IncomeDate
	,x.IncomeAccount
	,x.IncomeValue
	,x.all_in
	,x.OutcomeValue	
	,case	when x.all_in + x.IncomeValue <= x.OutcomeValue then x.IncomeValue
		when x.all_in <= x.OutcomeValue and x.OutcomeValue <= x.IncomeValue + x.all_in then x.OutcomeValue - x.all_in
		else 0
	end
from (
	select	 mi.IncomeDate
		,mi.IncomeAccount
		,mi.IncomeValue
		,all_in = sum(mi.IncomeValue) over (partition by mi.IncomeDate order by mi.IncomeAccount) - mi.IncomeValue
		,mo.OutcomeValue
	from @MoneyIn mi
	left join @MoneyOut mo on mo.OutcomeDate = mi.IncomeDate) x
...
Рейтинг: 0 / 0
09.11.2021, 14:18
    #40110324
alazanskiy
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Списание расходов по счетам
HandKot,

спасибо, работает, если не возникает дефицит на счетах. Немного поправил, возможно криво

Код: 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.
44.
45.
46.
DECLARE @MoneyIn TABLE(IncomeDate smalldatetime, IncomeAccount int, IncomeValue int)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220101', 1, 100)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220101', 2, 0)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220101', 3, 200)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220102', 1, 50)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220102', 2, 300)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220102', 3, 120)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220103', 1, 0)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220103', 2, 0)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220103', 3, 0)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220104', 1, 100)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220104', 2, 2600)
INSERT INTO @MoneyIn(IncomeDate, IncomeAccount, IncomeValue) VALUES('20220104', 3, 0)

DECLARE @MoneyOut TABLE(OutcomeDate smalldatetime, OutcomeValue int)
INSERT INTO @MoneyOut(OutcomeDate, OutcomeValue) VALUES('20220101', 180)
INSERT INTO @MoneyOut(OutcomeDate, OutcomeValue) VALUES('20220102', 7300)
INSERT INTO @MoneyOut(OutcomeDate, OutcomeValue) VALUES('20220103', 0)
INSERT INTO @MoneyOut(OutcomeDate, OutcomeValue) VALUES('20220104', 200)

select	 x.IncomeDate
	,x.IncomeAccount
	,x.IncomeValue
	,x.all_in
	,x.all_in_prev
	,x.all_out_prev
	,x.deficit
	,x.OutcomeValue	
	,case	when x.all_in + x.IncomeValue <= x.OutcomeValue then x.IncomeValue
		when x.all_in <= x.OutcomeValue and x.OutcomeValue <= x.IncomeValue + x.all_in then x.OutcomeValue - x.all_in
		else 0
	end 
from (
	select	 mi.IncomeDate
		,mi.IncomeAccount
		,mi.IncomeValue
		,all_in = sum(mi.IncomeValue) over (partition by mi.IncomeDate order by mi.IncomeAccount) - mi.IncomeValue
		,all_in_prev = all_in_prev.val
		,all_out_prev = all_out_prev.val
		,deficit = CASE WHEN ISNULL(all_out_prev.val, 0) > ISNULL(all_in_prev.val, 0) THEN ISNULL(all_out_prev.val, 0) - ISNULL(all_in_prev.val, 0) ELSE 0 END
		,OutcomeValue = mo.OutcomeValue + CASE WHEN ISNULL(all_out_prev.val, 0) > ISNULL(all_in_prev.val, 0) THEN ISNULL(all_out_prev.val, 0) - ISNULL(all_in_prev.val, 0) ELSE 0 END
	from @MoneyIn mi
	left join @MoneyOut mo on mo.OutcomeDate = mi.IncomeDate
	outer apply (select SUM(mis.IncomeValue) from @MoneyIn mis where mis.IncomeDate < mi.IncomeDate) all_in_prev(val)
	outer apply (select SUM(mos.OutcomeValue) from @MoneyOut mos where mos.OutcomeDate < mi.IncomeDate) all_out_prev(val)) x
	
...
Рейтинг: 0 / 0
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Списание расходов по счетам / 3 сообщений из 3, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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