Solved

SQL Select Max

Posted on 2013-05-26
7
277 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 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 59
Stored Proc - Rewrite 42 79
Negative isnull? 3 33
Getting local user timezone in Sql Server 5 40
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

710 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