Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
vuginet Member Откуда: Сообщений: 30 |
получил:
|
|||||||
19 мар 19, 03:20 [21836715] Ответить | Цитировать Сообщить модератору |
Щукина Анна Member Откуда: Сообщений: 1507 |
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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
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 | ![]() |