How to Get one Single statement of two table value in SQL I wanted to retrieve two table values in one single statement such as,
(Example Bank statements..)
Table 1: Invoice
SELECT ClientNo, InvoiceDate, InvoiceNo, TotalDue 
FROM Invoice_TBL 
WHERE ClientNo = 1 
ORDER BY InvoiceDate ASC


Table 2: Payment
SELECT ClientNo, PaymentDate, TotalAmountPaid 
FROM Payment_TBL 
WHERE ClientNo = 1 
ORDER BY PaymentDate ASC


The Question is how can i merge two table values and retrieve data from client wise date ascending order
I want the result like bellow screenshot

i'm new to SQL database. Thank you
It's not exactly the wanted output but pretty close:

Query for versions 2012+:

; WITH invoices_payments AS
  ( SELECT StatementDate = InvoiceDate, InvoiceNo, TotalDue, TotalPaid = 0.0,
           ID = InvoiceID, Type = 0
    FROM Invoice_TBL 
    WHERE ClientNo = 1 

    UNION ALL

    SELECT PaymentDate, '', 0.0, TotalAmountPaid, 
           ID = PaymentID, Type = 1
    FROM Payment_TBL 
    WHERE ClientNo = 1 
  )
SELECT StatementDate, InvoiceNo, TotalDue, TotalPaid,
       TotalBalance = SUM(TotalDue - TotalPaid) 
                        OVER (ORDER BY StatementDate, Type, ID
                              ROWS UNBOUNDED PRECEDING)
FROM invoices_payments
ORDER BY StatementDate, Type, ID ;


Test at SQLfiddle. I've assumed that you have a primary key (used InvoiceID and PaymentID) in the tables and I used it to make the ordering 100% determinate (so where you have more than one invoices or payments in the same day, you always get the same order in the results.)



For versions before 2012, there are various ways, none as efficient as the OVER (ORDER BY ... ROWS ...) of 2012. You can check this blog post by Aaron Bertrand about various approaches: 
Best approaches for running totals – updated for SQL Server 2012. The more efficient (for 2008R2 versions and older) are - as the blog post tests show - the cursor and the CLR approaches.

Following is one way using a self-join:

; WITH invoices_payments AS
  ( SELECT StatementDate = InvoiceDate, InvoiceNo, TotalDue, TotalPaid = 0.0,
           ID = InvoiceID, Type = 0
    FROM Invoice_TBL 
    WHERE ClientNo = 1 

    UNION ALL

    SELECT PaymentDate, '', 0.0, TotalAmountPaid, 
           ID = PaymentID, Type = 1
    FROM Payment_TBL 
    WHERE ClientNo = 1 
  )
SELECT a.StatementDate, a.InvoiceNo, a.TotalDue, a.TotalPaid,
       TotalBalance = SUM(b.TotalDue - b.TotalPaid) 
FROM invoices_payments AS a
  JOIN invoices_payments AS b
    ON b.StatementDate &lt; a.StatementDate
    OR b.StatementDate = a.StatementDate AND b.Type &lt; a.Type
    OR b.StatementDate = a.StatementDate AND b.Type = a.Type AND b.ID &lt;= a.ID
GROUP BY a.StatementDate, a.Type, a.ID, a.InvoiceNo, a.TotalDue, a.TotalPaid
ORDER BY a.StatementDate, a.Type, a.ID ;