title: Alternative to ROW_NUMBER() OVER (PARTITION BY x ORDER BY z)
body: <p>There are plenty of questions on here regarding poor performance with <code>ROW_NUMBER() OVER (PARTITION BY XXX ORDER BY YYY)</code> but ive not been able to apply any of the answers to my scenario.</p>
  
  <p>This is either a lack of understanding on my part or my scenario is not suited to this method &amp; should be using a completely different approach.</p>
  
  <p>I'm  dealing with a large amount of data (several GB), the tables break down as follows:</p>
  
  <ul>
  <li><strong>tbl_SynergyCache_PUR_ORD</strong>: 180,046 (Purchase Order Headers (PO's))</li>
  <li><strong>tbl_SynergyCache_PUR_LINE</strong>: 1,746,629 (Purchase Order Lines)</li>
  <li><strong>tbl_SynergyCache_SOH_HEAD</strong>: 2,345,347 (Sales Order Headers (Invoices))</li>
  <li><strong>tbl_SynergyCache_SOL_LINE</strong>: 35,615,535 (Invoice Lines)</li>
  </ul>
  
  <p>My requirement was to fetch 1 row per product (PRO_CODE), with the following values per product:</p>
  
  <ul>
  <li>Product code (PRO_CODE)</li>
  <li>Latest Invoice Date (SOH_DATE_ENTER)</li>
  <li>Latest Invoice No (DOC_NUMBER)</li>
  <li>Latest Invoice Account No (SLS_CODE)</li>
  <li>Latest Invoice Price (SOL_PRI_TRA)</li>
  <li>Latest PO Date (PUR_DATE)</li>
  <li>Latest PO No (DOC_NUMBER)</li>
  <li>Latest PO Account No (PUR_CODE)</li>
  <li>Latest PO Price (PUR_PRI_TRA)</li>
  </ul>
  
  <p>I'm not one to ask for answers before giving it a go myself, so I gave this a go using ROW_NUMBER() but the report is taking over half an hour to run (not an exaggeration) </p>
  
  <p>My tables are very simple:</p>
  
  <pre><code>CREATE TABLE [dbo].[tbl_SynergyCache_PUR_LINE] ( [DEPOT_CODE] [char] (10) NULL, [DOC_ID] [char] (2) NULL, [DOC_NUMBER] [char] (10) NULL, [PRO_CODE] [char] (50) NULL, [PUR_PRI_TRA] [float] NULL )
  CREATE TABLE [dbo].[tbl_SynergyCache_PUR_ORD]  ( [DEPOT_CODE] [char] (10) NULL, [DOC_ID] [char] (2) NULL, [DOC_NUMBER] [char] (10) NULL, [PUR_CODE] [char] (20) NULL, [PUR_DATE] [datetime] NULL )
  CREATE TABLE [dbo].[tbl_SynergyCache_SOH_HEAD] ( [DEPOT_CODE] [char] (10) NULL, [DOC_ID] [char] (2) NULL, [DOC_NUMBER] [char] (10) NULL, [SLS_CODE] [char] (20) NULL, [SOH_DATE_ENTER] [datetime] NULL )
  CREATE TABLE [dbo].[tbl_SynergyCache_SOL_LINE] ( [DEPOT_CODE] [char] (10) NULL, [DOC_ID] [char] (2) NULL, [DOC_NUMBER] [char] (10) NULL, [PRO_CODE] [char] (50) NULL, [SOL_PRI_TRA] [float] NULL )
  </code></pre>
  
  <p>I created clustered indexes to speed up the joins as follows:</p>
  
  <pre><code>CREATE CLUSTERED INDEX [IX_tbl_SynergyCache_PUR_LINE] ON [dbo].[tbl_SynergyCache_PUR_LINE] ( [DEPOT_CODE] ASC, [DOC_ID] ASC, [DOC_NUMBER] ASC )
  CREATE CLUSTERED INDEX [IX_tbl_SynergyCache_PUR_ORD]  ON [dbo].[tbl_SynergyCache_PUR_ORD]  ( [DEPOT_CODE] ASC, [DOC_ID] ASC, [DOC_NUMBER] ASC )
  CREATE CLUSTERED INDEX [IX_tbl_SynergyCache_SOH_HEAD] ON [dbo].[tbl_SynergyCache_SOH_HEAD] ( [DEPOT_CODE] ASC, [DOC_ID] ASC, [DOC_NUMBER] ASC )
  CREATE CLUSTERED INDEX [IX_tbl_SynergyCache_SOL_LINE] ON [dbo].[tbl_SynergyCache_SOL_LINE] ( [DEPOT_CODE] ASC, [DOC_ID] ASC, [DOC_NUMBER] ASC )
  </code></pre>
  
  <p>And this was my first attempt:</p>
  
  <pre><code>WITH    RawSalesData
            AS ( SELECT   Orders.DOC_NUMBER ,
                          Orders.SLS_CODE ,
                          Orders.SOH_DATE_ENTER ,
                          Lines.PRO_CODE ,
                          Lines.SOL_PRI_TRA ,
                          ROW_NUMBER() OVER ( PARTITION BY Lines.PRO_CODE ORDER BY Orders.SOH_DATE_ENTER DESC ) AS SaleRank
                 FROM     tbl_SynergyCache_SOH_HEAD AS Orders WITH ( NOLOCK )
                          INNER JOIN tbl_SynergyCache_SOL_LINE AS Lines WITH ( NOLOCK ) ON Lines.DEPOT_CODE = Orders.DEPOT_CODE
                                                                                           AND Lines.DOC_ID = Orders.DOC_ID
                                                                                           AND Lines.DOC_NUMBER = Orders.DOC_NUMBER
               ),
          RawPurchaseData
            AS ( SELECT   Orders.DOC_NUMBER ,
                          Orders.PUR_CODE ,
                          Orders.PUR_DATE ,
                          Lines.PRO_CODE ,
                          Lines.PUR_PRI_TRA ,
                          ROW_NUMBER() OVER ( PARTITION BY Lines.PRO_CODE ORDER BY Orders.PUR_DATE DESC ) AS PurchaseRank
                 FROM     tbl_SynergyCache_PUR_ORD AS Orders WITH ( NOLOCK )
                          INNER JOIN tbl_SynergyCache_PUR_LINE AS Lines WITH ( NOLOCK ) ON Lines.DEPOT_CODE = Orders.DEPOT_CODE
                                                                                           AND Lines.DOC_ID = Orders.DOC_ID
                                                                                           AND Lines.DOC_NUMBER = Orders.DOC_NUMBER
               ),
          ActiveProducts_All
            AS ( SELECT   PRO_CODE
                 FROM     RawSalesData
                 UNION ALL
                 SELECT   PRO_CODE
                 FROM     RawPurchaseData
               ),
          ActiveProducts
            AS ( SELECT DISTINCT
                          PRO_CODE
                 FROM     ActiveProducts_All
                 WHERE    LTRIM(RTRIM(ISNULL(PRO_CODE, ''))) &lt;&gt; ''
               )
      SELECT  ActiveProducts.PRO_CODE AS ProductCode,
              RawSalesData.SOH_DATE_ENTER AS LastSold,
              RawSalesData.DOC_NUMBER AS LastSold_OrderNo,
              RawSalesData.SLS_CODE AS LastSold_AccountNo,
              RawSalesData.SOL_PRI_TRA AS LastSold_Price,
              RawPurchaseData.PUR_DATE AS LastPurchased,
              RawPurchaseData.DOC_NUMBER AS LastPurchased_PurchaseOrderNo,
              RawPurchaseData.PUR_CODE AS LastPurchased_AccountNo,
              RawPurchaseData.PUR_PRI_TRA AS LastPurchased_Price
      FROM    ActiveProducts
              LEFT OUTER JOIN RawSalesData ON RawSalesData.PRO_CODE = ActiveProducts.PRO_CODE
                                              AND RawSalesData.SaleRank = 1
              LEFT OUTER JOIN RawPurchaseData ON RawPurchaseData.PRO_CODE = ActiveProducts.PRO_CODE
                                                 AND RawPurchaseData.PurchaseRank = 1
      ORDER BY ActiveProducts.PRO_CODE
  </code></pre>
  
  <p>So my question is this: how can I adapt this query to work efficiently? If i'm flogging a dead horse with ROW_NUMBER(), what would the alternative be to meet the requirements?</p>
  
  <p>To give you an idea of scale, whilst there are 40 million rows of source data here, there is only 33,000 unique products so the output from this query is relatively small in comparison to the input.</p>
  
  <p><strong>Edit:</strong> The Execution Plan for my 30+ minute approach looks ok (i.e. no full table scans) but I have the feeling I'm making SQL do more work than necessary:</p>
  
  <pre><code>  |--Parallelism(Gather Streams, ORDER BY:([Union1014] ASC))
         |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Lines].[PRO_CODE])=([Union1014]), RESIDUAL:([Union1014]=[dbo].[tbl_SynergyCache_PUR_LINE].[PRO_CODE] as [Lines].[PRO_CODE]))
              |--Filter(WHERE:([Expr1034]=(1)))
              |    |--Compute Scalar(DEFINE:([Expr1035]=[dbo].[tbl_SynergyCache_PUR_ORD].[DOC_NUMBER] as [Orders].[DOC_NUMBER], [Expr1036]=[dbo].[tbl_SynergyCache_PUR_ORD].[PUR_CODE] as [Orders].[PUR_CODE], [Expr1037]=[dbo].[tbl_SynergyCache_PUR_ORD].[PUR_DATE] as [Orders].[PUR_DATE], [Expr1039]=[dbo].[tbl_SynergyCache_PUR_LINE].[PUR_PRI_TRA] as [Lines].[PUR_PRI_TRA]))
              |         |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([Lines].[PRO_CODE]))
              |              |--Sequence Project(DEFINE:([Expr1034]=row_number))
              |                   |--Segment
              |                        |--Parallelism(Gather Streams, ORDER BY:([Lines].[PRO_CODE] ASC, [Orders].[PUR_DATE] DESC))
              |                             |--Sort(ORDER BY:([Lines].[PRO_CODE] ASC, [Orders].[PUR_DATE] DESC))
              |                                  |--Hash Match(Inner Join, HASH:([Orders].[DEPOT_CODE], [Orders].[DOC_ID], [Orders].[DOC_NUMBER])=([Lines].[DEPOT_CODE], [Lines].[DOC_ID], [Lines].[DOC_NUMBER]), RESIDUAL:([dbo].[tbl_SynergyCache_PUR_LINE].[DEPOT_CODE] as [Lines].[DEPOT_CODE]=[dbo].[tbl_SynergyCache_PUR_ORD].[DEPOT_CODE] as [Orders].[DEPOT_CODE] AND [dbo].[tbl_SynergyCache_PUR_LINE].[DOC_ID] as [Lines].[DOC_ID]=[dbo].[tbl_SynergyCache_PUR_ORD].[DOC_ID] as [Orders].[DOC_ID] AND [dbo].[tbl_SynergyCache_PUR_LINE].[DOC_NUMBER] as [Lines].[DOC_NUMBER]=[dbo].[tbl_SynergyCache_PUR_ORD].[DOC_NUMBER] as [Orders].[DOC_NUMBER]))
              |                                       |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Orders].[DEPOT_CODE], [Orders].[DOC_ID], [Orders].[DOC_NUMBER]))
              |                                       |    |--Clustered Index Scan(OBJECT:([dbo].[tbl_SynergyCache_PUR_ORD].[IX_tbl_SynergyCache_PUR_ORD] AS [Orders]))
              |                                       |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Lines].[DEPOT_CODE], [Lines].[DOC_ID], [Lines].[DOC_NUMBER]))
              |                                            |--Clustered Index Scan(OBJECT:([dbo].[tbl_SynergyCache_PUR_LINE].[IX_tbl_SynergyCache_PUR_LINE] AS [Lines]))
              |--Merge Join(Left Outer Join, MERGE:([Union1014])=([Lines].[PRO_CODE]), RESIDUAL:([Union1014]=[dbo].[tbl_SynergyCache_SOL_LINE].[PRO_CODE] as [Lines].[PRO_CODE]))
                   |--Sort(ORDER BY:([Union1014] ASC))
                   |    |--Hash Match(Aggregate, HASH:([Union1014]), RESIDUAL:([Union1014] = [Union1014]))
                   |         |--Concatenation
                   |              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Lines].[PRO_CODE]))
                   |              |    |--Hash Match(Aggregate, HASH:([Lines].[PRO_CODE]), RESIDUAL:([dbo].[tbl_SynergyCache_SOL_LINE].[PRO_CODE] as [Lines].[PRO_CODE] = [dbo].[tbl_SynergyCache_SOL_LINE].[PRO_CODE] as [Lines].[PRO_CODE]))
                   |              |         |--Hash Match(Inner Join, HASH:([Orders].[DEPOT_CODE], [Orders].[DOC_ID], [Orders].[DOC_NUMBER])=([Lines].[DEPOT_CODE], [Lines].[DOC_ID], [Lines].[DOC_NUMBER]), RESIDUAL:([dbo].[tbl_SynergyCache_SOL_LINE].[DEPOT_CODE] as [Lines].[DEPOT_CODE]=[dbo].[tbl_SynergyCache_SOH_HEAD].[DEPOT_CODE] as [Orders].[DEPOT_CODE] AND [dbo].[tbl_SynergyCache_SOL_LINE].[DOC_ID] as [Lines].[DOC_ID]=[dbo].[tbl_SynergyCache_SOH_HEAD].[DOC_ID] as [Orders].[DOC_ID] AND [dbo].[tbl_SynergyCache_SOL_LINE].[DOC_NUMBER] as [Lines].[DOC_NUMBER]=[dbo].[tbl_SynergyCache_SOH_HEAD].[DOC_NUMBER] as [Orders].[DOC_NUMBER]))
                   |              |              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Orders].[DEPOT_CODE], [Orders].[DOC_ID], [Orders].[DOC_NUMBER]))
                   |              |              |    |--Clustered Index Scan(OBJECT:([dbo].[tbl_SynergyCache_SOH_HEAD].[IX_tbl_SynergyCache_SOH_HEAD] AS [Orders]))
                   |              |              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Lines].[DEPOT_CODE], [Lines].[DOC_ID], [Lines].[DOC_NUMBER]))
                   |              |                   |--Clustered Index Scan(OBJECT:([dbo].[tbl_SynergyCache_SOL_LINE].[IX_tbl_SynergyCache_SOL_LINE] AS [Lines]), WHERE:(ltrim(rtrim(isnull([dbo].[tbl_SynergyCache_SOL_LINE].[PRO_CODE] as [Lines].[PRO_CODE],'                                                  ')))&lt;&gt;''))
                   |              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Lines].[PRO_CODE]))
                   |                   |--Hash Match(Aggregate, HASH:([Lines].[PRO_CODE]), RESIDUAL:([dbo].[tbl_SynergyCache_PUR_LINE].[PRO_CODE] as [Lines].[PRO_CODE] = [dbo].[tbl_SynergyCache_PUR_LINE].[PRO_CODE] as [Lines].[PRO_CODE]))
                   |                        |--Hash Match(Inner Join, HASH:([Orders].[DEPOT_CODE], [Orders].[DOC_ID], [Orders].[DOC_NUMBER])=([Lines].[DEPOT_CODE], [Lines].[DOC_ID], [Lines].[DOC_NUMBER]), RESIDUAL:([dbo].[tbl_SynergyCache_PUR_LINE].[DEPOT_CODE] as [Lines].[DEPOT_CODE]=[dbo].[tbl_SynergyCache_PUR_ORD].[DEPOT_CODE] as [Orders].[DEPOT_CODE] AND [dbo].[tbl_SynergyCache_PUR_LINE].[DOC_ID] as [Lines].[DOC_ID]=[dbo].[tbl_SynergyCache_PUR_ORD].[DOC_ID] as [Orders].[DOC_ID] AND [dbo].[tbl_SynergyCache_PUR_LINE].[DOC_NUMBER] as [Lines].[DOC_NUMBER]=[dbo].[tbl_SynergyCache_PUR_ORD].[DOC_NUMBER] as [Orders].[DOC_NUMBER]))
                   |                             |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Orders].[DEPOT_CODE], [Orders].[DOC_ID], [Orders].[DOC_NUMBER]))
                   |                             |    |--Clustered Index Scan(OBJECT:([dbo].[tbl_SynergyCache_PUR_ORD].[IX_tbl_SynergyCache_PUR_ORD] AS [Orders]))
                   |                             |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Lines].[DEPOT_CODE], [Lines].[DOC_ID], [Lines].[DOC_NUMBER]))
                   |                                  |--Clustered Index Scan(OBJECT:([dbo].[tbl_SynergyCache_PUR_LINE].[IX_tbl_SynergyCache_PUR_LINE] AS [Lines]), WHERE:(ltrim(rtrim(isnull([dbo].[tbl_SynergyCache_PUR_LINE].[PRO_CODE] as [Lines].[PRO_CODE],'                                                  ')))&lt;&gt;''))
                   |--Filter(WHERE:([Expr1021]=(1)))
                        |--Compute Scalar(DEFINE:([Expr1022]=[dbo].[tbl_SynergyCache_SOH_HEAD].[DOC_NUMBER] as [Orders].[DOC_NUMBER], [Expr1023]=[dbo].[tbl_SynergyCache_SOH_HEAD].[SLS_CODE] as [Orders].[SLS_CODE], [Expr1024]=[dbo].[tbl_SynergyCache_SOH_HEAD].[SOH_DATE_ENTER] as [Orders].[SOH_DATE_ENTER], [Expr1026]=[dbo].[tbl_SynergyCache_SOL_LINE].[SOL_PRI_TRA] as [Lines].[SOL_PRI_TRA]))
                             |--Parallelism(Distribute Streams, Hash Partitioning, PARTITION COLUMNS:([Lines].[PRO_CODE]))
                                  |--Sequence Project(DEFINE:([Expr1021]=row_number))
                                       |--Segment
                                            |--Parallelism(Gather Streams, ORDER BY:([Lines].[PRO_CODE] ASC, [Orders].[SOH_DATE_ENTER] DESC))
                                                 |--Sort(ORDER BY:([Lines].[PRO_CODE] ASC, [Orders].[SOH_DATE_ENTER] DESC))
                                                      |--Hash Match(Inner Join, HASH:([Orders].[DEPOT_CODE], [Orders].[DOC_ID], [Orders].[DOC_NUMBER])=([Lines].[DEPOT_CODE], [Lines].[DOC_ID], [Lines].[DOC_NUMBER]), RESIDUAL:([dbo].[tbl_SynergyCache_SOL_LINE].[DEPOT_CODE] as [Lines].[DEPOT_CODE]=[dbo].[tbl_SynergyCache_SOH_HEAD].[DEPOT_CODE] as [Orders].[DEPOT_CODE] AND [dbo].[tbl_SynergyCache_SOL_LINE].[DOC_ID] as [Lines].[DOC_ID]=[dbo].[tbl_SynergyCache_SOH_HEAD].[DOC_ID] as [Orders].[DOC_ID] AND [dbo].[tbl_SynergyCache_SOL_LINE].[DOC_NUMBER] as [Lines].[DOC_NUMBER]=[dbo].[tbl_SynergyCache_SOH_HEAD].[DOC_NUMBER] as [Orders].[DOC_NUMBER]))
                                                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Orders].[DEPOT_CODE], [Orders].[DOC_ID], [Orders].[DOC_NUMBER]))
                                                           |    |--Clustered Index Scan(OBJECT:([dbo].[tbl_SynergyCache_SOH_HEAD].[IX_tbl_SynergyCache_SOH_HEAD] AS [Orders]))
                                                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Lines].[DEPOT_CODE], [Lines].[DOC_ID], [Lines].[DOC_NUMBER]))
                                                                |--Clustered Index Scan(OBJECT:([dbo].[tbl_SynergyCache_SOL_LINE].[IX_tbl_SynergyCache_SOL_LINE] AS [Lines]))
  </code></pre>
  
  <p>SQL Fiddle with 1 PO &amp; 1 Invoice 1 2 PRO_CODES:</p>
  
  <p><a href="http://sqlfiddle.com/#!3/557b6/1/0" rel="nofollow">http://sqlfiddle.com/#!3/557b6/1/0</a></p>
  
  <p><strong>FINAL SOLUTION PROPOSED ON BLAM &amp; BLUE's advice:</strong></p>
  
  <p>I basically broke the CTE down into multiple indexed temp tables, this gave the outeput in 6 minutes as opposed to 30+ minutes.</p>
  
  <pre><code>CREATE TABLE #tbl_RawSalesData
      (
        DOC_NUMBER CHAR(10) ,
        SLS_CODE CHAR(20) ,
        SOH_DATE_ENTER DATETIME ,
        PRO_CODE CHAR(50) PRIMARY KEY ,
        SOL_PRI_TRA FLOAT
      )
  
  INSERT  INTO #tbl_RawSalesData
          ( DOC_NUMBER ,
            SLS_CODE ,
            SOH_DATE_ENTER ,
            PRO_CODE ,
            SOL_PRI_TRA
          )
          SELECT  DOC_NUMBER ,
                  SLS_CODE ,
                  SOH_DATE_ENTER ,
                  PRO_CODE ,
                  SOL_PRI_TRA
          FROM    ( SELECT    Orders.DOC_NUMBER ,
                              Orders.SLS_CODE ,
                              Orders.SOH_DATE_ENTER ,
                              Lines.PRO_CODE ,
                              Lines.SOL_PRI_TRA ,
                              ROW_NUMBER() OVER ( PARTITION BY Lines.PRO_CODE ORDER BY Orders.SOH_DATE_ENTER DESC ) AS SaleRank
                    FROM      tbl_SynergyCache_SOH_HEAD AS Orders WITH ( NOLOCK )
                              JOIN tbl_SynergyCache_SOL_LINE AS Lines WITH ( NOLOCK ) ON Lines.DEPOT_CODE = Orders.DEPOT_CODE
                                                                                         AND Lines.DOC_ID = Orders.DOC_ID
                                                                                         AND Lines.DOC_NUMBER = Orders.DOC_NUMBER
                  ) AS RawSalesData
          WHERE   SaleRank = 1
                  AND LTRIM(RTRIM(ISNULL(PRO_CODE, ''))) &lt;&gt; ''
  
  CREATE TABLE #tbl_RawPurchaseData
      (
        DOC_NUMBER CHAR(10) ,
        PUR_CODE CHAR(20) ,
        PUR_DATE DATETIME ,
        PRO_CODE CHAR(50) PRIMARY KEY ,
        PUR_PRI_TRA FLOAT
      ) INSERT  INTO #tbl_RawPurchaseData
          ( DOC_NUMBER ,
            PUR_CODE ,
            PUR_DATE ,
            PRO_CODE ,
            PUR_PRI_TRA
          )
          SELECT  DOC_NUMBER ,
                  PUR_CODE ,
                  PUR_DATE ,
                  PRO_CODE ,
                  PUR_PRI_TRA
          FROM    ( SELECT    Orders.DOC_NUMBER ,
                              Orders.PUR_CODE ,
                              Orders.PUR_DATE ,
                              Lines.PRO_CODE ,
                              Lines.PUR_PRI_TRA ,
                              ROW_NUMBER() OVER ( PARTITION BY Lines.PRO_CODE ORDER BY Orders.PUR_DATE DESC ) AS PurchaseRank
                    FROM      tbl_SynergyCache_PUR_ORD AS Orders WITH ( NOLOCK )
                              INNER JOIN tbl_SynergyCache_PUR_LINE AS Lines WITH ( NOLOCK ) ON Lines.DEPOT_CODE = Orders.DEPOT_CODE
                                                                                               AND Lines.DOC_ID = Orders.DOC_ID
                                                                                               AND Lines.DOC_NUMBER = Orders.DOC_NUMBER
                  ) AS RawPurchaseData
          WHERE   PurchaseRank = 1
                  AND LTRIM(RTRIM(ISNULL(PRO_CODE, ''))) &lt;&gt; ''
  
  CREATE TABLE #tbl_ActiveProducts
      (
        PRO_CODE CHAR(50) PRIMARY KEY
      ) INSERT  INTO #tbl_ActiveProducts
          ( PRO_CODE
          )
          SELECT  PRO_CODE
          FROM    #tbl_RawSalesData
          UNION
          SELECT  PRO_CODE
          FROM    #tbl_RawPurchaseData
  
  SELECT  A.PRO_CODE ,
          S.SOH_DATE_ENTER ,
          S.DOC_NUMBER ,
          S.SLS_CODE ,
          S.SOL_PRI_TRA ,
          P.PUR_DATE ,
          P.DOC_NUMBER ,
          P.PUR_CODE ,
          P.PUR_PRI_TRA FROM    #tbl_ActiveProducts AS A
          LEFT OUTER JOIN #tbl_RawPurchaseData AS P ON P.PRO_CODE = A.PRO_CODE
          LEFT OUTER JOIN #tbl_RawSalesData AS S ON S.PRO_CODE = A.PRO_CODE
  
  DROP TABLE #tbl_ActiveProducts DROP TABLE #tbl_RawPurchaseData DROP TABLE #tbl_RawSalesData
  </code></pre>