Solved

SQL Select Max

Posted on 2013-05-26
7
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 49

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 49

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
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!

 
LVL 49

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 49

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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 video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

631 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