W.E.B
asked on
SQL Select Max
Hello,
I have Below code,
because I'm using a Union, I get 2 values per Account,
Can you Please help me to get only one value per account (the higher value)
SELECT OT.AccountNumber, AccountCode , OT.TransactionNumber AS [LastInvoiceNumber], OT.SalesAmount AS [SubTotalAmount], OT.TaxAmount AS [TaxAmount], OT.ARAmount AS [TotalAmount],OT.Transacti onDate AS [LastInvoiceDate], 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 and TransactionType = 1
UNION ALL
SELECT OT.AccountNumber, AccountCode, OT.TransactionNumber AS [LastInvoiceNumber], OT.SalesAmount AS [SubTotalAmount], OT.TaxAmount AS [TaxAmount], OT.ARAmount AS [TotalAmount],OT.Transacti onDate AS [LastInvoiceDate], Clients.Accountlocked
FROM PurgedARTransactions as OT
INNER JOIN (SELECT AccountNumber , TransactionDate , row_number() over (partition by AccountNumber order by TransactionDate DESC) as row_ref FROM PurgedARTransactions 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 and TransactionType = 1
Thank you,
I have Below code,
because I'm using a Union, I get 2 values per Account,
Can you Please help me to get only one value per account (the higher value)
SELECT OT.AccountNumber, AccountCode , OT.TransactionNumber AS [LastInvoiceNumber], OT.SalesAmount AS [SubTotalAmount], OT.TaxAmount AS [TaxAmount], OT.ARAmount AS [TotalAmount],OT.Transacti
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 and TransactionType = 1
UNION ALL
SELECT OT.AccountNumber, AccountCode, OT.TransactionNumber AS [LastInvoiceNumber], OT.SalesAmount AS [SubTotalAmount], OT.TaxAmount AS [TaxAmount], OT.ARAmount AS [TotalAmount],OT.Transacti
FROM PurgedARTransactions as OT
INNER JOIN (SELECT AccountNumber , TransactionDate , row_number() over (partition by AccountNumber order by TransactionDate DESC) as row_ref FROM PurgedARTransactions 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 and TransactionType = 1
Thank you,
ASKER
Hello
thanks,
Msg 207, Level 16, State 1, Line 45
Invalid column name 'TransactionType'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'TotalAmount'.
thanks,
Msg 207, Level 16, State 1, Line 45
Invalid column name 'TransactionType'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'TotalAmount'.
I forgot
, TotalAmount
include under line 16
get rid of existing line 45 completely (please see the edit to my suggestion above)
, TotalAmount
include under line 16
get rid of existing line 45 completely (please see the edit to my suggestion above)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect,
thanks,
thanks,
ASKER
thanks
:) and thank you.
just as a point of site etiquette, the comment that actually provides (most of) the solution should be marked as the accepted solution - this is useful for reference by others. Not sure my comment ID: 39198381 really meets that criteria.
Please take the time to include table.field or alias.field in your SQL code - it makes your code more robust and maintainable.
Cheers, Paul
just as a point of site etiquette, the comment that actually provides (most of) the solution should be marked as the accepted solution - this is useful for reference by others. Not sure my comment ID: 39198381 really meets that criteria.
Please take the time to include table.field or alias.field in your SQL code - it makes your code more robust and maintainable.
Cheers, Paul
Open in new window
{+ edit for this note} as you filter the transactions themselves by TransactionType = 1 it is not necessary to do so at line 45 above - unless that field belongs to the clients table (which I doubt)