ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Расчёт даты возникновения задолженности. T-SQL
13 сообщений из 13, страница 1 из 1
Расчёт даты возникновения задолженности. T-SQL
    #39824374
vladymyr.k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vladymyr.k
Гость
Доброе утро!

Просьба помочь решить задачу, уже несколько дней как застрял на ней, просто туплю...
Есть исходная таблица с суммой реализации и оплатами. Нужно на каждый день, за заданный период построить баланс и дату возникновения задолженности если такая была.
Период сгенерировал с 01-01-2019 по 31-03-2019, на каждый день по каждому договору, т.к. потом должно загружаться в BI. Входящие оплаты со знаком "-", реализация со знаком "+".
Поля:
Договор - идентификатор договора
id - уникальный идентификатор оплаты или отгрузки
Дата - дата, когда проводилась реализация или поступила оплата
ДатаВознДЗ - это пример того, какой результат должен быть получен. Нужно найти дату возникновения задолженности (первую не погашенную реализацию).

Имеем несколько входящих операций (до начала расчётного периода с 01-01-2019 по 31-03-2019) - id 1-2-3, которые создают входящую задолженность. После идут обычные операции оплаты и реализации.

Во вложении Excel пример с комментариями как должно быть на выходе (результирующий столбец ДатаВознДЗ).

Смотрел тему http://www.sql.ru/forum/622067/raschet-zadolzhennosti-po-metodu-fifo. Не помогло...

Как пробовал решить:
1. Создавал накопительный итог по каждой строчке, после анализировал его. Если предоплата, то ДатаВознДЗ = 9999-12-31, иначе
Дата реализации. Но такой вариант не учитывает что задолженность образовалась раньше и не показывает именно первую не оплаченную реализацию
2. К п.1 добавил условие что если в предыдущей по дате операции накопительный итог был с задолженностью и с учётом текущей операции задолженность остаётся, то берём ДатаВознДЗ с предыдущего. Но такой вариант опять не работает когда входящая оплата гасит самую старую реализацию.
3. Это уже размышления. Может как-то через последующий обход данных через WHILE ?

Решение ищу на обычном SQL или T-SQL.
...
Рейтинг: 0 / 0
08.06.2019, 08:16
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824382
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Ну... освой накопительную сумму. Ничо тут нового нету.
...
Рейтинг: 0 / 0
08.06.2019, 09:20
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824387
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Ща придут знатоки оконных функций и поправят...
Код: 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.
declare @t table(Договор nchar(6) not null, id int not null, Дата datetime not null, Сумма int not null );
insert @t 
select '000248', 1, '20181212', 2700
union all
select '000248', 2,	'20181217',	235
union all
select '000248', 3, '20181227', 	1500
union all
select '000248', 4,	'20190110', 	-3000
union all
select '000248', 5,	'20190117', 	-25
union all
select '000248', 6,	'20190124', 	-1203
union all
select '000248', 7,	'20190125', 	-657
union all
select '000248', 8,	'20190129', 	-1753
union all
select '000248', 9,	'20190130', 	4753
;


with t as ( select * from @t )
  select *, НакопСумма = ( select sum(Сумма) from t as t1 where t1.Договор = t.Договор and t1.Дата <= t.Дата and t1.id <= t.id ) 
    from t;

with t as ( select * from @t )
   , x as ( select *, НакопСумма = ( select sum(Сумма) from t as t1 where t1.Договор = t.Договор and t1.Дата <= t.Дата and t1.id <= t.id ) from t )
  select *
       , ДатаВознДЗ = case when НакопСумма <=0 then null
                           else isnull( 
                                         ( select top(1) Дата from x as x1 
                                              where x1.НакопСумма > 0 
                                                    and x1.Договор = x.Договор 
                                                    and x1.Дата <= x.Дата 
                                                    and x1.Дата > isnull( (select top(1) Дата from x as x2 where x2.НакопСумма <= 0 and x2.Договор = x.Договор and x2.Дата <= x.Дата and x2.id < x.id order by x2.Дата desc, x2.id desc), 0)
                                                    and x1.id < x.id 
                                              order by x1.Дата asc, x1.id asc 
                                         )
                                      , Дата
                                      )
                      end
 from x

;
...
Рейтинг: 0 / 0
08.06.2019, 10:22
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824441
vladymyr.k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vladymyr.k
Гость
aleks222,

Спасибо, но здесь не только накопительный итог. Если бы был просто накопительный итог и выявление даты смены знака накопительного итога, то через INNER JOIN alias это решалось бы на раз-два. Даже с последующей обработкой результатов.

Я несколько увеличил исходную базу парой новых позиций и поправил цифры:
Код: 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.
declare @t table(Договор nchar(6) not null, id int not null, Дата datetime not null, Сумма int not null );
insert @t 
select '000248', 1, '20181212', 2700
union all
select '000248', 2,	'20181217',	235
union all
select '000248', 3, '20181227', 	1500
union all
select '000248', 4,	'20190110', 	-3000
union all
select '000248', 5,	'20190117', 	-25
union all
select '000248', 6,	'20190124', 	-1203
union all
select '000248', 7,	'20190125', 	-657
union all
select '000248', 8,	'20190129', 	-1753
union all
select '000248', 9,	'20190130', 	2753
union all
select '000248', 10,	'20190206', 	700
union all
select '000248', 11,	'20190207', 	1500
union all
select '000248', 12,	'20190208', 	-600
union all
select '000248', 13,	'20190214', 	-1700
union all
select '000248', 14,	'20190215', 	-500
;



Если взять ваш пример решения, то после возникновения задолженности, берётся дата возникновения и она не меняется до момента, пока она не будет полностью погашена, хотя по мере поступления оплаты дата может меняться, т.к. идёт постепенное закрытие долгов.

Забыл уточнить - в первую очередь все входящие оплаты "гасят" самую старую задолженность.

На операции id 9 опять возникает задолженность с датой возникновения 30-01-2019 (всё корректно), остаток по которому 550. id 10-11 задолженность увеличивается. Заходит оплата от клиента (входящие платежи) id 12 и этот платёж больше чем не закрытый остаток по id 9 и он его закрывает с остатком 50 который частично покрывает реализацию id 10. И соответственно, уже первая не оплаченная реализации именно id 10 с датой 06-02-2019.

После, заходит оплата от клиента id 13, которая покрывает не оплаченный остаток по id 10 и опять таки только частично закрывает id 11. И дата задолженности уже становится = дате операции по id 11.

Вложил пример в Excel с комментариями, возможно так проще будет понять проблему.
...
Рейтинг: 0 / 0
08.06.2019, 15:25
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824444
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
на другой БД вот такое решают:
Расчёт задолженности по LIFO
...
Рейтинг: 0 / 0
08.06.2019, 15:40
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824544
vladymyr.k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vladymyr.k
Гость
MazoHist,

Я переписал ваш пример под T-SQL, добавил новые операции. "+" это расходы (продаж, снятие средств со счёта и т.д.), "-" пополнение счёт, входящие платежи.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
IF OBJECT_ID('q', 'U') IS NOT NULL DROP TABLE q;
IF OBJECT_ID('w', 'U') IS NOT NULL DROP TABLE w;

create table dbo.q (DDATE datetime not null, customer nchar(6) not null, deal nchar(6) not null, 
				 currency nchar(6) not null, SSUM int not null);
insert q 
select '20091012', 111110, 111111, 'USD', 5000  union all
select '20091015', 111110, 111111, 'USD', 12000 union all
select '20091017', 111110, 111111, 'USD', 99100 union all
select '20091019', 111110, 111111, 'USD', -7200 union all
select '20091020', 111110, 111111, 'USD', -100000  union all
select '20091022', 111110, 111111, 'USD', -10100 union all
select '20091025', 111110, 111111, 'USD', 15000  union all
select '20091030', 111110, 111111, 'USD', 150000  union all
select '20091030', 111110, 111111, 'USD', -99000 
;
-- создаём таблицу с накопительным итогом по каждой операции в разрезе договоров используя partition by, поле cumulativeTotal
select q1.*, sum(ssum) over (partition by deal order by ddate) as cumulativeTotal into w from q q1

select distinct first_value(ddate) over (partition by deal order by ddate) as "ДатаВознДЗ", deal
from w w1 
where not exists (select 1 from w w2 where w1.deal = w2.deal and w2.cumulativeTotal <= 0 and w1.ddate <= w2.ddate)
	  and w1.DDATE <= CAST(N'20091030' As Date)



Что у меня получилось. Если ставить дату отсечения CAST(N'20091030' As Date), то считает корректно. Но если поставить дату расчёта '20091019' включительно, то оплата 7200 должна была погасить первую расходку и дата просрочки должна стать 15-10-2009.

Посмотрите пожалуйста мой текущий исходник.
...
Рейтинг: 0 / 0
09.06.2019, 08:59
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824551
vladymyr.k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vladymyr.k
Гость
MazoHist,

Извините, это был не ваш пример, а другого форумчанина SkilledJunior

Я его передал как смог под MS SQL:
Код: 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.
-- deal - номер договора
-- sum - сумма
-- date - дата операции

IF OBJECT_ID('t', 'U') IS NOT NULL DROP TABLE t;
IF OBJECT_ID('w', 'U') IS NOT NULL DROP TABLE w;
IF OBJECT_ID('res', 'U') IS NOT NULL DROP TABLE res;

create table dbo.t (DDATE date not null, deal nchar(6) not null, SSUM int not null);
insert t 
select '20091010', 111110,   500 union all
select '20091011', 111110,   600 union all
select '20091012', 111110,  1200 union all
select '20091017', 111110,  -1000 union all
select '20091019', 111110,  -700 union all
select '20091020', 111110, 10000 union all
select '20091022', 111110, -1000 union all
select '20091025', 111110,  1500 union all
select '20091030', 111110, 15000 union all
select '20091030', 111110, -9900            ;
 
select DDate, Deal, SSum, sum_nak, per_day
        , case when sign(sum_nak) <= 0 then 0 else 1 end as rnk_flag1
        , case when sign(sum_nak) > 0  then 0 else 1 end as rnk_flag2 into w
    from 	(
			select DDate, Deal, SSum, DATEDIFF (day, CAST(SYSDATETIME() AS Date), DDate) as per_day, 
			sum(SSUM) over (partition by Deal order by DDate) as sum_nak
			from t
			WHERE DDATE <= CAST(N'20091019' AS date)	
			) tmp

    select DDate, Deal, SSum, sum_nak, per_day, rnk_flag1, rnk_flag2
         , rnk_flag1 * (sum(rnk_flag2) over(order by Deal, DDate) + 1) rnk
		 into res
      from w
     order by Deal, DDate

select DDate, Deal, SSum, sum_nak, per_day, rnk_flag1, rnk_flag2, rnk
     , last_value(sum_nak) over (partition by Deal order by DDate RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "1. Долг"
     , first_value(case when rnk = 0 then null else DDate end) 
        over (partition by Deal order by rnk desc, DDate asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "2. Дату начала текущей (последней) просрочки."
     , case when last_value(sum_nak) over (partition by Deal order by  DDate RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) > 0
            then first_value(case when rnk = 0 then null else per_day end)  over (partition by Deal order by rnk desc, DDate asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
            else null end "3. Кол-во дней текущей просрочки."
  from res
  ORDER BY DDATE


Здесь опять таки, первая оплата -1000 закрывает 2 расходки, но дата всё равно вытягивается первой возникшей задолженности, а нужно первой не закрытой входящими платежами расходки.

Касательно вашего примера, я не смог переписать на T-SQL запрос:

Код: 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.
IF OBJECT_ID('q', 'U') IS NOT NULL DROP TABLE q;
IF OBJECT_ID('w', 'U') IS NOT NULL DROP TABLE w;

create table dbo.q (DDATE datetime not null, customer nchar(6) not null, deal nchar(6) not null, 
				 currency nchar(6) not null, SSUM int not null);
insert q 
select '20091012', 111110, 111111, 'USD', 5000  union all
select '20091015', 111110, 111111, 'USD', 12000 union all
select '20091017', 111110, 111111, 'USD', 99100 union all
select '20091019', 111110, 111111, 'USD', -7200 union all
select '20091020', 111110, 111111, 'USD', -100000  union all
select '20091022', 111110, 111111, 'USD', -10100 union all
select '20091025', 111110, 111111, 'USD', 15000  union all
select '20091030', 111110, 111111, 'USD', 150000  union all
select '20091030', 111110, 111111, 'USD', -99000 
;

select deal
     , max(over_sum), ROW_NUMBER() over (partition by deal order by "DDATE" desc) AS liability_sum
     , min(case when exclude_sign is null then "DDATE" end) AS liability_date
  from (select "DDATE"
             , deal
             , over_sum
             , lead(nullif(sign(over_sum),1), 1, nullif(sign(over_sum),1)) over (partition by deal order by "DDATE") exclude_sign
          from ( select "DDATE", deal, sum("SSUM") over(partition by deal order by "DDATE") over_sum
                 from q
               ) tmp
       ) tmp2
group by deal



Возникает ошибка Column 'tmp2.DDATE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause с указателем на строку "max(over_sum), ROW_NUMBER() over (partition by deal order by "DDATE" desc) AS liability_sum".
Поможете исправить?
Заранее спасибо.
...
Рейтинг: 0 / 0
09.06.2019, 10:18
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824559
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
vladymyr.kMazoHist,

Я переписал ваш пример под T-SQL, добавил новые операции. "+" это расходы (продаж, снятие средств со счёта и т.д.), "-" пополнение счёт, входящие платежи.

 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
IF OBJECT_ID('q', 'U') IS NOT NULL DROP TABLE q;
IF OBJECT_ID('w', 'U') IS NOT NULL DROP TABLE w;

create table dbo.q (DDATE datetime not null, customer nchar(6) not null, deal nchar(6) not null, 
				 currency nchar(6) not null, SSUM int not null);
insert q 
select '20091012', 111110, 111111, 'USD', 5000  union all
select '20091015', 111110, 111111, 'USD', 12000 union all
select '20091017', 111110, 111111, 'USD', 99100 union all
select '20091019', 111110, 111111, 'USD', -7200 union all
select '20091020', 111110, 111111, 'USD', -100000  union all
select '20091022', 111110, 111111, 'USD', -10100 union all
select '20091025', 111110, 111111, 'USD', 15000  union all
select '20091030', 111110, 111111, 'USD', 150000  union all
select '20091030', 111110, 111111, 'USD', -99000 
;
-- создаём таблицу с накопительным итогом по каждой операции в разрезе договоров используя partition by, поле cumulativeTotal
select q1.*, sum(ssum) over (partition by deal order by ddate) as cumulativeTotal into w from q q1

select distinct first_value(ddate) over (partition by deal order by ddate) as "ДатаВознДЗ", deal
from w w1 
where not exists (select 1 from w w2 where w1.deal = w2.deal and w2.cumulativeTotal <= 0 and w1.ddate <= w2.ddate)
	  and w1.DDATE <= CAST(N'20091030' As Date)



Что у меня получилось. Если ставить дату отсечения CAST(N'20091030' As Date), то считает корректно. Но если поставить дату расчёта '20091019' включительно, то оплата 7200 должна была погасить первую расходку и дата просрочки должна стать 15-10-2009.

Посмотрите пожалуйста мой текущий исходник.

Я не большой специалист по диалекту MS SQL, если в этом варианте условие на дату перенести в запрос w - ведь там считается накопительный итог?
...
Рейтинг: 0 / 0
09.06.2019, 10:48
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824565
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Удивительно, насколько замороченно решают простейшие задачи.
Вас теории алгоритмов совсем не учили?

Код: 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.
47.
48.
49.
50.
51.
52.
declare @t table(Договор nchar(6) not null, id int not null, Дата datetime not null, Сумма int not null );
insert @t 
select '000248', 1, '20181212', 2700
union all
select '000248', 2,	'20181217',	235
union all
select '000248', 3, '20181227', 	1500
union all
select '000248', 4,	'20190110', 	-3000
union all
select '000248', 5,	'20190117', 	-25
union all
select '000248', 6,	'20190124', 	-1203
union all
select '000248', 7,	'20190125', 	-657
union all
select '000248', 8,	'20190129', 	-1753
union all
select '000248', 9,	'20190130', 	2753
union all
select '000248', 10,	'20190206', 	700
union all
select '000248', 11,	'20190207', 	1500
union all
select '000248', 12,	'20190208', 	-600
union all
select '000248', 13,	'20190214', 	-1700
union all
select '000248', 14,	'20190215', 	-500
;


with t as ( select * from @t )
  select *
       , НакопДолг = isnull( ( select sum(Сумма) from t as t1 where t1.Сумма >= 0 and t1.Договор = t.Договор and t1.Дата <= t.Дата and t1.id <= t.id ) , 0 )
	   , НакопПлатеж = isnull( ( select -sum(Сумма) from t as t1 where t1.Сумма < 0 and t1.Договор = t.Договор and t1.Дата <= t.Дата and t1.id <= t.id ) , 0 )
    from t;

with t as ( select * from @t )
   , x as ( select *
                 , НакопДолг = isnull( ( select sum(Сумма) from t as t1 where t1.Сумма >= 0 and t1.Договор = t.Договор and t1.Дата <= t.Дата and t1.id <= t.id ) , 0 )
	             , НакопПлатеж = isnull( ( select -sum(Сумма) from t as t1 where t1.Сумма < 0 and t1.Договор = t.Договор and t1.Дата <= t.Дата and t1.id <= t.id ) , 0 )
              from t 
	      )
  select *
       , ДатаВознДЗ = case when НакопДолг <= НакопПлатеж then null
                           else isnull( ( select top(1) x1.Дата from x as x1 where x1.НакопДолг > x.НакопПлатеж and x1.Договор = x.Договор and x1.Дата <= x.Дата and x1.id < x.id order by x1.НакопДолг asc )
                                      , Дата
                                      )
                      end
 from x
;
...
Рейтинг: 0 / 0
09.06.2019, 11:17
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824568
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
то же самое, но через оконные функции

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with t as ( select * from @t )
   , x as ( select *
                 , НакопДолг = sum(iif( Сумма >= 0, Сумма, 0 ) ) over( partition by Договор order by Дата, id  ROWS UNBOUNDED PRECEDING )
	             , НакопПлатеж = sum(iif( Сумма < 0, -Сумма, 0 ) ) over( partition by Договор order by Дата, id  ROWS UNBOUNDED PRECEDING )
              from t 
	      )
  select *
       , ДатаВознДЗ = case when НакопДолг <= НакопПлатеж then null
                           else isnull( ( select top(1) x1.Дата from x as x1 where x1.НакопДолг > x.НакопПлатеж and x1.Договор = x.Договор and x1.Дата <= x.Дата and x1.id < x.id order by x1.НакопДолг asc )
                                      , Дата
                                      )
                      end
 from x
;
...
Рейтинг: 0 / 0
09.06.2019, 11:28
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824645
vladymyr.k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vladymyr.k
Гость
aleks222,

Огромное спасибо!!! Именно то, что нужно. Завтра уже спокойно сяду, разберу логику работы.

P.S.: В обед чуть продвинулся в решении, только приходилось бы через какие-то грабли анализировать итог. ИМХО, данная задача имеет право перекочевать в FAQ.
...
Рейтинг: 0 / 0
09.06.2019, 20:02
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824712
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
vladymyr.k,

2 vladymyr.k

Наполнения двух стаканов накопленными суммами зависимо от нумерации операций
и это не выглядит хорошо

А терминах императивных алгоритмов задача решается надежно с привлечением очередей.

Если поток входных данных отсортирован по договору, затем по дате события
то достаточно одной простой очереди для возникающих просрочек, без поддержки приоритетов.

В PL/SQL в качестве хранилища такой простой очереди в оперативной памяти хорошо годятся
ассоциативные массивы.
В T-SQL, вероятно, надо использовать индексированные табличные переменные

В этом алгоритме есть детали, касающиеся правил работы с суммами оплаты.

А именно, если в потоке данных оплата поступает по времени до наступления просрочки,
необходимо решить, что с этим делать - генерировать сообщение об ошибке в потоке данных,
тихо игнорировать такие суммы, как будто их не было, или накапливать как деньги, поступившие в счет будущих оплат.
Тот же вопрос о допустимости накопдения должен решаться, когда поступившая сумма превышает сумму текущих просрочек.
Учет такого рода деталей дает несовпадающие по поведению алгоритмы.

Приложенная вами модель в Excel файле предполагает накопление суммы оплат,
Во вложении пример, работающий исходя из накопления излишних оплат для будущего использования.

в предположении, что данные курсора отсортированы по договорам и датам платежных событий
общий алгоритм будет такой:


Пока не пуст курсор платежей
Цикл
текущий договор = курсор.идентификатор_договора
ЕСЛИ текущий договор <> предыдущий_договор
TO
СФОРМИРОВАТЬ_ИТОГОВЫЕ_ЗНАЧЕНИЯ_ПРЕДЫДУЩЕГО_ДОГОВОРА
ОЧИСТИТЬ_ОЧЕРЕДЬ_ПРОСРОЧЕК
КОНЕЦ_ЕСЛИ

ЕСЛИ знак(курсор.сумма) = 1
ТО
ДОБАВИТЬ_ПРОСРОЧКУ_В_ОЧЕРЕДЬ

ИНАЧЕ_если знак(курсор.сумма) = -1
Сформировать общую сумму оплат

КОНЕЦ_ЕСЛИ

ЕСЛИ Суцмма оплат есть
ОПЛАЧИВАТЬ_ПЕРВУЮ_В_ОЧЕРЕДИ_ПРОСРОЧКУ( сумма_оплат) -- этот метод должен возвращать неиспользованный остаток, когда по отношению к нему принимается решение о его дальнейшем использовании
КОНЕЦ_ЕСЛИ

курсор.следующая_запись
Конец_Цикла


В приложенном файле моделька решения задачи на VBA.
я скопировал ваши данные, чтобы получить строки для еще одного договора.
В клетке J2 нужно указать дату, на которую должен происходить расчет
Нажатие кнопки рачет расчитывает состояние по просрочка, и выводит, начиная с L2 данные по обработанным договорам.
ПРедварительно область исходных данных сортируется необходимым образом.
То есть, в принципе можно дописывать в конец и смотреть, что получается после нажатия кнопки рассчитать.


Код работы с очередью может показаться сложнее, чем нужно.
Но причины этому просты:
а) в vba нет встроенной поддержки ассоциативных массивов с целочисленными ключами
б) реализацию очереди можно было бы построить на стандартном collection, но это неспортивно.

У меня нет сомнения в том, что аналогичный по характеру код на pl/sql с использованием ассоциативных массивов
обеспечит лучшую по отношению к аналитическим функциям производительность.

Мне императивнный код кажется более управляемым.

По отношению к T-SQL у меня нет сформированной системы ожиданий, но может быть и там подобный подход не полностью бессмысленный.
Это вопрос эксперимента.
...
Рейтинг: 0 / 0
10.06.2019, 01:00
| Ответить | Цитировать | Написать  
Расчёт даты возникновения задолженности. T-SQL
    #39824919
Кесарь
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кесарь
Участник
Народ, будьте проще и действуйте в лоб. Как показывает практика, так оно надёжнее и работать проще.

На каждую новую запись в таблице операций вычисляем/анализируем значения трёх полей:

1. ID операции породившей задолженность (хоть по лифо, хоть по фифо) или ID задолженности (ID отдельной таблицы, где они хранятся)
2. Дата задолженности
3. Остаток по данной(!) задолженности (а не общий остаток по счёту накопительным итогом)

Итак, долг возник. Заполняем поля задолженности значениями, тут всё очевидно.

Затем долг начинает списываться, клиент вносит деньги. Сумма долга уменьшается (по модулю, если вы её решите хранить как отрицательную).

Если она становится равной нулю, то тут два варианта:

1. Если общая сумма по счёту >=0 то всё опять же прозрачно. На данной записи поля по задолженности будут уже нулловыми. В отдельной таблице задолженность закрывается.

2. Если же общая сумма < 0 то дата задолженности и её ID (или ID породившей её операции) помогут в поиске следующей/предъидущей по времени задолженности (я не знаю, фифо у вас или лифо). Когда таковая найдена, то её значения ставятся в соответствующие поля.


Система простая, наглядная, удобная. Можно по ней строить отчёты с минимумом вычислений, делать индексы, вьюхи и так далее.
...
Рейтинг: 0 / 0
10.06.2019, 13:10
| Ответить | Цитировать | Написать  
13 сообщений из 13, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Расчёт даты возникновения задолженности. T-SQL
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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