我有以下数据
Date Trans Detail Amt Payment
5/4/18 Inv Inv_1 100 0.00
5/4/18 Inv Inv_2 500 0.00
5/4/18 Payment Inv_1,Inv_2 0.0 400
5/6/18 Inv Inv_2 500 0.00
5/6/18 Payment Inv_2 0.0 600
5/6/18 credit credit 500 0.00
5/8/18 Inv Inv_3 100 0.00
我需要一个查询来得到下面结果中的余额
Date Trans Detail Amt Payment Balance
5/4/18 Inv Inv_1 100 0.00 100
5/4/18 Inv Inv_2 500 0.00 600
5/4/18 Payment Inv_1,Inv_2 0.0 700 -100
5/6/18 Inv Inv_2 500 0.00 400
5/6/18 Payment Inv_2 0.0 600 -200
5/6/18 credit credit 500 0.00 -700
5/8/18 Inv Inv_3 100 0.00 -600
到目前为止我已经试过了,但是搞砸了,请告诉我有什么解决办法来平衡吗?
SELECT t4.invoicedate,t4.transcation,t4.details,t4.amount,t4.payments,t4.balance,
CASE WHEN t4.transcation='Payment Received' THEN @c:=@c+t4.balance -t4.payments END AS paymentbal
FROM
(SELECT @c:=0.0) AS dummyb
CROSS JOIN (
SELECT * FROM (SELECT t1.InvoiceHeaderId,t1.invoicedate,t1.transcation,t1.details,t1.amount,t1.payments,
@d:= @d+t1.amount AS balance
FROM
(SELECT @d:=0.0) AS dummy
CROSS JOIN
( SELECT a.InvoiceHeaderId,a.InvoiceDate,'Invoice'AS transcation,CONCAT(a.InvoiceNumber, ',',a.DueDate)details,a.Total AS amount,
0 payments FROM Table_Invoice a
WHERE a.InvoiceDate BETWEEN '2018-02-02' AND '2018-02-06'
GROUP BY a.InvoiceDate,a.InvoiceNumber,a.DueDate,a.InvoiceHeaderId)t1
UNION ALL
SELECT '',PaymentDate AS invoicedate,'Payment Received'AS transcation,ReferenceNumber AS details,0 amount,SUM(Total)payments,0 balance FROM
Table_PaymentReceived WHERE PaymentDate BETWEEN '2018-02-02' AND '2018-02-06'
GROUP BY PaymentDate
UNION ALL
SELECT '',CreditNoteDate AS invoicedate,'Return Credits ' AS transcation,ReferenceNumber AS details,SUM(Total)amount,0 payments,0 balance FROM
Table_CreditNotes WHERE CreditNoteDate BETWEEN '2018-02-02' AND '2018-02-06'
GROUP BY CreditNoteDate)t2 ORDER BY CAST(t2.invoicedate AS DATE),t2.transcation,t2.details)t4
1条答案
按热度按时间imzjd6km1#
查看下面的代码,这里是我们如何计算平衡的逻辑,它解决了您的问题。如果你还面临这个问题,请告诉我们。