How to Filter Sub Query Results from Main query

Question

I have a query that Checks records Customers transactions vs their last transactions:

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 > @startdate 
AND     t.dtcreated < @enddate
AND     t.transactiontype = 'Cust Save'

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.

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"

Example:

I'm Pulling:

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

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'

I've tried:

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 < t.dtcreated**
            ) AS cp
                ON cp.CustomerID = t.CustomerID
                AND cp.RowNumber = 1
    WHERE   t.dtcreated > @startdate 
    AND     t.dtcreated < @enddate
    AND     t.transactiontype = 'Cust Save'

I want the most recent transaction of type Cust Purchase that happened BEFORE 'Cust Save' transaction for each record found of 'Cust Save' Not: The most Recent transaction for each record found of 'Cust Save' — Shmewnix
Looks like something you could do with outer apply (select top 1 ... from Transactions t2 where ... and t2.dtCreated < t.dtcreated order by t2.dtCreated desc) — James Z

Sam cd Sam cd · Accepted Answer · 2015-02-20T20:03:48

SELECT  t.CustomerID,
        t.ServiceID,
        t.dtCreated,
        MAX(cp.dtCreated) OVER (PARTITION BY t.CustomerID) as cp_dtCreated
        Upgraded = CASE WHEN t.ServiceID = cp.ServiceID THEN 0 ELSE 1 END
FROM    Transactions AS t
        INNER JOIN
        (   SELECT  CustomerID,
                    ServiceID,
                    dtCreated,
          )
            FROM    Transactions
            WHERE   transactiontype = 'Cust Purchase'
        ) AS cp
            ON cp.CustomerID = t.CustomerID
            AND cp.dtCreated < t.dtCreated
WHERE   t.dtcreated > @startdate 
AND     t.dtcreated < @enddate
AND     t.transactiontype = 'Cust Save'

How to Filter Sub Query Results from Main query

1 Answers