Sql query to find the customer having due amount

It was a billing application and I got stuck to get the output

Requirement:

I want to get which customerID has due amount on which InvoiceNo by what amount

as CustomerID 1 having total Bill Amount 1000 and had paid 1000 (200+100+700) as AdvanceAmount, that means Invoice 1 is totally paid, and hence customerID 1 is having a due amount of 10000.

I cannot able to write the query for the above problem.

Output should be the row having InvoiceNo2 with due amount 10000.

Here's Table structure

CREATE TABLE [dbo].[AdvancePayment](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Date] [date] NULL,
[CustomerID] [nvarchar](max) NULL,
[InvoiceNo] [nvarchar](max) NULL,
[BillAmt] [decimal](18, 2) NULL,
[AdvanceAmount] [decimal](18, 2) NULL,
[Status] [nvarchar](50) NULL,
[CreatedBy] [nvarchar](max) NULL,
CONSTRAINT [PK_AdvancePayment] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Anil Jha9y ago
Accepted answer
Devendra,

I have preprared below sql which gives the result that you want.
SELECT Distinct AP.CustomerID, AP.InvoiceNo, AP.BillAmt,  (AP.BillAmt - AM.AdvanceAmount) AS DueAmount
FROM AdvancePayment AP
JOIN (  SELECT CustomerID, InvoiceNo, sum(AdvanceAmount) AS AdvanceAmount
        FROM AdvancePayment
        GROUP BY CustomerID, InvoiceNo ) AM
        ON AP.CustomerID = AM.CustomerID
        AND AP.InvoiceNo = AM.InvoiceNo
WHERE AP.BillAmt > AM.AdvanceAmount
Please let me know if it doesn't solve your problem.
Devendra Kumar9y ago
Thank you Anil