1

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

enter image description here

Table 2: Payment

SELECT ClientNo, PaymentDate, TotalAmountPaid 
FROM Payment_TBL 
WHERE ClientNo = 1 
ORDER BY PaymentDate ASC

enter image description here

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

enter image description here

i'm new to SQL database. Thank you

1 Answer 1

3

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 < a.StatementDate
    OR b.StatementDate = a.StatementDate AND b.Type < a.Type
    OR b.StatementDate = a.StatementDate AND b.Type = a.Type AND b.ID <= a.ID
GROUP BY a.StatementDate, a.Type, a.ID, a.InvoiceNo, a.TotalDue, a.TotalPaid
ORDER BY a.StatementDate, a.Type, a.ID ;
8
  • Thanks for your answer.. i also tested at SQLfiddle working fine but when i applied in sql server management studio and execute... i'm getting error.. "Msg 102, Level 15, State 1, Line 14 Incorrect syntax near 'order'. " please help me.. Thaks again ypercube
    – M.I.M.F
    May 15, 2015 at 17:03
  • I suppose you have different columns as PKs in your tables? Change the InvoiceID and PaymentID in my code to match your PKs (assuming you have integer columns as PKs) May 15, 2015 at 17:05
  • Oh sorry, you have 2008R2 version. I think the aggregates with over were added in 2012. May 15, 2015 at 17:08
  • yes problem in over what shall i do for that.. thanks for your answer and comments...
    – M.I.M.F
    May 15, 2015 at 17:14
  • I've edited the answer. May 15, 2015 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.