CODEWITHSUNDEEP
sqlsql-serverfilter
gberisha
gberisha

Reputation: 301

Filter same Value - Only take One - Without using CTE

I have the following table (which i get after a simple date filter Query)

now i want to Show the SalesAmount for the given Month, though i have Multiple values for the same ID in some cases, in which case, one Needs to be ignored and the other taken, otherwise (if there is only one entry for the ID, that value should be taken) 

| TableID |SalesOpportunityID| OrderID | SalesCycleStatus | Status | SalesAmount | OrderIncMonth |
|    1    |   14551          |  NULL   |     Oppurtunity  |  Lost  |    1556,316 |     2013-10   |
|    2    |   12551          |  14515  |       Order      | Active |   23563,23  |     2013-10   |
|    3    |   12151          |  15131  |       Order      | Active |  2212352,43 |     2013-10   |
|    4    |   14531          |  NULL   |     Opportunity  |  Lost  | 32152332,1  |     2013-10   |

|    5    |   12651          |  NULL   |     Opportunity  |  Won   |   23525,3245|     2013-10   |
|    6    |   12651          |  21452  |       Order      | Active |   23525,3245|     2013-10   |

As you can See, TableID = 5 and = 6 are basicaly the same Order,

just stored as a Sales Opurtunity, than (after it was won) turned into an Order and Stored again, since i am trying to Calculate the Summ of all Oportunities and Orders for the Month being,

i Need to filter out these Double Values, and do so by: Taking only the Value for the SalesOpportunityID where the SalesCycleStatus is = Order

(IF of curse there are 2 entries with the same SalesOpportunityID, Otherwise take SalesAmount for SalesOpportunityID - regardles weather the SalesCycleStatus is an Order or an Opportunity)

Is there a way to do this without using CTE ? thanks a lot for your Help :) !

Upvotes: 0

Views: 86

Answers (2)

GarethD
GarethD

Reputation: 69789

You can also achieve this using ROW_NUNBER() which could be more efficient as it only requires one table scan:

SELECT  TableID, 
        SalesOpportunityID, 
        OrderID, 
        SalesCycleStatus, 
        Status, 
        SalesAmount
FROM (  SELECT  TableID, 
                SalesOpportunityID, 
                OrderID, 
                SalesCycleStatus, 
                Status, 
                SalesAmount ,
                RowNumber = ROW_NUMBER() OVER(PARTITION BY SalesOpportunityID 
                                                ORDER BY CASE WHEN SalesCycleStatus = 'Order' THEN 1 ELSE 0 END, TableID)
        FROM    T
    ) t
WHERE t.RowNumber = 1;

Upvotes: 1

AHiggins
AHiggins

Reputation: 7219

There are a number of ways to do this: the first one I'd suggest would be a subquery using NOT EXISTS as follows:

SELECT TableID, SalesOpportunityID, OrderID, SalesCycleStatus, Status, SalesAmount 
FROM TestTable 
WHERE 
  SalesCycleStatus = 'Order' OR
  NOT EXISTS
    (
      SELECT TableID 
      FROM TestTable sub 
      WHERE 
        SalesCycleStatus = 'Order' AND
        sub.SalesOpportunityID = TestTable.SalesOpportunityID
    )

Click here for the full SQL Fiddle example

Upvotes: 1

Related Questions