Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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.TransactionDate 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.TransactionDate 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,
0
W.E.B
Asked:
W.E.B
  • 4
  • 3
1 Solution
 
PortletPaulCommented:
Please try this
SELECT
      Clients.AccountNumber
    , Clients.AccountCode
    , Latest.LastInvoiceNumber
    , Latest.SubTotalAmount
    , Latest.TaxAmount
    , Latest.TotalAmount
    , Latest.TransactionDate AS [LastInvoiceDate]
    , Clients.Accountlocked
FROM (
        SELECT
          AccountNumber
        , TransactionDate
        , LastInvoiceNumber
        , SubTotalAmount
        , TaxAmount
        , row_number() OVER (
              PARTITION BY AccountNumber ORDER BY TransactionDate DESC
              ) AS row_ref
        FROM (

                SELECT AccountNumber
                     , TransactionDate
                     , TransactionNumber AS [LastInvoiceNumber]
                     , SalesAmount AS [SubTotalAmount]
                     , TaxAmount AS [TaxAmount]
                     , ARAmount AS [TotalAmount]
                FROM ARopentransactions
                WHERE TransactionType = 1

                UNION ALL

                SELECT AccountNumber
                     , TransactionDate
                     , TransactionNumber AS [LastInvoiceNumber]
                     , SalesAmount AS [SubTotalAmount]
                     , TaxAmount AS [TaxAmount]
                     , ARAmount AS [TotalAmount]
                FROM PurgedARTransactions
                WHERE TransactionType = 1
             ) AS Trans
        ) AS Latest
INNER JOIN Clients ON Latest.AccountNumber = Clients.AccountNumber
WHERE Latest.row_ref = 1
-- AND Clients.TransactionType = 1 -- probably not required

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)
0
 
W.E.BAuthor Commented:
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'.
0
 
PortletPaulCommented:
I forgot
        , TotalAmount
include under line 16

get rid of existing line 45 completely (please see the edit to my suggestion above)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
PortletPaulCommented:
to be honest, I would expect you to recognize if the following is valid or not

AND Clients.TransactionType = 1

if you don't include table.field or alias.field in your code we have to guess
0
 
W.E.BAuthor Commented:
Perfect,
thanks,
0
 
W.E.BAuthor Commented:
thanks
0
 
PortletPaulCommented:
:) 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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now