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

Откуда:
Сообщений: 29
Всем привет!

Имеется 1-ая таблица контракта:

- № контракта;
- Период контракта (Количество месяцев, например = 3 месяца)
- Сумма контракта (например = 2100);

Имеется 2-ая таблица контракта:

- № контракта;
- Оплаты по контракту (в разнобой, возможны и частичные оплаты, сумма всех оплат, к примеру = 950);

Списание происходит от 1-го к последнему месяцам!

Нужно получить примерно такой результат, например

№ контракта Период Сумма Оплачено Долг
----------------- ---------------- -------- ------------ ------
К001 1-ый месяц 700 700 0
К001 2-ой месяц 700 250 450
К001 3-ий месяц 700 0 700
15 мар 19, 12:49    [21833619]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
vuginet
Всем привет!

Имеется 1-ая таблица контракта:

- № контракта;
- Период контракта (Количество месяцев, например = 3 месяца)
- Сумма контракта (например = 2100);

Имеется 2-ая таблица контракта:

- № контракта;
- Оплаты по контракту (в разнобой, возможны и частичные оплаты, сумма всех оплат, к примеру = 950);

Списание происходит от 1-го к последнему месяцам!

Нужно получить примерно такой результат, например

№ контракта Период Сумма Оплачено Долг
----------------- ---------------- -------- ------------ ------
К001 1-ый месяц 700 700 0
К001 2-ой месяц 700 250 450
К001 3-ий месяц 700 0 700



Оплаты велись в разные времена, и частично, например:

150
200
100
250
90
160
---------
Сумма = 950
15 мар 19, 12:54    [21833634]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6025
vuginet,

и к какому решению вы пришли? Что не получается?
15 мар 19, 12:56    [21833640]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
TaPaK
vuginet,

и к какому решению вы пришли? Что не получается?


делю всё на 3 не то получается (

2100/3 950/3 Долг
----------- -------- --------

700 316,67 383,33
700 316,67 383,33
700 316,67 383,33
15 мар 19, 13:01    [21833655]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
vuginet
TaPaK
vuginet,

и к какому решению вы пришли? Что не получается?


делю всё на 3 не то получается (

2100/3 950/3 Долг
----------- -------- --------

700 316,67 383,33
700 316,67 383,33
700 316,67 383,33



нужно, чтобы

первые 700 закрыли бы - 1-ый месяц, если имеются ещё оплаты, то
950-700=250 === 250 списались бы со 2-го месяца и т.д.
15 мар 19, 13:05    [21833668]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
Cane Cat Fisher
Member

Откуда:
Сообщений: 1774
Нужны таблицы:

Контракт (уже есть, только сумму отсюда выкинуть, считать ее из следующей таблицы)

CONTR_ID№ контрактаПериод контракта=Количество месяцев
111233


Суммы контрактов по месяцам
SUM_CONTR_BY_MONTH_IDCONTR_IDСумма
111700
211700
311700


Оплаты (номер контракта отсюда выкинуть)
PAYMENT_IDСумма
77950


Связь Оплаты и Суммы контрактов по месяцам
PAYMENT_IDSUM_CONTR_BY_MONTH_IDСумма
771700
772250


Тогда видно, что одна оплата может распределяться по нескольким месяцам контракта, и один месяц контракта может оплачиваться по частям многими оплатами.

А алгоритм заполнения таблицы связи уже должен смотреть, насколько оплачена каждая позиция контракта, и куда распределять очередную оплату.
15 мар 19, 13:31    [21833731]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
TaPaK
vuginet,

и к какому решению вы пришли? Что не получается?



select t1.номер, t1.период, t1.сумма, sum(t2.оплата)/3

from table1 t1, table2 t2

where t1.контракт_ID= t1.контракт_ID

group by t1.номер, t1.период, t1.сумма
15 мар 19, 13:35    [21833739]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
Cane Cat Fisher
Нужны таблицы:

Контракт (уже есть, только сумму отсюда выкинуть, считать ее из следующей таблицы)

CONTR_ID№ контрактаПериод контракта=Количество месяцев
111233


Суммы контрактов по месяцам
SUM_CONTR_BY_MONTH_IDCONTR_IDСумма
111700
211700
311700


Оплаты (номер контракта отсюда выкинуть)
PAYMENT_IDСумма
77950


Связь Оплаты и Суммы контрактов по месяцам
PAYMENT_IDSUM_CONTR_BY_MONTH_IDСумма
771700
772250


Тогда видно, что одна оплата может распределяться по нескольким месяцам контракта, и один месяц контракта может оплачиваться по частям многими оплатами.

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


Спасибо Вам преогромное за аналитику, но это я знаю, и у меня точно так, как Вы говорите.
Но как на MS SQL select написать?
15 мар 19, 13:39    [21833747]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
vuginet
Cane Cat Fisher
Нужны таблицы:

Контракт (уже есть, только сумму отсюда выкинуть, считать ее из следующей таблицы)

CONTR_ID№ контрактаПериод контракта=Количество месяцев
111233


Суммы контрактов по месяцам
SUM_CONTR_BY_MONTH_IDCONTR_IDСумма
111700
211700
311700


Оплаты (номер контракта отсюда выкинуть)
PAYMENT_IDСумма
77950


Связь Оплаты и Суммы контрактов по месяцам
PAYMENT_IDSUM_CONTR_BY_MONTH_IDСумма
771700
772250


Тогда видно, что одна оплата может распределяться по нескольким месяцам контракта, и один месяц контракта может оплачиваться по частям многими оплатами.

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


Спасибо Вам преогромное за аналитику, но это я знаю, и у меня точно так, как Вы говорите.
Но как на MS SQL select написать?



как Вот этот результат заполучить?

№ контракта Период Сумма Оплачено Долг
----------------- ---------------- -------- ------------ ------
К001 1-ый месяц 700 700 0
К001 2-ой месяц 700 250 450
К001 3-ий месяц 700 0 700
15 мар 19, 13:46    [21833761]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6025
vuginet,

долг накопительный что-ли? почему не 1150 в последнем месяце
15 мар 19, 14:03    [21833811]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
TaPaK
vuginet,

долг накопительный что-ли? почему не 1150 в последнем месяце


Не накопительный, но на руки выданная таблица оплат имеетя,
а плательщик как хочет платит, никаких пений не начисляется,
тупо платит сколько хочет, и, несмотря на его частичные платежи,
месяц закрывается, если полностью оплата произведена. А лишняя часть
оплаты начинает гасить следующий в очереди не погашённый месяц.
Т.е., закрыл первый 700 перешёл на следующий.
Проблема моя состоит в том, что я не могу рекурсивно показать в select-е
таблично - результат. Программно могу, но как через select показать?
15 мар 19, 14:31    [21833886]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6025
vuginet
TaPaK
vuginet,

долг накопительный что-ли? почему не 1150 в последнем месяце


Не накопительный, но на руки выданная таблица оплат имеетя,
а плательщик как хочет платит, никаких пений не начисляется,
тупо платит сколько хочет, и, несмотря на его частичные платежи,
месяц закрывается, если полностью оплата произведена. А лишняя часть
оплаты начинает гасить следующий в очереди не погашённый месяц.
Т.е., закрыл первый 700 перешёл на следующий.
Проблема моя состоит в том, что я не могу рекурсивно показать в select-е
таблично - результат. Программно могу, но как через select показать?

почему долг 700, если в третьем месяце долг 1150
15 мар 19, 14:33    [21833889]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
TaPaK
vuginet
пропущено...


Не накопительный, но на руки выданная таблица оплат имеетя,
а плательщик как хочет платит, никаких пений не начисляется,
тупо платит сколько хочет, и, несмотря на его частичные платежи,
месяц закрывается, если полностью оплата произведена. А лишняя часть
оплаты начинает гасить следующий в очереди не погашённый месяц.
Т.е., закрыл первый 700 перешёл на следующий.
Проблема моя состоит в том, что я не могу рекурсивно показать в select-е
таблично - результат. Программно могу, но как через select показать?

почему долг 700, если в третьем месяце долг 1150


Вы правы, если посмотреть в суммарном виде на долг, то это = 1150,
мне не нужно SUM(долг) вытащить, это не проблема, нужно таблично показать
Сумма Оплачено Долг
700 700 0
700 250 450
700 0 700
15 мар 19, 14:36    [21833897]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6025
vuginet,

причёсывать лень, всё сводится к теме накопительного итога
+

;WITH a AS 
(
	SELECT	[Id] = 1, [Suma] = 2100,	[Cnt]	=  3
), opl AS
(
	SELECT 
	*
	FROM (VALUES		
		(1, 150),
		(1, 200),
		(1, 100),
		(1, 250),
		(1, 90 ),
		(1, 160)
	) as val(id,a)
), dolg AS
(
	SELECT  
		a.*,
		M,
		plat = a.Suma/Cnt,
		dolg = SUM(a.Suma/Cnt) OVER (PARTITION BY a.Id ORDER BY M) - FSuma 
	FROM a
	CROSS APPLY
	(
		SELECT number + 1 as M
		FROM master..spt_values
		WHERE Type = 'P'	AND
		Number < a.Cnt
	)	b
	CROSS APPLY
	(
		SELECT 
			Id,
			SUM(a) as FSuma
		
		FROM opl	GROUP BY Id
	) AS C
)
SELECT 
	Id,
	Suma,
	M,
	CASE 
		WHEN plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END) < 0 THEN 0 
		ELSE plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END)
	END
FROM	dolg

15 мар 19, 15:29    [21833965]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
TaPaK
vuginet,

причёсывать лень, всё сводится к теме накопительного итога
+

;WITH a AS 
(
	SELECT	[Id] = 1, [Suma] = 2100,	[Cnt]	=  3
), opl AS
(
	SELECT 
	*
	FROM (VALUES		
		(1, 150),
		(1, 200),
		(1, 100),
		(1, 250),
		(1, 90 ),
		(1, 160)
	) as val(id,a)
), dolg AS
(
	SELECT  
		a.*,
		M,
		plat = a.Suma/Cnt,
		dolg = SUM(a.Suma/Cnt) OVER (PARTITION BY a.Id ORDER BY M) - FSuma 
	FROM a
	CROSS APPLY
	(
		SELECT number + 1 as M
		FROM master..spt_values
		WHERE Type = 'P'	AND
		Number < a.Cnt
	)	b
	CROSS APPLY
	(
		SELECT 
			Id,
			SUM(a) as FSuma
		
		FROM opl	GROUP BY Id
	) AS C
)
SELECT 
	Id,
	Suma,
	M,
	CASE 
		WHEN plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END) < 0 THEN 0 
		ELSE plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END)
	END
FROM	dolg



Спасибо большое, что-то вроде этого, я подгоню всё это под свой селект, а потом сообщу, Спасибо Преогромное!!!
15 мар 19, 15:34    [21833974]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

Откуда:
Сообщений: 29
vuginet
TaPaK
vuginet,

причёсывать лень, всё сводится к теме накопительного итога
+

;WITH a AS 
(
	SELECT	[Id] = 1, [Suma] = 2100,	[Cnt]	=  3
), opl AS
(
	SELECT 
	*
	FROM (VALUES		
		(1, 150),
		(1, 200),
		(1, 100),
		(1, 250),
		(1, 90 ),
		(1, 160)
	) as val(id,a)
), dolg AS
(
	SELECT  
		a.*,
		M,
		plat = a.Suma/Cnt,
		dolg = SUM(a.Suma/Cnt) OVER (PARTITION BY a.Id ORDER BY M) - FSuma 
	FROM a
	CROSS APPLY
	(
		SELECT number + 1 as M
		FROM master..spt_values
		WHERE Type = 'P'	AND
		Number < a.Cnt
	)	b
	CROSS APPLY
	(
		SELECT 
			Id,
			SUM(a) as FSuma
		
		FROM opl	GROUP BY Id
	) AS C
)
SELECT 
	Id,
	Suma,
	M,
	CASE 
		WHEN plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END) < 0 THEN 0 
		ELSE plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END)
	END
FROM	dolg



Спасибо большое, что-то вроде этого, я подгоню всё это под свой селект, а потом сообщу, Спасибо Преогромное!!!


Чуточку поправил,


;WITH a AS 
(
	SELECT	[Id] = 1, [Suma] = 2100,	[Cnt]	=  3
), opl AS
(
	SELECT 
	*
	FROM (VALUES		
		(1, 150),
		(1, 200),
		(1, 100),
		(1, 250),
		(1, 90 ),
		(1, 160)
	) as val(id,a)
), dolg AS
(
	SELECT  
		a.*,
		M,
		plat = a.Suma/Cnt,
		dolg = SUM(a.Suma/Cnt) OVER (PARTITION BY a.Id ORDER BY M) - FSuma 
	FROM a
	CROSS APPLY
	(
		SELECT number + 1 as M
		FROM master..spt_values
		WHERE Type = 'P'	AND
		Number < a.Cnt
	)	b
	CROSS APPLY
	(
		SELECT 
			Id,
			SUM(a) as FSuma
		
		FROM opl	GROUP BY Id
	) AS C
)
SELECT 
	Id,
    M,
	Suma/Cnt as Summa,
	CASE 
		WHEN plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END) < 0 THEN 0 
		ELSE plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END) 
	END  AS Oplata,

    Suma/Cnt - (CASE 
		WHEN plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END) < 0 THEN 0 
		ELSE plat - (CASE WHEN dolg < 0 THEN 0 ELSE dolg END) 
	END) as Dolg
FROM	dolg
15 мар 19, 15:59    [21834012]     Ответить | Цитировать Сообщить модератору
 Re: списания средств ежемесячно  [new]
vuginet
Member

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

А можно как-то While select использовать?

Типа этого:


Оплата=SUM(таблица оплат)

while Оплата> 0
begin
case
when (Оплата > 0) and (Оплата > Долг)

then Долг=0

else Долг=Долг - Оплата

Оплата=Оплата - Долг
end
end
18 мар 19, 16:17    [21836186]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить