Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 расчет задолженности  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
Добрый день!
Есть таблица расходов:
+
Declare @Sale TABLE (id int, date datetime, summa numeric(19,2))
Insert Into @Sale
Select 1, '01.01.2017', 200
union all
Select 2, '05.01.2017', 1000
Union ALL 
Select 3, '05.01.2017', 300
Union ALL
Select 4, '17.01.2017', 700
Union ALL
select 7, '17.01.2017', 400
Union all 
Select 5, '25.01.2017', 600
Union ALL
Select 6, '26.01.2017', 1000
Union ALL
Select 8, '26.01.2017', 1000
Union ALL
Select 9, '27.01.2017', 1000

таблица платежей
+
Declare @Pay TABLE (id int, date datetime, summa numeric(19,2))
Insert Into @Pay
Select 1, '08.01.2017', 1400
Union ALL
Select 2, '20.01.2017', 1400
Union ALL
Select 3, '31.01.2017', 1000

и таблица связи платежа и расхода(какую часть документа закрывает платеж)
+
Declare @PSD TABLE (sale_id int, pay_id int, summa numeric(19,2))
Insert Into @PSD
Select 1, 1, 200
union ALL
Select 2, 1, 1000
union ALL
Select 3, 1, 200
union ALL
Select 3, 2, 100
union ALL
select 7, 2, 400
union all
Select 4, 2, 700
union ALL
Select 5, 2, 200
union ALL
Select 5, 3, 400
union ALL
Select 6, 3, 600


Помогите написать запрос который отобразит просроченную задолженность на каждый день, исходя из того, что отсрочка платежа составляет 3 дня
10 янв 18, 16:09    [21093959]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
желательно без использования курсоров и циклов
10 янв 18, 16:09    [21093965]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
Shakill
Member

Откуда: мск
Сообщений: 1870
RuslanSharipov
желательно без использования курсоров и циклов

а на каком этапе проблемы? алгоритм подсчета не выходит или его реализация?
10 янв 18, 17:06    [21094268]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
Shakill, сам алгоритм
10 янв 18, 17:13    [21094284]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
Shakill
Member

Откуда: мск
Сообщений: 1870
RuslanSharipov
Shakill, сам алгоритм


объединяете приход и расход в один датасет с разными знаками, причем расход сдвигаете на три дня вперед, ключевое поле - id расхода
потом считаете нарастающий итог в разрезе id расхода с группировкой по дате
далее на каждую дату суммируете только отрицательные значения итогов - это и будет общая просроченная задолженность на эту дату
запрос получится довольно компактный
10 янв 18, 17:16    [21094297]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
Shakill,
видимо я неправильно понял, потому что результат далек от правильного

with cte as (Select id, dateadd(day, 3, date) date, summa
			 From @Sale
			 union all
			 Select sale_id, p.date, -psd.summa summa
			 from @PSD psd
			 inner join @Pay AS p on psd.pay_id = p.id)

Select date, -sum(sumitog) debt
from (select *, (Select IsNUll(sum(summa),0) from cte c where c.id = cc.id and c.Date < cc.date) sumitog
	  from cte cc) s
Where s.sumitog < 0
Group by date
10 янв 18, 17:28    [21094335]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
Shakill
Member

Откуда: мск
Сообщений: 1870
RuslanSharipov,

почти. в cte надо знаки для сумм наоборот поставить и по дате неравенство должно быть нестрогое
10 янв 18, 17:46    [21094405]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7396
RuslanSharipov,

Вам нужен календарь, в каждую клетку которого положите нарастающую сумму расходов и нарастающую сумму платежей (только с учетом смещения в три дня). Разница в клетке и будет долгом.

Календарь заранее создайте.
10 янв 18, 17:47    [21094412]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
Shakill,все равно не то

Declare @Sale TABLE (id int, date datetime, summa numeric(19,2))
Declare @Pay TABLE (id int, date datetime, summa numeric(19,2))
Declare @PSD TABLE (sale_id int, pay_id int, summa numeric(19,2))

Insert Into @Sale
Select 1, '01.01.2017', 10000
union
Select 2, '15.01.2017', 4000

Insert Into @Pay
Select 1, '07.01.2017', 3000
Union ALL
Select 2, '07.01.2017', 3000

Insert Into @PSD
Select 1, 1, 3000
union ALL
Select 1, 2, 3000;

with cte as (Select id, dateadd(day, 3, date) date, -summa summa
			 From @Sale
			 union all
			 Select sale_id, p.date, psd.summa summa
			 from @PSD psd
			 inner join @Pay AS p on psd.pay_id = p.id)

Select date, -sum(sumitog) debt
from (select *, (Select IsNUll(sum(summa),0) from cte c where c.id = cc.id and c.Date <= cc.date) sumitog
	  from cte cc) s
Where s.sumitog < 0
Group by date;
10 янв 18, 18:08    [21094475]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
Владислав Колосов,
Ваш вариант на двух примерах отработал правильно, попробую еще на паре случаев

with 
cteDays([Date]) AS
(
 SELECT @Date_From WHERE @Date_From <= @Date_To
 UNION ALL
 SELECT DATEADD(DAY,1,[Date]) FROM cteDays WHERE [Date] < @Date_To
)

Select c.date, (Select IsNUll(Sum(s.Summa), 0) From @Sale s Where dateadd(day, 3,s.date) <= c.date) - 
  (Select IsNull(Sum(summa),0) From (Select p.date, psd.summa summa
			 from @PSD psd
			 inner join @Pay AS p on psd.pay_id = p.id) psd where psd.Date <= c.Date)
From cteDays c
option (maxrecursion 0)
10 янв 18, 18:13    [21094481]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
Shakill
Member

Откуда: мск
Сообщений: 1870
RuslanSharipov
Shakill,все равно не то

да, поспешил, календарь нужен
10 янв 18, 18:38    [21094567]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
Владислав Колосов,

А как насчёт подсчёта количества дней просрочки(максимальное значение) на каждую дату?
10 янв 18, 21:05    [21094844]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7396
RuslanSharipov,

хм... Задача станет тривиальной, если как-то пронумеровать каждый период долга, а потом подсчитать количество дней по каждому номеру.

За один проход сразу не представляю, как всё сделать.
10 янв 18, 22:56    [21095057]     Ответить | Цитировать Сообщить модератору
 Re: расчет задолженности  [new]
RuslanSharipov
Member

Откуда:
Сообщений: 192
Вообщем, если не ошибся
+
with ctePSD as (
	Select s.id sale_id, dateadd(day, 3, s.date) sale_date, s.summa sale_summa, psd.pay_id, IsNull(p.date, @Date_To) pay_date, IsNull(p.summa, 0) pay_summa, IsNull(psd.summa, 0) summa  
	  from @Sale AS s
	  Left join @PSD psd on s.id = psd.sale_id
	  Left join @Pay p on p.id = psd.pay_id)
, cteDays([Date]) AS
(
 SELECT @Date_From WHERE @Date_From <= @Date_To
 UNION ALL
 SELECT DATEADD(DAY,1,[Date]) FROM cteDays WHERE [Date] < @Date_To
)
, ctePSD2 as (
	Select p.sale_id, p.sale_date, p.sale_summa, 
	  case when p.pay_date = d.Date then pay_id else null end pay_id,
	  case when p.pay_date = d.Date then pay_date else d.Date end pay_date,
	  case when p.pay_date = d.Date then pay_summa else 0 end pay_summa,
	  case when p.pay_date = d.Date then summa else 0 end summa	  
	from ctePSD p
	Left Join cteDays d on d.Date >= p.sale_date	
	)
, ctePSD3 as (
	Select *, (Select Sum(psd.Summa) From ctePSD2 psd Where c.sale_id = psd.sale_id and psd.pay_date <= c.pay_date) sumitog
	From ctePSD2 c)
	
		
Select c.pay_date, max([days]) [tdays]
From (select c.sale_date, c.pay_date, datediff(day, sale_date, pay_date) + 1 [days] 
	  from ctePSD3 c
	  Where c.sale_summa - sumitog > 0) c
Group by c.pay_date
option (maxrecursion 0);


дополняем нулевым приходом каждый расход(от даты расхода + 3дня до последней даты(можно заменить датой когда расход полностью перекроется)) и считаем нарастающий итог прихода для каждого расхода. Выбираем те строки где разница между суммой расхода и нарастающим итогом приходов больше нуля, считаем разницу в днях между датами, и берем максимальное по каждому дню. Может кому пригодится.

Правда незнаю как на длинном промежутке с большими движениями отработает запрос,попробую вместо cte'ешек темповые таблицы с индексами чтобы ускорить.

Может есть предложения как посчитать по другому?
11 янв 18, 12:12    [21096511]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить