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

Откуда:
Сообщений: 38
Доброго времени суток!

Есть три таблицы CLIENTS, DEALS, PAYMENTS. В CLIENTS хранятся данные о клиентах, в DEALS хранятся данные о сделках, а в PAYMENTS хранятся оплаты по сделкам. Оплаты проходят ни полными суммами, а частями в течение некоторого времени. Мне требуется узнать, по каким сделкам прошли оплаты после даты сделки, и вывести или полную сумму сделки, если сумма оплат превышает сумму сделок либо равна ей, или же частично, если сумма оплат меньше суммы сделки.

Я сделал вот такой запрос, вроде бы всё правильно.

SELECT CLIENTS.NAME, DEALS.DATA, DEALS.SUMMA,
SUMPLAT =
CASE
WHEN SUM(ISNULL(PAYMENTS.SUMMA,0)) >= DEALS.SUMMA
THEN DEALS.SUMMA
ELSE PAYMENTS.SUMMA
END
FROM CLIENTS
JOIN DEALS ON DEALS.ROW_ID = CLIENTS.ROW_ID
LEFT JOIN PAYMENTS ON PAYMENTS.ROW_ID = CLIENTS.ROW_ID AND PAYMENTS.DATA >= DEALS.DATA
GROUP BY CLIENTS.NAME, DEALS.DATA, DEALS.SUMMA
ORDER BY CLIENTS.NAME

Но есть один нюанс, есть клиенты, с которыми провели несколько сделок и нужно узнать, какие сделки уже закрыты, а какие ещё нет.
Пример:
Клиент=Иванов И.И. / Сумма сделки=10000 / Дата сделки = 01.05.2014 / Сумма оплат с 01.05.2014 по текущий момент = 5000
Клиент=Иванов И.И. / Сумма сделки= 7000 / Дата сделки = 01.02.2015 / Сумма оплат с 01.02.2015 по текущий момент = 3500

Вот здесь-то и кроется ошибка. В 5000 сидят и 3000. Получается, что на самом деле первая сделка закрыта на 5000, а вторая вообще на 0! Но из выборки следует, что первая на 5000 и вторая на 3000.

Подскажите, как нужно изменить запрос, чтобы избежать этой ошибки?!
12 авг 16, 15:06    [19534671]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
TaPaK
Member

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

я так понимаю читайте про накопительный итог
12 авг 16, 15:10    [19534697]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

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

я так понимаю читайте про накопительный итог

А причём здесь накопительный итог?!
12 авг 16, 15:21    [19534760]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
TaPaK
Member

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

сумма прихода - накопительный итог по сделкам даст 5к для 1ой и 0 для 2й
12 авг 16, 15:24    [19534777]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
хотя оплаты не связанные со сделками это весело
12 авг 16, 15:27    [19534790]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
sti
Member

Откуда:
Сообщений: 769
TaPaK
хотя оплаты не связанные со сделками это весело

+1
А что ТС будет делать, если в один день будет две сделки на одинаковую сумму, я даже боюсь спрашивать :)
12 авг 16, 15:33    [19534824]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

Откуда:
Сообщений: 38
TaPaK
хотя оплаты не связанные со сделками это весело

Ну, сделки погашаются по приоритету по дате
12 авг 16, 15:38    [19534850]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

Откуда:
Сообщений: 38
sti
TaPaK
хотя оплаты не связанные со сделками это весело

+1
А что ТС будет делать, если в один день будет две сделки на одинаковую сумму, я даже боюсь спрашивать :)

Максимум раз в месяц ;)
12 авг 16, 15:40    [19534866]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
sti
Member

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

Если есть возможность заведите табличку Сделка-Платёж-Сумма и заполняйте автоматически в момент платежа. И запрос станет тривиальным.

А так - или поищите по форуму, было уже неоднократно, или предоставьте скрипты со структурой и данными. Кто-нибудь и наваяет вам решение, кому не лень. Правда я всё равно не очень понимаю, что должно получится, например, при двух сделках по 30 и трёх платежах по 20 :)
12 авг 16, 16:01    [19534940]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
ospts
А причём здесь накопительный итог?!
https://www.sql.ru/forum/1018035/zadachka-pro-yabloki-ili-sliyanie-2h-tablic
12 авг 16, 16:12    [19534985]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

Откуда:
Сообщений: 38
sti
ospts,

Если есть возможность заведите табличку Сделка-Платёж-Сумма и заполняйте автоматически в момент платежа. И запрос станет тривиальным.

А так - или поищите по форуму, было уже неоднократно, или предоставьте скрипты со структурой и данными. Кто-нибудь и наваяет вам решение, кому не лень. Правда я всё равно не очень понимаю, что должно получится, например, при двух сделках по 30 и трёх платежах по 20 :)

Возможности к сожалению нет. Базу я получаю уже готовую.

Может я не совсем корректно пример сделал... Попробую точнее!

Пример:
Сумма оплат у Иванова с 01.05.2014 (дата сделки) по текущий момент равна 5000
Сумма оплат у Иванова с 01.02.2015 (дата сделки) по текущий момент равна 3000

Должен получиться следующий результат:
Клиент=Иванов И.И. / Сумма сделки=10000 / Дата сделки = 01.05.2014 / Оплата = 5000
Клиент=Иванов И.И. / Сумма сделки= 7000 / Дата сделки = 01.02.2015 / Оплата = 0

На вторую сделку должен прийти НОЛЬ, т.к. сумма всех оплат <= сумме сделки и 5000 идут в погашение первой сделки!

Второй пример:
Сумма всех оплат Петрова с 01.01.2014 (дата сделки) по текущий момент равна 11000
Сумма всех оплат Петрова с 01.01.2015 (дата сделки) по текущий момент равна 6000

Должен получиться следующий результат:
Клиент=Петров П.П. / Сумма сделки=8000 / Дата сделки = 01.01.2014 / Оплата = 8000
Клиент=Петров П.П. / Сумма сделки= 4000 / Дата сделки = 01.01.2015 / Оплата = 3000

На первую сделку пришло 8000, а на вторую 11000-8000=3000

Так более понятней?!
12 авг 16, 16:20    [19535044]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
ospts,
накопительный итог!, так понятней?


автор
DECLARE @Deal TABLE
(
Code VARCHAR(10),
DateDeal DATETIME,
Suma MONEY
)

INSERT INTO @Deal
(
Code ,
DateDeal ,
Suma
)
SELECT
*
FROM (VALUES
('FIRST','01 jan 2016',5000),
('FIRST','01 feb 2016',3500)
) as a(Code,DateDeal,Suma)

DECLARE @Turns TABLE
(
DateTurn DATETIME,
Suma MONEY
)

INSERT INTO @Turns
(
DateTurn ,
Suma
)
SELECT
*
FROM (VALUES
('10 jan 2016',5000),
('15 dec 2016',300)
) as a(DateTurn,Suma)



SELECT a.*,
CASE
WHEN FullPay - SUM(a.Suma) OVER (PARTITION BY 1/0 ORDER BY DateDeal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) > 0
THEN 'уплОчено'
ELSE 'долг' + str(SUM(a.Suma) OVER (PARTITION BY 1/0 ORDER BY DateDeal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - FullPay )
END as x
FROM
@Deal a
CROSS APPLY
(
SELECT SUM(b.Suma) as FullPay
FROM @Turns b
) as b
12 авг 16, 16:29    [19535080]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

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

Спасибо за пример, попробую сделать запрос.
А это подойдёт для SQL 2008?
12 авг 16, 16:39    [19535115]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
TaPaK
Member

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

Спасибо за пример, попробую сделать запрос.
А это подойдёт для SQL 2008?

неа
12 авг 16, 16:40    [19535119]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

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

жалко :(

А аналог на 2008 есть?
12 авг 16, 16:44    [19535144]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
TaPaK
Member

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

жалко :(

А аналог на 2008 есть?

есть
ГУГЛ -> sql накопительный итог
или вон ссылку уже прям дали, или лень заходить?
12 авг 16, 16:47    [19535161]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

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

заходил, но что там именно смотреть, непонятно...
12 авг 16, 16:50    [19535182]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
Nemoxur
Member

Откуда:
Сообщений: 179
https://www.sql.ru/faq/faq_topic.aspx?fid=125
12 авг 16, 17:17    [19535307]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

Откуда:
Сообщений: 38
Nemoxur
https://www.sql.ru/faq/faq_topic.aspx?fid=125

Это мне не подходит, выбирается совсем не то, что нужно!
15 авг 16, 09:39    [19540444]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

Откуда:
Сообщений: 38
А если организовать цикл по моей первоначальной выборке и в нём уже найти клиентов с несколькими сделками?
Такое возможно сделать в SQL? А то я только запросы не очень сложные делал в SQL'е...
15 авг 16, 16:32    [19542901]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
ospts
А то я только запросы не очень сложные делал в SQL'е...
И уже можете судить что подходит, а что не подходит. Круто.

Вам был дан готовый пример. Всего-то нужно было понять принцип его работы и адаптировать под свои нужды, заменив людей на сделки, а яблоки на платежи.
15 авг 16, 16:51    [19543020]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7869
Если изменить в запросе суммы платежей на 4500 и 200, то получим долг 200 и 3700. Долг, по идее, не должен рассчитываться нарастающим, т.к. для каждой сделки он формируется отдельно. Должно быть 200 и 3500.
15 авг 16, 17:15    [19543163]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов
Если изменить в запросе суммы платежей на 4500 и 200, то получим долг 200 и 3700. Долг, по идее, не должен рассчитываться нарастающим, т.к. для каждой сделки он формируется отдельно. Должно быть 200 и 3500.

при остутсвии прямой связи между сделкой и оплатой, алгоритм может быть любой(какой в ТЗ написан), хоть искать первую сделку которую сумма покроет, хоть просто если при отсортированной дате не хватает - значить не хватет для всех остальных
15 авг 16, 17:28    [19543241]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
aleks2
Guest
-- 1. Не стой под стрелой. Не суй под группировку лишнее
with
   d as ( select *, ROW_NUMBER() over( partition by ROW_ID order by DATA ) as n from DEALS )
,  p as ( select ROW_ID, SUM( SUMMA ) as SUMMA from PAYMENTS group by ROW_ID) -- это следует оформить индексированным VIEW
, dp as ( select d.*
               , case when dd.SUMMA <= p.SUMMA then d.SUMMA when dd.SUMMA - d.SUMMA  < p.SUMMA then p.SUMMA - dd.SUMMA + d.SUMMA else 0 end as [Оплачено]
              from d 
                     outer apply( select SUM( d1.SUMMA ) as SUMMA from d as d1 where d1.ROW_ID = d.ROW_ID and d1.DATA <= d.DATA and d1.n < = d.n ) as dd
                  left outer join p on p.ROW_ID = d.ROW_ID
          )
select c.NAME, * 
  from dp 
       left outer join CLIENTS as c oh dp.ROW_ID = c.ROW_ID
15 авг 16, 17:56    [19543353]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с SQL-запросом  [new]
ospts
Member

Откуда:
Сообщений: 38
invm
ospts
А то я только запросы не очень сложные делал в SQL'е...
И уже можете судить что подходит, а что не подходит. Круто.

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

Ну если получается вообще не тот результат, который мне нужен, то почему я не могу судить, что это мне не подходит?!
16 авг 16, 08:39    [19545252]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить