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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
create table #t (dt int, Bad_out money, Good_out money, Own_in money, Loan_in money)

insert into #t values 
 (1	,NULL	,NULL	,300	,NULL )
,(2	,NULL	,100	,NULL	,NULL )
,(3	,NULL	,NULL	,NULL	,5000 )
,(4     ,200    ,NULL	,NULL	,NULL )
,(5	,NULL	,100	,50     ,NULL )
,(6	,100	,NULL	,NULL	,NULL )
,(7	,NULL	,400	,300	,NULL )
,(8	,1000	,NULL	,NULL	,1000 )
,(9	,NULL	,NULL	,300	,NULL )
,(10    ,NULL   ,2500   ,NULL   ,NULL )
select * from #t



Bad_out - траты на плохие цели, Good_out - списание на хорошие цели.
Own_in - поступление собственных средств, Loan_in - поступление заемные денег.

Нужно создать столбцы с остатками по собственным (Own) и по заемным (Loan) деньгам на каждую операцию, с условием, что:
  • Bad_out (плохие траты) списываются приоритетно со счета Own (собств. ср-в), а если не хватает, то с Loan.
  • Good_out (хорошие траты) списаывают вначале со счета Loan (заемные средства), а если на Loan не хватает, то с Own.
Уже полдня думаю как этот учет сделать..
...
Рейтинг: 0 / 0
16.01.2020, 15:30
| Ответить | Цитировать | Написать  
Два нарастающих итога с условием
    #39914895
mrbungle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mrbungle
Гость
mrbungle

Нужно создать столбцы с остатками по собственным (Own) и по заемным (Loan) деньгам на каждую операцию


собственно вот желаемый результат: столбцы Own_outbal money и Loan_outbal money
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
create table #t (dt int, Bad_out money, Good_out money, Own_in money, Loan_in money, Own_outbal money, Loan_outbal money)
insert into #t values 
 (1 ,NULL   ,NULL   ,300    ,NULL ,300 ,0 )
,(2 ,NULL   ,100    ,NULL   ,NULL ,200 ,0 )
,(3 ,NULL   ,NULL   ,NULL   ,5000 ,200 , 5000 )
,(4 ,200    ,NULL   ,NULL   ,NULL ,0 ,5000 )
,(5 ,NULL   ,100    ,50     ,NULL ,50 ,4900 )
,(6 ,100    ,NULL   ,NULL   ,NULL ,50 ,4800 )
,(7 ,NULL   ,400    ,300    ,NULL ,350 ,4400 )
,(8 ,1000   ,NULL   ,NULL   ,1000 ,350 ,4400 )
,(9 ,NULL   ,NULL   ,300    ,NULL ,650 ,4400 )
,(10,NULL   ,2500   ,NULL   ,NULL ,650 ,1900 )
select * from #t
...
Рейтинг: 0 / 0
16.01.2020, 16:18
| Ответить | Цитировать | Написать  
Два нарастающих итога с условием
    #39914905
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
mrbungle,

нетрадиционна задача для реляционных СУБД.
Самое простое решение - перебор строк таблицы, например, курсором.
...
Рейтинг: 0 / 0
16.01.2020, 16:48
| Ответить | Цитировать | Написать  
Два нарастающих итога с условием
    #39914906
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
а правила "ухода в минус" есть?
...
Рейтинг: 0 / 0
16.01.2020, 16:50
| Ответить | Цитировать | Написать  
Два нарастающих итога с условием
    #39914916
mrbungle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mrbungle
Гость
msLex
а правила "ухода в минус" есть?

В минус нельзя уйти.
Еще точных требований не получил, но предполагаю, если к примеру остаток на обоих счета 50 и 50, и сумма списания 100, то тогда с обоих счетов спишет по 50.
...
Рейтинг: 0 / 0
16.01.2020, 17:03
| Ответить | Цитировать | Написать  
Два нарастающих итога с условием
    #39914928
Фотография court
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
court
Участник
mrbungle
mrbungle


собственно вот желаемый результат: столбцы Own_outbal money и Loan_outbal money
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
create table #t (dt int, Bad_out money, Good_out money, Own_in money, Loan_in money, Own_outbal money, Loan_outbal money)
insert into #t values 
 (1 ,NULL   ,NULL   ,300    ,NULL ,300 ,0 )
,(2 ,NULL   ,100    ,NULL   ,NULL ,200 ,0 )
,(3 ,NULL   ,NULL   ,NULL   ,5000 ,200 , 5000 )
,(4 ,200    ,NULL   ,NULL   ,NULL ,0 ,5000 )
,(5 ,NULL   ,100    ,50     ,NULL ,50 ,4900 )
,(6 ,100    ,NULL   ,NULL   ,NULL ,50 ,4800 )
,(7 ,NULL   ,400    ,300    ,NULL ,350 ,4400 )
,(8 ,1000   ,NULL   ,NULL   ,1000 ,350 ,4400 )
,(9 ,NULL   ,NULL   ,300    ,NULL ,650 ,4400 )
,(10,NULL   ,2500   ,NULL   ,NULL ,650 ,1900 )
select * from #t


В "желаемом результате" не соблюдаются эти условия
mrbungle
  • Bad_out (плохие траты) списываются приоритетно со счета Own (собств. ср-в), а если не хватает, то с Loan.
  • Good_out (хорошие траты) списаывают вначале со счета Loan (заемные средства), а если на Loan не хватает, то с Own.

    • Код: 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.
    create table #t (dt int, Bad_out money, Good_out money, Own_in money, Loan_in money, Own_outbal1 money, Loan_outbal1 money)
insert into #t values 
 (1 ,NULL   ,NULL   ,300    ,NULL ,300 ,0 )
,(2 ,NULL   ,100    ,NULL   ,NULL ,200 ,0 )
,(3 ,NULL   ,NULL   ,NULL   ,5000 ,200 , 5000 )
,(4 ,200    ,NULL   ,NULL   ,NULL ,0 ,5000 )
,(5 ,NULL   ,100    ,50     ,NULL ,50 ,4900 )
,(6 ,100    ,NULL   ,NULL   ,NULL ,50 ,4800 )
,(7 ,NULL   ,400    ,300    ,NULL ,350 ,4400 )
,(8 ,1000   ,NULL   ,NULL   ,1000 ,350 ,4400 )
,(9 ,NULL   ,NULL   ,300    ,NULL ,650 ,4400 )
,(10,NULL   ,2500   ,NULL   ,NULL ,650 ,1900 )
--select * from #t


;with cte as (
	select 
		*
		,Own_outbal		=isnull(Own_in,0)

		,Loan_outbal	=isnull(Loan_in,0)

	from #t 
	where dt = 1

	union all

	select 
		t.*
		,Own_outbal		=case when cte.Own_outbal + isnull(t.Own_in,0) - isnull(t.Bad_out,0) < 0 then 0 else cte.Own_outbal + isnull(t.Own_in,0) - isnull(t.Bad_out,0) end 
						+case when cte.Loan_outbal + isnull(t.Loan_in,0) - isnull(t.Good_out,0) < 0 then cte.Loan_outbal + isnull(t.Loan_in,0) - isnull(t.Good_out,0) else 0 end

		,Loan_outbal	=case when cte.Loan_outbal + isnull(t.Loan_in,0) - isnull(t.Good_out,0) < 0 then 0 else cte.Loan_outbal + isnull(t.Loan_in,0) - isnull(t.Good_out,0) end 
						+case when cte.Own_outbal + isnull(t.Own_in,0) - isnull(t.Bad_out,0) < 0 then cte.Own_outbal + isnull(t.Own_in,0) - isnull(t.Bad_out,0) else 0 end
	from #t t inner join cte on cte.dt=t.dt-1
	)
select * from cte 

drop table #t



    dtBad_outGood_outOwn_inLoan_inOwn_outbal1Loan_outbal1Own_outbalLoan_outbal1NULLNULL300,00NULL300,000,00300,000,002NULL100,00NULLNULL200,000,00200,000,003NULLNULLNULL5000,00200,005000,00200,005000,004200,00NULLNULLNULL0,005000,000,005000,005NULL100,0050,00NULL50,004900,0050,004900,006100,00NULLNULLNULL50,004800,000,004850,007NULL400,00300,00NULL350,004400,00300,004450,0081000,00NULLNULL1000,00350,004400,000,004750,009NULLNULL300,00NULL650,004400,00300,004750,0010NULL2500,00NULLNULL650,001900,00300,002250,00
    ...
    Рейтинг: 0 / 0
    16.01.2020, 17:23
    | Ответить | Цитировать | Написать  
    Два нарастающих итога с условием
        #39914933
    msLex
    		Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    msLex
    Участник
    Код: sql
    1.
    2.
    3.
    4.
    5.
    6.
    7.
    8.
    9.
    10.
    11.
    12.
    13.
    14.
    15.
    16.
    17.
    18.
    19.
    20.
    21.
    22.
    23.
    with cte as (
	select 
		Own = isnull(Own_in, 0)
		, Loan = isnull(Loan_in, 0)
		, dt
	from #t 
	where 
		dt = 1 
	union all 
	select 
		Own = 
			case when  cte.Own + isnull(t.Own_in, 0) >= isnull(t.Bad_out, 0) then cte.own + isnull(t.Own_in, 0) - isnull(t.Bad_out, 0) else 0 end 
				+ case when  cte.Loan + isnull(t.Loan_in, 0) < isnull(t.Good_out, 0) then cte.Loan + isnull(t.Loan_in, 0) - isnull(t.Good_out, 0) else 0 end
		, Loan = 
			case when  cte.Loan + isnull(t.Loan_in, 0) >= isnull(t.Good_out, 0) then cte.Loan + isnull(t.Loan_in, 0) - isnull(t.Good_out, 0) else 0 end 
				+ case when  cte.Own + isnull(t.Own_in, 0) < isnull(t.Bad_out, 0) then cte.own + + isnull(t.Own_in, 0) - isnull(t.Bad_out, 0) else 0 end
		, t.dt
	from #t t 
	inner join cte  on t.dt = cte.dt + 1 
) 
select *
from cte c
inner join #t t  on c.dt = t.dt




    только вот тут
    Код: sql
    1.
    2.
    ,(5 ,NULL   ,100    ,50     ,NULL ,50 ,4900 )
,(6 ,100    ,NULL   ,NULL   ,NULL ,50 ,4800 )



    у меня в итоге получается 0, 4850
    т.к. я расходы по максимуму списываю с приоритетного счета, и только остаток со "вторичного"
    ...
    Рейтинг: 0 / 0
    16.01.2020, 17:26
    | Ответить | Цитировать | Написать  
    Два нарастающих итога с условием
        #39914935
    msLex
    		Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    msLex
    Участник
    court,
    как под копирку писали
    ...
    Рейтинг: 0 / 0
    16.01.2020, 17:29
    | Ответить | Цитировать | Написать  
    Два нарастающих итога с условием
        #39914940
    Фотография court
    		Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    court
    Участник
    msLex
    court,
    как под копирку писали

    ...
    Рейтинг: 0 / 0
    16.01.2020, 17:33
    | Ответить | Цитировать | Написать  
    Два нарастающих итога с условием
        #39914943
    Фотография court
    		Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    court
    Участник
    чуть более "читаемо"

    Код: 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.
    ;with cte as (
	select 
		*
		,Own_outbal	=isnull(Own_in,0)

		,Loan_outbal	=isnull(Loan_in,0)

	from #t 
	where dt = 1

	union all

	select 
		t.*
		,Own_outbal	=case when a.Own_outbal < 0 then 0 else a.Own_outbal end 
						+case when a.Loan_outbal < 0 then a.Loan_outbal else 0 end

		,Loan_outbal	=case when a.Loan_outbal < 0 then 0 else a.Loan_outbal end 
						+case when a.Own_outbal < 0 then a.Own_outbal else 0 end

	from #t t inner join cte on cte.dt=t.dt-1
	cross apply (select 
				cte.Own_outbal + isnull(t.Own_in,0) - isnull(t.Bad_out,0) as Own_outbal
				,cte.Loan_outbal + isnull(t.Loan_in,0) - isnull(t.Good_out,0) as Loan_outbal) a
	)
select * from cte
    ...
    Рейтинг: 0 / 0
    16.01.2020, 17:36
    | Ответить | Цитировать | Написать  
    Два нарастающих итога с условием
        #39914966
    mrbungle
    		Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    mrbungle
    Гость
    court,

    msLex,

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


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