Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Два нарастающих итога с условием / 11 сообщений из 11, страница 1 из 1
16.01.2020, 15:30
    #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, 16:18
    #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:48
    #39914905
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Два нарастающих итога с условием
mrbungle,

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

В минус нельзя уйти.
Еще точных требований не получил, но предполагаю, если к примеру остаток на обоих счета 50 и 50, и сумма списания 100, то тогда с обоих счетов спишет по 50.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.01.2020, 17:23
    #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:26
        #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:29
        #39914935
    msLex
    msLex
    Участник
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Два нарастающих итога с условием
    court,
    как под копирку писали
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    16.01.2020, 17:33
        #39914940
    court
    court
    Участник
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Два нарастающих итога с условием
    msLex
    court,
    как под копирку писали

    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    16.01.2020, 17:36
        #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, 18:16
        #39914966
    mrbungle
    mrbungle
    Гость
    Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
    Два нарастающих итога с условием
    court,

    msLex,

    спасибо вам большое!
    ...
    Рейтинг: 0 / 0
    | Ответить | Цитировать | Написать  
    Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Два нарастающих итога с условием / 11 сообщений из 11, страница 1 из 1
    Целевая тема:
    Создать новую тему:
    Автор:
    Найденые пользователи ...
    Разблокировать пользователей ...
    Читали тему (0):
    Читали форум (0):
    Пользователи онлайн (0):
    Польз. соглашение
    созд. / загр.: 481ms
    Закрыть
    start [/forum/topic.php?fid=46&mobile=1&tid=1686644]:
    		 0ms
    get settings:
    		 9ms
    get forum list:
    		 13ms
    check forum access:
    		 4ms
    check topic access:
    		 4ms
    track hit:
    		 45ms
    get topic data:
    		 11ms
    get forum data:
    		 2ms
    get page messages:
    		 47ms
    get tp. blocked users:
    		 1ms
    others: 345ms
    total:  481ms
    x
    x
    Закрыть


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