ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / как решить?
6 сообщений из 6, страница 1 из 1
как решить?
    #39788133
vuginet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vuginet
Гость
SELECT * FROM PAYMENT - 1-я таблица должен оплатить ежемесячно (3 раза по 700)
------------------------------------------------------
ID CONTRACT AMOUNT
1 A001 700.00
2 A001 700.00
3 A001 700.00
-------------------------------------------------------
-------------------------------------------------------

SELECT * FROM PAYMENT_HISTORY - 2-я таблица частичных оплат

ID CONTRACT PAYED_AMOUNT
1 A001 100.00
2 A001 200.00
3 A001 300.00
4 A001 400.00
5 A001 500.00
-------------------------------------------------------
-------------------------------------------------------

надо получить такой результаты:

ID CONTRACT AMOUNT PAYED_AMOUNT DOLG
1 A001 700.00 700 0
2 A001 700.00 700 0
3 A001 700.00 100 600
...
Рейтинг: 0 / 0
19.03.2019, 02:29
| Ответить | Цитировать | Написать  
как решить?
    #39788137
vuginet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vuginet
Гость
написал, но не смог получить результат, указанный выше (

---------------------------------------------------
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SELECT A.ID, A.CONTRACT, A.AMOUNT, 
       
	   CASE 
		 WHEN OPL.TOTAL - (CASE WHEN A.AMOUNT < 0 THEN 0 ELSE A.AMOUNT END) < 0 
		 THEN 0 
		 ELSE OPL.TOTAL - (CASE WHEN A.AMOUNT < 0 THEN 0 ELSE A.AMOUNT END) 
	   END  AS Oplata,

       A.AMOUNT-
	   CASE 
		 WHEN OPL.TOTAL - (CASE WHEN A.AMOUNT < 0 THEN 0 ELSE A.AMOUNT END) < 0 
		 THEN 0 
		 ELSE OPL.TOTAL - (CASE WHEN A.AMOUNT < 0 THEN 0 ELSE A.AMOUNT END) 
	   END  AS Dolg

FROM   PAYMENT A
  
CROSS APPLY 
      (SELECT SUM(B.PAYED_AMOUNT) AS TOTAL 
	   FROM   PAYMENT_HISTORY B) OPL
...
Рейтинг: 0 / 0
19.03.2019, 03:16
| Ответить | Цитировать | Написать  
как решить?
    #39788138
vuginet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vuginet
Гость
vuginetнаписал, но не смог получить результат, указанный выше (

---------------------------------------------------
SELECT A.ID, A.CONTRACT, A.AMOUNT,

CASE
WHEN OPL.TOTAL - (CASE WHEN A.AMOUNT < 0 THEN 0 ELSE A.AMOUNT END) < 0
THEN 0
ELSE OPL.TOTAL - (CASE WHEN A.AMOUNT < 0 THEN 0 ELSE A.AMOUNT END)
END AS Oplata,

A.AMOUNT-
CASE
WHEN OPL.TOTAL - (CASE WHEN A.AMOUNT < 0 THEN 0 ELSE A.AMOUNT END) < 0
THEN 0
ELSE OPL.TOTAL - (CASE WHEN A.AMOUNT < 0 THEN 0 ELSE A.AMOUNT END)
END AS Dolg

FROM PAYMENT A

CROSS APPLY
(SELECT SUM(B.PAYED_AMOUNT) AS TOTAL
FROM PAYMENT_HISTORY B) OPL

получил:

ID CONTRACT AMOUNT Oplata Dolg 1 A001 700.00 800.00 -100.00 2 A001 700.00 800.00 -100.00 3 A001 700.00 800.00 -100.00
...
Рейтинг: 0 / 0
19.03.2019, 03:20
| Ответить | Цитировать | Написать  
как решить?
    #39788141
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
vuginet,

ну, тут не всё так прямолинейно, как вы пытаетесь написать. нужно немного хитрее подходить с поставленной задаче:
Код: 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.
--
-- Ваши тестовые данные:
with PAYMENT(ID, CONTRACT, AMOUNT) as
(
select * from ( values
(1,'A001', 700.00),
(2,'A001', 700.00),
(3,'A001', 700.00)) v(i,c,a)
)
, PAYMENT_HISTORY(ID, CONTRACT, PAYED_AMOUNT) as
(
select * from (values
(1, 'A001',	100.00),
(2, 'A001',	200.00),
(3, 'A001',	300.00),
(4, 'A001', 400.00),
(5, 'A001',	500.00)) v(i,c,p)
)
--
-- Возможный вариант решения:
select id, contract, amount, total_pay
     , case when x_sum > 0 then x_sum else 0 end dolg
  from (
         select p.* , h.total_pay
              , sum(p.AMOUNT) over(partition by p.CONTRACT order by p.id) - isnull(h.total_pay,0) as x_sum
           from PAYMENT p
           left join (
                       select CONTRACT, sum(PAYED_AMOUNT) as total_pay 
                         from PAYMENT_HISTORY
                        group by CONTRACT
                     ) h
             on p.CONTRACT = h.CONTRACT
       )v
...
Рейтинг: 0 / 0
19.03.2019, 04:39
| Ответить | Цитировать | Написать  
как решить?
    #39788142
vuginet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vuginet
Гость
Щукина Анна,
Спасибо Прелестная Анна

Вариант а)
-----------------------
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select id, contract, amount, 

       case when x_sum < 0 then amount else amount-x_sum end oplata,
	   
       case when x_sum > 0 then x_sum else 0 end dolg

  from (select p.* , h.total_pay, 
               sum(p.AMOUNT) over(partition by p.CONTRACT order by p.id) - isnull(h.total_pay,0) as x_sum
          
		from   PAYMENT p
		left join (select CONTRACT, sum(PAYED_AMOUNT) as total_pay 
                   from PAYMENT_HISTORY
                   group by CONTRACT) h
            
			      on p.CONTRACT = h.CONTRACT)v





Вариант б)
-----------------------


Код: 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 a AS 
            (SELECT	MIN(ID) AS UD, SUM(AMOUNT) AS Suma, COUNT(ID) AS Cnt FROM PAYMENT), 
		      
	opl AS
            (SELECT ID, PAYED_AMOUNT FROM PAYMENT_HISTORY), 

   dolg AS
            (SELECT a.*, M, plat = a.Suma/Cnt, dolg = SUM(a.Suma/Cnt) OVER (PARTITION BY a.UD 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 SUM(PAYED_AMOUNT) as FSuma FROM opl ) AS C)

SELECT 
	UD, 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 BORC

FROM	dolg
...
Рейтинг: 0 / 0
19.03.2019, 05:09
| Ответить | Цитировать | Написать  
как решить?
    #39789571
vuginet
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vuginet
Гость
Окончательный вариант

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT ID, COUNT_PAY, TOTAL_PAY, CONTRACT, AMOUNT,
   
   CASE WHEN SUMMA < 0 THEN AMOUNT ELSE (CASE WHEN SUMMA-AMOUNT<0 THEN AMOUNT-SUMMA ELSE 0 END) END AS OPLATA,

  AMOUNT-CASE WHEN SUMMA < 0 THEN AMOUNT ELSE (CASE WHEN SUMMA-AMOUNT<0 THEN AMOUNT-SUMMA ELSE 0 END) END AS DOLG


FROM (
       SELECT PP.*, H.COUNT_PAY, H.TOTAL_PAY, 

	          SUM(PP.AMOUNT) OVER (PARTITION BY PP.CONTRACT ORDER BY PP.ID) - ISNULL(H.TOTAL_PAY,0) AS SUMMA
	   
       FROM   PAYMENT PP 

       LEFT JOIN (SELECT   PH.CONTRACT, COUNT(PH.ID) AS COUNT_PAY, SUM(PH.PAYED_AMOUNT) AS TOTAL_PAY 
	              FROM     PAYMENT_HISTORY  PH 
                  GROUP BY PH.CONTRACT) H 
				  
	   ON  PP.CONTRACT=H.CONTRACT) V
...
Рейтинг: 0 / 0
21.03.2019, 13:59
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / как решить?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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