ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / расчет задолженности
14 сообщений из 14, страница 1 из 1
расчет задолженности
    #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, 16:09
| Ответить | Цитировать | Написать  
расчет задолженности
    #39581908
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
RuslanSharipovжелательно без использования курсоров и циклов
а на каком этапе проблемы? алгоритм подсчета не выходит или его реализация?
...
Рейтинг: 0 / 0
10.01.2018, 17:06
| Ответить | Цитировать | Написать  
расчет задолженности
    #39581914
RuslanSharipov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RuslanSharipov
Гость
Shakill, сам алгоритм
...
Рейтинг: 0 / 0
10.01.2018, 17:13
| Ответить | Цитировать | Написать  
расчет задолженности
    #39581917
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
RuslanSharipovShakill, сам алгоритм

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

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

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

Календарь заранее создайте.
...
Рейтинг: 0 / 0
10.01.2018, 17:47
| Ответить | Цитировать | Написать  
расчет задолженности
    #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:08
| Ответить | Цитировать | Написать  
расчет задолженности
    #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:13
| Ответить | Цитировать | Написать  
расчет задолженности
    #39582002
Фотография Shakill
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Shakill
Участник
RuslanSharipovShakill,все равно не то
да, поспешил, календарь нужен
...
Рейтинг: 0 / 0
10.01.2018, 18:38
| Ответить | Цитировать | Написать  
расчет задолженности
    #39582079
RuslanSharipov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
RuslanSharipov
Гость
Владислав Колосов,

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

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

За один проход сразу не представляю, как всё сделать.
...
Рейтинг: 0 / 0
10.01.2018, 22:56
| Ответить | Цитировать | Написать  
расчет задолженности
    #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
11.01.2018, 12:12
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / расчет задолженности
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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