title: How to Filter Sub Query Results from Main query
body: <p>I have a query that Checks records Customers transactions vs their last transactions:</p>
  
  <pre><code>SELECT  t.CustomerID,
          t.ServiceID,
          t.dtCreated,
          Upgraded = CASE WHEN t.ServiceID = cp.ServiceID THEN 0 ELSE 1 END
  FROM    Transactions AS t
          INNER JOIN
          (   SELECT  CustomerID,
                      ServiceID,
                      dtCreated,
                      RowNumber = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY dtCreated DESC)
              FROM    Transactions
              WHERE   transactiontype = 'Cust Purchase'
          ) AS cp
              ON cp.CustomerID = t.CustomerID
              AND cp.RowNumber = 1
  WHERE   t.dtcreated &gt; @startdate 
  AND     t.dtcreated &lt; @enddate
  AND     t.transactiontype = 'Cust Save'
  </code></pre>
  
  <p>What I'd like to do, is filter the "Cust Purchase" or the "cp" table to pull all results that occured BEFORE the "Cust Save" transaction type in table t.</p>
  
  <p>Right now, I'm getting results of the LAST transaction the customer had with type "Cust Purchase".  However, I want the last transaction for "Cust Purchase" that happened PRIOR to "Cust Save"</p>
  
  <p>Example:</p>
  
  <p>I'm Pulling:</p>
  
  <pre><code>Customer      Date Cust Save     Date Cust Purchase
  1                 1/2/15             12/15/14
  2                 1/2/15             12/17/14
  3                 1/2/15             1/4/15(most recent)   Before save (12/18/14)
  4                 1/2/15             12/18/14
  5                 1/2/15             12/19/14
  </code></pre>
  
  <p>You'll see Customer 3 had a Purchase of 1/4/15.  I want the last cust purchase prior to the cust save.  So in this case, i would want the cust purchase date of '12/18/14'</p>
  
  <p>I've tried:</p>
  
  <pre><code>SELECT  t.CustomerID,
              t.ServiceID,
              t.dtCreated,
              Upgraded = CASE WHEN t.ServiceID = cp.ServiceID THEN 0 ELSE 1 END
      FROM    Transactions AS t
              INNER JOIN
              (   SELECT  CustomerID,
                          ServiceID,
                          dtCreated,
                          RowNumber = ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY dtCreated DESC)
                  FROM    Transactions
                  WHERE   transactiontype = 'Cust Purchase' **and dtcreated &lt; t.dtcreated**
              ) AS cp
                  ON cp.CustomerID = t.CustomerID
                  AND cp.RowNumber = 1
      WHERE   t.dtcreated &gt; @startdate 
      AND     t.dtcreated &lt; @enddate
      AND     t.transactiontype = 'Cust Save'
  </code></pre>