我有以下数据

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