W.E.B
asked on
SQL SELECT Max
Hello,
I'm trying to use below code to find the last Invoice number (Highest) per client.
Results I'm getting is all Invoices / per client.
Can you please help.
Select (Select Name From Clients where Clients.AccountNumber = AROpentransactions.Account Number) AS [Client],
AccountNumber, MAX (TransactionDate), TransactionNumber,OrdersSu btotalAmou nt,SalesAm ount,TaxAm ount,ARAmo unt,
(Select Accountlocked From Clients where Clients.AccountNumber = AROpentransactions.Account Number) AS [Accountlocked]
from ARopentransactions
where (Select Accountlocked From Clients where Clients.AccountNumber = AROpentransactions.Account Number) = 1
AND TransactionType = 1
GROUP BY AccountNumber,TransactionN umber,Orde rsSubtotal Amount,Sal esAmount,T axAmount,A RAmount
Thanks,
I'm trying to use below code to find the last Invoice number (Highest) per client.
Results I'm getting is all Invoices / per client.
Can you please help.
Select (Select Name From Clients where Clients.AccountNumber = AROpentransactions.Account
AccountNumber, MAX (TransactionDate), TransactionNumber,OrdersSu
(Select Accountlocked From Clients where Clients.AccountNumber = AROpentransactions.Account
from ARopentransactions
where (Select Accountlocked From Clients where Clients.AccountNumber = AROpentransactions.Account
AND TransactionType = 1
GROUP BY AccountNumber,TransactionN
Thanks,
ASKER
Hello Paul,
I get error
Msg 207, Level 16, State 1, Line 19
Invalid column name 'TransactionDate'.
at this line
) AS Latest ON OT.AccountNumber = Latest.AccountNumber
AND OT.TransactionDate = Latest.TransactionDate
thanks
I get error
Msg 207, Level 16, State 1, Line 19
Invalid column name 'TransactionDate'.
at this line
) AS Latest ON OT.AccountNumber = Latest.AccountNumber
AND OT.TransactionDate = Latest.TransactionDate
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I suspect you may find the derived table is just missing the TransactionDate column (but no points if I am right). In other words, it should be:
SELECT Clients.NAME AS [Client],
OT.AccountNumber,
OT.TransactionDate,
OT.TransactionNumber,
OT.OrdersSubtotalAmount,
OT.SalesAmount,
OT.TaxAmount,
OT.ARAmount,
Clients.Accountlocked
FROM ARopentransactions AS OT
INNER JOIN (SELECT AccountNumber,
TransactionDate,
ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY TransactionDate DESC) AS row_ref
FROM ARopentransactions
WHERE TransactionType = 1
) AS Latest ON OT.AccountNumber = Latest.AccountNumber
AND OT.TransactionDate = Latest.TransactionDate
INNER JOIN Clients ON OT.AccountNumber = Clients.AccountNumber
WHERE Latest.row_ref = 1
Oops, sorry about that. I did not see your last comment.
:) no problem, & you spotted the ommission
ASKER
Paul,
As always,
Thank you.
As always,
Thank you.
ASKER
Thank you
Open in new window