Question On SQL Query Result

crmfghtr

I Have 3 Tables Orders, OrderItems, OrderPymts. For this query, I only need records from the Orders and OrderPymts Tables. The Order Table Contains Fields: OrderId, ClientId, SubTotal, Tax, OrderTotal The OrderPymts Table Contains Fields: PaymentId, OrderId, PaymentDate, Terms, PrevBal, Payment, Balance. I'm trying to query records only on the Last PaymentDate for each Order (To show the Outstanding Balance of Each Order). I tried This:

SELECT Orders.OrderId, Orders.ClientId, Orders.OrderTotal, OrderPymts.Terms, (SELECT MAX(OrderPymts.PaymentDate)) AS PaymentDate, OrderPymts.PrevBal, OrderPymts.Payment, OrderPymts.Balance
FROM OrderPymts
JOIN Orders ON OrderPymts.OrderId = Orders.OrderId
WHERE (Balance >= 0.01)
ORDER BY Orders.OrderId, PaymentDate

To just get the last payment but it still returns all the payments. Example Result of My query: OrderId |ClientId |OrderTotal|Terms |PaymentDate|PrevBal|Payment|Balance| 1001 | 1 | 500 |Credit| 1/1/2023 | 500 | 100 | 400 | 1002 | 2 | 800 |Cash | 1/9/2023 | 800 | 200 | 600 | 1002 | 2 | 800 |Cash | 1/11/2023 | 600 | 100 | 500 | Above is an example of the data my query is showing. I only want to get the bolded records in the query. But my code above is pulling all the records with a balance greater than 0.01. As you can see, I tried getting the Max date but that didn't work. I also tried Group By but I get the same results as above. I need someone smarter than me, to help me with this. Thanks for your help.

Richard Deeming

Assuming Microsoft SQL Server, ROW_NUMBER is your friend. :)

WITH ctePayments As
(
SELECT
OrderId,
PaymentDate,
Terms,
PrevBal,
Payment,
Balance,
ROW_NUMBER() OVER (PARTITION BY OrderId ORDER BY PaymentDate DESC) As RN
FROM
OrderPymts
)
SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
INNER JOIN ctePayments As P
ON P.OrderId = O.OrderId And P.RN = 1
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
;

ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn[^] Alternatively, you could use a correlated sub-query:

SELECT
O.OrderId,
O.ClientId,
O.OrderTotal,
P.Terms,
P.PaymentDate,
P.PrevBal,
P.Payment,
P.Balance
FROM
Orders As O
CROSS JOIN
(
SELECT TOP 1 Terms, PaymentDate, PrevBal, Payment, Balance
FROM OrderPymts
WHERE OrderPymts.OrderId = O.OrderId
ORDER BY PaymentDate DESC
) As P
WHERE
P.Balance >= 0.01
ORDER BY
O.OrderId
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

crmfghtr

Thank You Richard. I'm coming From VB.Net learning C#, and SQL, Didn't think about a cross Join.

Mycroft Holmes

On a different note - it is probably not a good idea to link payments to an order, or at least make it a secondary connection. A client should have an account, orders place a debit on the account and payments credit the account. You may then want to link 1 or more payments to an order or 1 or more orders to a payment.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

crmfghtr

I needed it to show the last payment made on an invoice. But thanks for your advice.