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

Откуда:
Сообщений: 30
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
19 мар 19, 02:29    [21836706]     Ответить | Цитировать Сообщить модератору
 Re: как решить?  [new]
vuginet
Member

Откуда:
Сообщений: 30
написал, но не смог получить результат, указанный выше (

---------------------------------------------------
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


Сообщение было отредактировано: 19 мар 19, 11:29
19 мар 19, 03:16    [21836712]     Ответить | Цитировать Сообщить модератору
 Re: как решить?  [new]
vuginet
Member

Откуда:
Сообщений: 30
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
19 мар 19, 03:20    [21836715]     Ответить | Цитировать Сообщить модератору
 Re: как решить?  [new]
Щукина Анна
Member

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

ну, тут не всё так прямолинейно, как вы пытаетесь написать. нужно немного хитрее подходить с поставленной задаче:
--
-- Ваши тестовые данные:
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 
19 мар 19, 04:39    [21836737]     Ответить | Цитировать Сообщить модератору
 Re: как решить?  [new]
vuginet
Member

Откуда:
Сообщений: 30
Щукина Анна,
Спасибо Прелестная Анна

Вариант а)
-----------------------
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 




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


;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


Сообщение было отредактировано: 19 мар 19, 11:29
19 мар 19, 05:09    [21836754]     Ответить | Цитировать Сообщить модератору
 Re: как решить?  [new]
vuginet
Member

Откуда:
Сообщений: 30
Окончательный вариант

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
21 мар 19, 13:59    [21839566]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить