Sql-server – Find over-payment amount using a three-table join

sql server

How to find Overpayment value of only Client No 1 from below 3 tables using inner join? Each client wise I need to retrieve query.

For example 1:

Once I retrieved client 2 only, answer should be 0 because total he invoiced 300 and total he paid for invoice(s) 100 only so overpayment is 0 .

For example 2:

Once I retrieved client 1 only, answer should be 500 because total he invoiced 1500 and total he paid for invoice(s) 2000 only so overpayment is 500.

enter image description here

Best Answer

There is no straight-forward way, just linking these tables, to give the result.

The code below gives you the answers, with the bonus that, letting @ColId null, every Client will be processed.

You may think: it is computationally expensive!

Yes, it may seems so. BUT, check the complementary comments after the code snippet, plz.

DECLARE @CliNo INT

SET @CliNo = 1

;

WITH pay AS
(
    SELECT cli.[Client No], (pay.Amount - inv.Amount) AS DeltaPayment
    FROM
        dbo.[Client TBL] cli
            INNER JOIN(
                SELECT inv.[Client No], SUM(inv.Amount) AS Amount
                FROM dbo.[Invoice TBL] inv
                GROUP BY inv.[Client No]
            ) inv
            ON inv.[Client No] = cli.[Client No]
            --
            INNER JOIN(
                SELECT pay.[Client No], SUM(pay.Amount) AS Amount
                FROM dbo.[Payment TBL] pay
                GROUP BY pay.[Client No]
            ) pay
            ON pay.[Client No] = cli.[Client No]
    WHERE cli.[Client No] = @CliNo OR @CliNo IS NULL
)
SELECT
    pay.[Client No],
    CASE WHEN pay.DeltaPayment > 0 THEN pay.DeltaPayment
                                   ELSE 0
    END AS OverPayment
FROM pay

At first sight, it seems we are asking the SQL Server to SUM every invoice, SUM every payments, and only then computes any overpayment.

But, SQL Server 2012 is a hell of a smart beast.

Check the actual execution plan:

enter image description here

The SQL Server query engine is filtering the payment and invoice records BEFORE sumarizing them.

Hence, no performance penalty, although this is not exactly what you asked for - a simple query with tree direct INNER JOINS.

The complete code for this POC follows:

CREATE TABLE [Client TBL]([Client No] INT NOT NULL PRIMARY KEY)
CREATE TABLE [Invoice TBL]([Invoice No] INT NOT NULL PRIMARY KEY, Amount MONEY, [Client No] INT NOT NULL FOREIGN KEY ([Client No]) REFERENCES [Client TBL])
CREATE TABLE [Payment TBL]([Payment No] INT NOT NULL PRIMARY KEY, Amount MONEY, [Client No] INT NOT NULL FOREIGN KEY ([Client No]) REFERENCES [Client TBL])
go

INSERT INTO dbo.[Client TBL] VALUES(1), (2)
INSERT INTO dbo.[Invoice TBL] VALUES(1, 200, 1), (2, 100, 2), (3, 500, 1), (5, 300, 1), (6, 200, 2), (7, 500, 1)
INSERT INTO dbo.[Payment TBL] VALUES(1, 1000, 1), (2, 100, 2), (4, 1000, 1)
GO

DECLARE @CliNo INT

SET @CliNo = 1

;

WITH pay AS
(
    SELECT cli.[Client No], (pay.Amount - inv.Amount) AS DeltaPayment
    FROM
        dbo.[Client TBL] cli
            INNER JOIN(
                SELECT inv.[Client No], SUM(inv.Amount) AS Amount
                FROM dbo.[Invoice TBL] inv
                GROUP BY inv.[Client No]
            ) inv
            ON inv.[Client No] = cli.[Client No]
            --
            INNER JOIN(
                SELECT pay.[Client No], SUM(pay.Amount) AS Amount
                FROM dbo.[Payment TBL] pay
                GROUP BY pay.[Client No]
            ) pay
            ON pay.[Client No] = cli.[Client No]
    WHERE cli.[Client No] = @CliNo
)
SELECT
    pay.[Client No],
    CASE WHEN pay.DeltaPayment > 0 THEN pay.DeltaPayment
                                   ELSE 0
    END AS OverPayment
FROM pay
go
Related Question