SQL-запрос (из трёх таблиц)

 
0
 
MS SQL Server
ava
ospts | 15.08.2016, 12:46
Доброго времени суток!

Есть три таблицы 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


Но есть один нюанс, есть клиенты, с которыми провели несколько сделок и нужно узнать, какие сделки уже закрыты, а какие ещё нет.
Пример:
Сумма оплат у Иванова с 01.05.2014 (дата сделки) по текущий момент равна 5000
Сумма оплат у Иванова с 01.02.2015 (дата сделки) по текущий момент равна 3000

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

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

У меня получается:
Клиент=Иванов И.И. / Сумма сделки=10000 / Дата сделки = 01.05.2014 / Оплата = 5000
Клиент=Иванов И.И. / Сумма сделки= 7000 / Дата сделки = 01.02.2015 / Оплата = 5000

А должно было прийти оплат на вторую сделку НОЛЬ!

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

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

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

У меня выходит вот что:
Клиент=Петров П.П. / Сумма сделки=8000 / Дата сделки = 01.01.2014 / Оплата = 8000
Клиент=Петров П.П. / Сумма сделки= 4000 / Дата сделки = 01.01.2015 / Оплата = 4000

Оплата по второй сделке должна быть равна 3000!

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

Подскажите, как нужно изменить запрос, чтобы получился нужный результат?!

Версия SQL 2008
Ответы (13)
ava
Akina | 15.08.2016, 12:52 #
Цитата (ospts @  15.8.2016,  13:46 findReferencedText)
требуется узнать, по каким сделкам прошли оплаты после даты сделки

Для ответа на этот вопрос НЕОБХОДИМО, чтобы в таблице оплат для каждой оплаты было указано, по какой сделке выполняется оплата.

Если же речь идёт об идиотской схеме, когда подобных сведений в таблице оплат нет, а оплата тупо считается погашением по самой старой неоплаченной сделке (какой болван это строил? лучше бы сперва с бухгалтером посоветовался), то единственный выход - это для сделок и для платежей отдельно получить выборки с нарастающим по дате итогом суммы, а потом объединить их, используя в качестве базовой таблицы суммарную выборку всех дат из обеих выборок.
ava
ospts | 15.08.2016, 13:01 #
К сожалению речь идёт о втором варианте...
Такую базу мы получаем уже готовую

А можете поподробнее описать, а то я в SQL ещё не очень силён?!
ava
Akina | 15.08.2016, 13:19 #
Соболезную...

Начальная схема у тебя будет приблизительно такая:

Сперва тебе нужно получить для каждого клиента нарастающий итог по сделкам. Это будет выглядеть как-то типа

SELECT d1.client, d1.deal_id, d1.date, SUM(d2.sum) deal_sum
FROM deals d1, deals d2
WHERE d1.client=d2.client
  AND d1.date>=d2.date
GROUP BY d1.client, d1.deal_id, d1.date

Аналогично получишь для каждого клиента нарастающий итог сумм оплаты.
Затем получишь для каждого клиента суммарный список дат из обеих таблиц. Типа

SELECT deals.client, deals.date
FROM deals
UNION
SELECT payments.client, payments.date
FROM payments

К этому списку привязываешь обе полученные ранее выборки. В результате на каждую дату ты получаешь два значения - общая сумма сделок и общая сумма платежей. Т.е. фактически финансовую историю. Ну и получишь тупым вычитанием текущий баланс на каждую дату.

Когда получишь этот результат - преобразуй его в более компактный. В нём много лишних данных. Тебе достаточно оставить для каждой сделки только одну запись - самую последнюю перед следующей сделкой.
ava
ospts | 15.08.2016, 13:26 #
Спасибо, сейчас буду пробовать!
ava
Akina | 15.08.2016, 13:53 #
Да, когда станешь привязывать обе выборки сумм к выборке дат, тебе нужно заполнить обе части. Посему ты используешь не LEFT JOIN (нафига тебе NULL-ы?), а декартово произведение с отбором по непревышению даты, и, группируя по дате из общего списка, берёшь максимальные дату и сумму из нарастающих итогов (они в выборках нарастающих сумм у тебя по определению должны монотонно расти).
ava
ospts | 15.08.2016, 14:43 #
Не получается запрос для каждого клиента нарастающий итог по суммам оплаты.
Видимо потому, что оплата привязана к сумме сделки!
ava
Akina | 15.08.2016, 14:49 #
Цитата (ospts @  15.8.2016,  15:43 findReferencedText)
Видимо потому, что оплата привязана к сумме сделки

Ну вообще-то некоторые товарищи давно должны были опубликовать DDL своих таблиц с пояснениями. А то ХШ, как назло, в ремонте...
ava
ospts | 15.08.2016, 14:54 #
Прошу прощения! Я не то написал, не к сумме, а к дате сделки...
ava
Akina | 15.08.2016, 15:14 #
Это хрень, а не структура. Публикуй CREATE TABLE своих таблиц. С FK и пояснениями, кто есть ху.
ava
ospts | 15.08.2016, 16:13 #
Чаво-чаво?
ava
ospts | 16.08.2016, 08:47 #
Таблица 1 (Было яблок)
Продукт    Цвет    Количество
Яблоки    Зеленые    4
Яблоки    Красные    7


Таблица 2 (Гости)
Продукт    Гость    Количество
Яблоки    Саша    1
Яблоки    Олег    2
Яблоки    Маша    5
Яблоки    Валентин    3


Очень хочется получить Результат в виде таблицы:

Таблица 3 (Кто каких яблок съел?)

Продукт    Гость    Цвет    Количество
Яблоки    Саша    Зеленые    1
Яблоки    Олег    Зеленые    1
Яблоки    Олег    Красные    1
Яблоки    Маша    Зеленые    2
Яблоки    Маша    Красные    3
Яблоки    Валентин    Красные    3


Устроит любая комбинация Главное требование,чтобы результирующая таблица сходилась с первыми двумя


if @@trancount > 0
rollback;

begin tran;

declare @p table (p_Product varchar(30), p_Color varchar(30), p_Quantity int, p_ProductOrder int identity, primary key (p_Product, p_Color));
declare @g table (g_Product varchar(30), g_Guest varchar(30), g_Quantity int, g_GuestOrder int identity, primary key (g_Product, g_Guest));

insert into @p
 (p_Product, p_Color, p_Quantity)
values
 ('Яблоки', 'Зеленые', 4),
 ('Яблоки', 'Красные', 7);

insert into @g
 (g_Product, g_Guest, g_Quantity)
values
('Яблоки', 'Саша', 1),
('Яблоки', 'Олег', 2),
('Яблоки', 'Маша', 5),
('Яблоки', 'Валентин', 3);

select
t1.p_Product, t1.p_Color, t1.p_Quantity,
isnull(sum(t2.p_Quantity), 0) + 1 as p_QuantityStart, isnull(sum(t2.p_Quantity), 0) + t1.p_Quantity as p_QuantityEnd
into
#p
from
@p t1 left join
@p t2 on t2.p_Product = t1.p_Product and t2.p_ProductOrder < t1.p_ProductOrder
group by
t1.p_Product, t1.p_Color, t1.p_Quantity, t1.p_ProductOrder;

select
t1.g_Product, t1.g_Guest, t1.g_Quantity,
isnull(sum(t2.g_Quantity), 0) + 1 as g_QuantityStart, isnull(sum(t2.g_Quantity), 0) + t1.g_Quantity as g_QuantityEnd
into
#g
from
@g t1 left join
@g t2 on t2.g_Product = t1.g_Product and t2.g_GuestOrder < t1.g_GuestOrder
group by
t1.g_Product, t1.g_Guest, t1.g_Quantity, t1.g_GuestOrder;

select
p.p_Product, p.p_Color, g.g_Guest,
case
  when p.p_QuantityStart >= g.g_QuantityStart and p.p_QuantityEnd <= g.g_QuantityEnd then p.p_Quantity
  when g.g_QuantityStart >= p.p_QuantityStart and g.g_QuantityEnd <= p.p_QuantityEnd then g.g_Quantity
  when g.g_QuantityStart between p.p_QuantityStart and p.p_QuantityEnd then p.p_QuantityEnd - g.g_QuantityStart + 1
  when g.g_QuantityEnd between p.p_QuantityStart and p.p_QuantityEnd then g.g_QuantityEnd - p.p_QuantityStart + 1
end
from
#p p join
#g g on g.g_Product = p.p_Product and p.p_QuantityStart <= g.g_QuantityEnd and p.p_QuantityEnd >= g.g_QuantityStart
order by
p.p_Product, p.p_QuantityStart;

rollback;


Мне посоветовали вот этот пример. Типа его можно переделать под мои условия, заменив людей на сделки, а яблоки на платежи.
Но я хоть убей, не вижу сходства с моей задачей...

Как считаете, можно это использовать?
ava
Akina | 16.08.2016, 10:46 #
Цитата (ospts @  16.8.2016,  09:47 findReferencedText)
Как считаете, можно это использовать? 

Наверное... ведь если посоветовали, то не просто так...
ava
ospts | 16.08.2016, 11:13 #
Не знаю! Я там не вижу ничего общего!

Я вот, что набросал:

declare @c table (ROW_ID int, Name varchar(30), Client int identity, primary key (ROW_ID, Name));
declare @d table (ROW_ID int, Data date, Summa real, Deals int identity, primary key (ROW_ID, Data));
declare @p table (ROW_ID int, Data date, Summa real, Payments int identity, primary key (ROW_ID, Data));

insert into @c
 (ROW_ID, Name)
values
 (1, 'Иванов И.И.'),
 (2, 'Петров П.П.'),
 (3, 'Сидоров С.С.');

insert into @d
 (ROW_ID, Data, Summa)
values
 (1, '01.05.2014', 10000),
 (1, '01.02.2015', 7000),
 (2, '01.01.2014', 8000),
 (2, '01.01.2015', 4000),
 (3, '01.10.2015', 5000);

insert into @p
 (ROW_ID, Data, Summa)
values
 (1, '01.06.2014', 1000),
 (1, '01.01.2015', 1000),
 (1, '01.03.2014', 1000),
 (1, '01.09.2015', 1000),
 (1, '01.12.2015', 1000),
 (2, '01.02.2014', 1000),
 (2, '01.05.2014', 2000),
 (2, '01.08.2014', 1000),
 (2, '01.10.2014', 1000),
 (2, '01.05.2015', 2000),
 (2, '01.04.2015', 2000),
 (2, '01.08.2015', 2000),
 (3, '01.11.2015', 1000),
 (3, '01.01.2016', 2000),
 (3, '01.03.2016', 2000);

SELECT C.Name,U.DATA,U.SUMMA,
    CASE WHEN U.SumPayments > U.SumDealsBefore  
    THEN CASE WHEN U.SUMMA > U.SumPayments - U.SumDealsBefore
         THEN U.SumPayments - U.SumDealsBefore
         ELSE U.SUMMA END
    ELSE 0 END AS SumPayments
FROM (SELECT D.ROW_ID,D.SUMMA,D.DATA,
      ISNULL((SELECT SUM(D1.SUMMA) FROM @d D1 WHERE D1.ROW_ID=D.ROW_ID AND D1.DATA<D.DATA),0) AS SumDealsBefore,
      ISNULL((SELECT SUM(P.SUMMA) FROM @p P WHERE P.ROW_ID=D.ROW_ID),0) AS SumPayments
      FROM @d D) U
LEFT JOIN @c C ON U.ROW_ID=C.ROW_ID


На примере работает...
Зарегистрируйтесь или войдите, чтобы написать.
Фирма дня
Вы также можете добавить свою фирму в каталог IT-фирм, и публиковать статьи, новости, вакансии и другую информацию от имени фирмы.
Подробнее
Участники
  Akina   ospts
advanced
Отправить