Devendra Kumar

Devendra Kumar

  • 1.6k
  • 500
  • 133k

Sql query to find the customer having due amount

Jul 23 2017 4:06 PM
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
  1. CREATE TABLE [dbo].[AdvancePayment](  
  2.     [ID] [bigint] IDENTITY(1,1) NOT NULL,  
  3.     [Date] [date] NULL,  
  4.     [CustomerID] [nvarchar](max) NULL,  
  5.     [InvoiceNo] [nvarchar](max) NULL,  
  6.     [BillAmt] [decimal](18, 2) NULL,  
  7.     [AdvanceAmount] [decimal](18, 2) NULL,  
  8.     [Status] [nvarchar](50) NULL,  
  9.     [CreatedBy] [nvarchar](max) NULL,  
  10.  CONSTRAINT [PK_AdvancePayment] PRIMARY KEY CLUSTERED   
  11. (  
  12.     [ID] ASC  
  13. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  14. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  15.   
  16. GO  
 

Answers (2)