Member 14173753 Ответов: 3

Помогите мне с caculate debit balace и incredit от SQL server 2012

У меня есть столы:
Customer (Id_Customer, Name, Address)

       (1,A, Add1)
       (2,B, Add2)
       (3,C, Add3)
       (4,D, Add4)    
       (5,E, Add5)    

    Receipt (Id_Customer, Money)

       (2, 10)
       (3, 20)
       (2, 15)

    Payment (Id_Customer, Money)

       (1, 30)
       (2, 40)
       (4, 05)
Теперь я хочу показать, как показано ниже:
Name ------- Debit balance ------ In credit
    A ----------------------------------------30

    B ----------------15-------------------------

    C -----------------------------------------20

    D -------------- 05-----------------------

    Total DB: ----- 20-------------Total IC: 50
В том:

если сумма(деньги) квитанции < сумма(деньги) платежа
тогда дебетовое сальдо = сумма(деньги) платежа - сумма(деньги) поступления
еще
В кредите = сумма(деньги) поступления-сумма(деньги) платежа

Обратите внимание, что показывать клиенту можно только в том случае, если дебетовый баланс или кредит отличается от нуля. И составляет более () для подкачки.

Что я уже пробовал:

Я запускаю запрос, но показываю только клиентов, у которых также есть "деньги" в двух таблицах квитанция и оплата (если у клиента есть только деньги в квитанции или платеж не будет отображаться в результате, почему это так? Он не может показать этот кутомер???)
select a.*, Total_DebitBalance=sum(DebitBalance) over (), Total_InCredit=sum(InCredit) over () from (SELECT C.name, C.Address,

CASE WHEN SUM(isnull(R.Money, 0))< SUM(isnull(P.Money, 0)) THEN SUM(isnull(P.Money, 0)) - SUM(isnull(R.Money, 0)) END AS DebitBalance,

CASE WHEN SUM(isnull(R.Money, 0))> SUM(isnull(P.Money, 0)) THEN SUM(isnull(R.Money, 0)) - SUM(isnull(P.Money, 0)) END AS InCredit,

C.Id_Customer, COUNT(*) OVER () AS total_count

FROM Customer C LEFT JOIN Receipt R ON C.Id_Customer = R.Id_Customer LEFT JOIN Payment P ON C.Id_Customer = P.Id_Customer group by C.Id_Customer, C.name, C.Address)a ;
ЭТО И ЕСТЬ РЕЗУЛЬТАТ:
Name ------- Debit balance ------ In credit

B ----------------15-------------------------

Total DB: -----   15-------------Total IC: 0

Пожалуйста, помогите мне. Большое спасибо.

Member 14173753

кто-нибудь может мне помочь?

3 Ответов

Рейтинг:
9

Member 14173753

уважаемые ChiII60,

после исследования вашей справки и всего остального в google и использования приведенного ниже запроса, но он работает очень медленно при загрузке и нажатии следующей страницы на форме C#. который этот запрос, окончательный rerult-это 91 запись (на что я надеюсь).
Не могли бы вы помочь мне улучшить мой запрос на более короткий и быстрый. Большое спасибо.

В запросе я использую 

... FROM Customer C CROSS JOIN Payment P CROSS JOIN Receipt R WHERE (P.Id_Customer=C.Id_Customer OR R.Id_Customer=C.Id_Customer)



query = "SELECT C.Name, C.Address, (case when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer)<(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer) then (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer)-(SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer) when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer ) is null then  (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer)  end) as Debitbalance, (case when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer )>(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) then (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer )-(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) when (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) is null then  (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer)  end) as InCredit, C.Id_Customer Id_Customer, COUNT(*) OVER () AS total_count, SUM(case when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer)<(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer) then (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer)-(SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer) when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer ) is null then  (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer)  end) OVER() AS Total_Debitbalance, SUM(case when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer )>(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) then (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer )-(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) when (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) is null then  (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer)  end) OVER() AS Total_InCredit from Customer C CROSS JOIN Receipt R CROSS JOIN Payment P where (P.Id_Customer=C.Id_Customer OR R.Id_Customer=C.Id_Customer) ";

CHill60

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

Member 14173753

Я использую ниже quey, он final return 91 record (это нормально), но в форме, если пользователь выбирает какое-то условие в качестве даты от x до y, а имя-Джек... Я не могу применить филлер к основному месту, где, как:

запрос += " и (Р. о свидании между @fromdate и todate в сочетании) и (П. о свидании между @fromdate и todate в сочетании) ";

запрос += " и имя как '%' + @имя + '%'";

если пользователь выбирает дату, то ошибка: недопустимое имя столбца thedate


ЭТО МОЙ ВОПРОС:

Запрос= "выбрать c.name,
случай когда
функция isnull(стр. pmoney,0) - функция isnull(Р.rmoney,0) и gt;0
тогда функция isnull(стр. pmoney,0) - функция isnull(Р.rmoney,0)
else null end
дебетбананс,
случай когда
Функция isnull(Р.rmoney,0)-функция isnull(стр. pmoney,0) и gt;0
тогда функция isnull(Р.rmoney,0)-функция isnull(стр. pmoney,0)
else null end incredit

от
клиент с левое соединение
(выберите сумму(деньги) rmoney,id_customer из группы квитанций по id_customer ) как r
на c.id_customer=r.id_customer
левое соединение
(выбрать сумма(денег) pmoney, id_customer от уплаты группу, id_customer) как п
на C.id_customer=стр. id_customer
где r.rmoney не равно нулю или P.pmoney не null" //здесь я добавить в условие WHERE

CHill60

Вы ответили на свой собственный комментарий, Вот почему я не видел этого до сих пор.
Вы должны объявить переменные @fromDate и @toDate в sql-запросе, который будет выполнен. Это динамический запрос. Неясно, какой язык вы используете для этого, но вам нужно либо использовать параметризованный запрос, либо поместить свой запрос в хранимую процедуру и передать ей даты в качестве параметров.

Рейтинг:
1

CHill60

Вот и мы

Причина, по которой некоторые клиенты не появляются, заключается в том, что вы не обрабатываете нулевые значения. Функция SUM будет игнорировать нули при фактическом выполнении суммирования, но если все значения нуль она также будет возвращать значение null. Это, вероятно, легче понять с помощью запроса ..

select C.Id_Customer, [Name], [Address], SUM(R.Money) AS receipt, SUM(P.Money)  AS payment
FROM #Customer C 
LEFT JOIN #Receipt R ON C.Id_Customer = R.Id_Customer 
LEFT JOIN #Payment P ON C.Id_Customer = P.Id_Customer 
GROUP BY C.Id_Customer, Name, Address
давать результат 
1	A	Add1	NULL	30
2	B	Add2	25	80
3	C	Add3	20	NULL
4	D	Add4	NULL	5
5	E	Add5	NULL	NULL
Поэтому первым шагом является изменение этих нулей в значение - то есть ноль 
select C.Id_Customer, [Name], [Address], 
   ISNULL(SUM(R.Money),0) AS receipt, ISNULL(SUM(P.Money),0)  AS payment
FROM #Customer C 
LEFT JOIN #Receipt R ON C.Id_Customer = R.Id_Customer 
LEFT JOIN #Payment P ON C.Id_Customer = P.Id_Customer 
GROUP BY C.Id_Customer, Name, Addres
Теперь у нас есть основа, на которой мы можем построить наши перечисленные условия. Мы могли бы сделать это как подзапрос, например 
SELECT Id_Customer, [Name], [Address],
Debit_Balance = CASE WHEN receipt < payment THEN payment - receipt ELSE 0 END,
In_Credit = CASE WHEN receipt < payment THEN 0 ELSE receipt - payment END
FROM
(select C.Id_Customer, [Name], [Address], ISNULL(SUM(R.Money),0) AS receipt, ISNULL(SUM(P.Money),0)  AS payment
FROM #Customer C 
LEFT JOIN #Receipt R ON C.Id_Customer = R.Id_Customer 
LEFT JOIN #Payment P ON C.Id_Customer = P.Id_Customer 
GROUP BY C.Id_Customer, Name, Address) Q
Или мы могли бы использовать "общее табличное выражение" CTE (Введение в общие табличные выражения (CTE) - Essential SQL[^]) например, 
;with Q as
(
	select C.Id_Customer, [Name], [Address], ISNULL(SUM(R.Money),0) AS receipt, ISNULL(SUM(P.Money),0)  AS payment
	FROM #Customer C 
	LEFT JOIN #Receipt R ON C.Id_Customer = R.Id_Customer 
	LEFT JOIN #Payment P ON C.Id_Customer = P.Id_Customer 
	GROUP BY C.Id_Customer, Name, Address
) 
SELECT Id_Customer, [Name], [Address],
Debit_Balance = CASE WHEN receipt < payment THEN payment - receipt ELSE 0 END,
In_Credit = CASE WHEN receipt < payment THEN 0 ELSE receipt - payment END
FROM Q
Оба они дают совершенно одинаковые результаты..
1	A	Add1	30	0
2	B	Add2	55	0
3	C	Add3	0	20
4	D	Add4	5	0
5	E	Add5	0	0

Member 14173753

Спасибо CHiLl60 vere очень много, я проверю и, пожалуйста, обратите внимание на вас.

Спасибо вам.

Member 14174664

Дорогой CHill60,
Я пытаюсь запустить ваш запрос, но результат возврата не удовлетворяет, это только показывает клиентам, что одновременно в таблицах квитанций и платежей. Это значит, что клиент только в квитанции или только в оплате не вернется в результате. Точно, в моей таблице клиентов есть 604 клиента, в том числе 18 клиентов одновременно в таблицах квитанций и платежей, 91 клиент одновременно в квитанциях и платежах и или в квитанциях или в платежах.
Запрос выше показывает только 18 клиентов. Моя надежда должна быть на 91 клиента.

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

CHill60

Ну, если вы посмотрите на результаты, которые я опубликовал, то клиенты, которые были только в квитанциях, или клиенты, которые были только в платежах, действительно появлялись в окончательных результатах. Левое соединение уместно, иначе они будут "исключены" из результатов.
Опубликуйте свой точный код, и я постараюсь еще раз посмотреть завтра

Member 14173753

CHIII60, я опубликовал свой запрос ниже, но он работает очень медленно. если у вас есть время, пожалуйста, меня!

Рейтинг:
1

virang_21

Вы можете сделать что-то подобное, если это не очень большой стол. 

select X.id,X.Name,X.ReceiptTotal, Y.PaymentTotal, (Y.PaymentTotal-X.ReceiptTotal) as InCreditBalance from (

(select 
c.id,
c.name,
sum(isnull(r.amount,0)) as ReceiptTotal
from Customer c
Left Join Receipt r on c.id=r.cust_id
group by c.id,c.name) X

  LEFT JOIN
  (
select 
c.id,
c.name,
sum(isnull(p.amount,0)) as PaymentTotal
from Customer c
left join Payment p on c.id=p.cust_id
group by c.id,c.name) Y
  on X.id=Y.id
  )


Скрипка SQL[^]

Member 14173753

Я подаю заявку с вашим запросом, но результат неверен, возвращаю всех клиентов в таблицу customer (около 1000 строк, в которой многие клиенты не имеют квитанции или оплаты) и DebitBalance,Incredit value не сопоставляются с DebitBalance и INcredit colums.

Можете ли вы улучшить мой запрос, который я пробовал.

пожалуйста, помогите мне, срочно! Спасибо.

Member 14173753

любой может мне помочь!

CHill60

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

Member 14173753

Спасибо Вам CHil60 очень много, и я сожалею, когда говорю срочно. Я надеюсь, что вы сможете мне помочь. еще раз спасибо за это. Я жду тебя.

CHill60

Я опубликовал решение. Если вы все еще застряли прокомментируйте решение и я постараюсь помочь дальше