Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Select Max

Posted on 2013-05-26
7
Medium Priority
?
282 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 49

Accepted Solution

by:
PortletPaul earned 1600 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

704 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