ReSQL.ru
     
powered by simpleCommunicator - 2.0.50     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Списание расходов по счетам
3 сообщений из 3, страница 1 из 1
Списание расходов по счетам
    #40110267
alazanskiy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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, 12:36
| Ответить | Цитировать | Написать  
Списание расходов по счетам
    #40110281
Фотография HandKot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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, 13:19
| Ответить | Цитировать | Написать  
Списание расходов по счетам
    #40110324
alazanskiy
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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
09.11.2021, 14:18
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Списание расходов по счетам
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=10&tid=1684123]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 52ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 38ms
get tp. blocked users:
 2ms
others: 13ms
total:  145ms
созд. / загр.: 145ms
x
x
Закрыть


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