Query to select outstanding (unpaid) Payments in SQL Server

Hi satabeach,

Refer below query.

SQL

DECLARE @Customer TABLE (CsID INT, CsName VARCHAR(20), Credit Decimal(18,2), IssuedOn Datetime)
INSERT INTO @Customer (CsID, CsName, Credit, IssuedOn) VALUES (1, 'Akhil', '20000', '19-Oct-2019')
INSERT INTO @Customer (CsID, CsName, Credit, IssuedOn) VALUES (2, 'Prajeesh', '15000', '15-Dec-2019')
INSERT INTO @Customer (CsID, CsName, Credit, IssuedOn) VALUES (3, 'Shruthi', '12000', '17-Dec-2019')
INSERT INTO @Customer (CsID, CsName, Credit, IssuedOn) VALUES (4, 'Joseph', '18000', '20-Jan-2020')
  
  
DECLARE @payment TABLE (CsNo INT, PaidAmount Decimal(18,2), Balance Decimal(18,2), PaidDate Datetime)
INSERT INTO @payment (CsNo, PaidAmount, Balance, PaidDate) VALUES (1 , '2000', '18000', '1-Nov-2019')
INSERT INTO @payment (CsNo, PaidAmount, Balance, PaidDate) VALUES (1 , '1000', '17000', '5-Dec-2019')
INSERT INTO @payment (CsNo, PaidAmount, Balance, PaidDate) VALUES (2 , '5000', '10000', '8-Jan-2020')
--INSERT INTO @payment (CsNo, PaidAmount, Balance, PaidDate) VALUES (3 , '2000', '10000', '3-Jan-2020')
INSERT INTO @payment (CsNo, PaidAmount, Balance, PaidDate) VALUES (4 , '2000', '16000', '10-Feb-2020')
--INSERT INTO @payment (CsNo, PaidAmount, Balance, PaidDate) VALUES (3 , '3000', '7000', '6-Feb-2020')
 
SELECT CsID,CsName,Credit FROM @Customer WHERE CsID NOT IN (SELECT DISTINCT CsNo FROM @payment)
UNION
SELECT CsNo,CsName,Balance FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY CsNo ORDER BY Balance ASC) RowNo,CsNo,Balance FROM @payment
WHERE CsNo NOT IN (
SELECT CsNo
FROM @payment p
WHERE PaidDate BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AND DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())+1,0))))t
INNER JOIN @Customer c ON c.CsID = t.CsNo
WHERE t.RowNo = 1

Output

CsNo    CsName      Balance

    1        Akhil          17000.00

    2        Prajeesh    10000.00

    3        Shruthi      12000.00