Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / расчет задолженности / 14 сообщений из 14, страница 1 из 1
10.01.2018, 16:09
    #39581848
RuslanSharipov
RuslanSharipov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
Добрый день!
Есть таблица расходов:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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


таблица платежей
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
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


и таблица связи платежа и расхода(какую часть документа закрывает платеж)
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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 дня
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 16:09
    #39581849
RuslanSharipov
RuslanSharipov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
желательно без использования курсоров и циклов
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 17:06
    #39581908
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
RuslanSharipovжелательно без использования курсоров и циклов
а на каком этапе проблемы? алгоритм подсчета не выходит или его реализация?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 17:13
    #39581914
RuslanSharipov
RuslanSharipov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
Shakill, сам алгоритм
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 17:16
    #39581917
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
RuslanSharipovShakill, сам алгоритм

объединяете приход и расход в один датасет с разными знаками, причем расход сдвигаете на три дня вперед, ключевое поле - id расхода
потом считаете нарастающий итог в разрезе id расхода с группировкой по дате
далее на каждую дату суммируете только отрицательные значения итогов - это и будет общая просроченная задолженность на эту дату
запрос получится довольно компактный
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 17:28
    #39581931
RuslanSharipov
RuslanSharipov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
Shakill,
видимо я неправильно понял, потому что результат далек от правильного

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 17:46
    #39581951
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
RuslanSharipov,

почти. в cte надо знаки для сумм наоборот поставить и по дате неравенство должно быть нестрогое
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 17:47
    #39581952
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
RuslanSharipov,

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

Календарь заранее создайте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 18:08
    #39581966
RuslanSharipov
RuslanSharipov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
Shakill,все равно не то

Код: 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.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 18:13
    #39581969
RuslanSharipov
RuslanSharipov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
Владислав Колосов,
Ваш вариант на двух примерах отработал правильно, попробую еще на паре случаев

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 18:38
    #39582002
Shakill
Shakill
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
RuslanSharipovShakill,все равно не то
да, поспешил, календарь нужен
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 21:05
    #39582079
RuslanSharipov
RuslanSharipov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
Владислав Колосов,

А как насчёт подсчёта количества дней просрочки(максимальное значение) на каждую дату?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.01.2018, 22:56
    #39582121
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
RuslanSharipov,

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

За один проход сразу не представляю, как всё сделать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.01.2018, 12:12
    #39582401
RuslanSharipov
RuslanSharipov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
расчет задолженности
Вообщем, если не ошибся
 
Код: 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.
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'ешек темповые таблицы с индексами чтобы ускорить.

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


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