Solved

SQL Select Max

Posted on 2013-05-26
7
274 Views
Last Modified: 2013-05-26
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
Comment
Question by:W.E.B
  • 4
  • 3
7 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39198369
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
 

Author Comment

by:W.E.B
ID: 39198374
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39198379
I forgot
        , TotalAmount
include under line 16

get rid of existing line 45 completely (please see the edit to my suggestion above)
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 39198381
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
 

Author Comment

by:W.E.B
ID: 39198389
Perfect,
thanks,
0
 

Author Closing Comment

by:W.E.B
ID: 39198390
thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39198400
:) 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question