sql-serversql-server-2008with-clause

Using the results of WITH clause IN where STATEMENT of main query


I am relatively new at SQL so I apologise if this is obvious but I cannot work out how to use the results of the WITH clause query in the where statement of my main query. My with query pulls the first record for each customer and gives the sale date for that record:

WITH summary AS(
SELECT ed2.customer,ed2.saledate,
ROW_NUMBER()OVER(PARTITION BY ed2.customer
ORDER BY ed2.saledate)AS rk
FROM Filteredxportdocument ed2)
SELECT s.*
FROM summary s
WHERE s.rk=1

I need to use the date in the above query as the starting point and pull all records for each customer for their first 12 months i.e. where the sale date is between ed2.saledate AND ed2.saledate+12 months. My main query is:

SELECT  ed.totalamountincvat, ed.saledate, ed.name AS SaleRef, 
ed.customer, ed.customername, comp.numberofemployees, 
comp.companyuid
FROM exportdocument AS ed INNER JOIN
FilteredAccount AS comp ON ed.customer = comp.accountid
WHERE (ed.statecode = 0)  AND
ed.saledate BETWEEN ed2.saledate AND DATEADD(M,12,ed2.saledate)

I am sure that I need to add the main query into the WITH clause but I cant work out where. Is anyone able to help please


Solution

  • Does this help?

    ;WITH summary AS(
    SELECT ed2.customer,ed2.saledate,
    ROW_NUMBER()OVER(PARTITION BY ed2.customer
    ORDER BY ed2.saledate)AS rk
    FROM Filteredxportdocument ed2)
    
    SELECT  ed.totalamountincvat, ed.saledate, ed.name AS SaleRef, 
    ed.customer, ed.customername, comp.numberofemployees, 
    comp.companyuid
    FROM exportdocument AS ed INNER JOIN
    FilteredAccount AS comp ON ed.customer = comp.accountid
    OUTER APPLY (SELECT s.* FROM summary s WHERE s.rk=1) ed2
    WHERE ed.statecode = 0  AND
    ed.saledate BETWEEN ed2.saledate AND DATEADD(M,12,ed2.saledate)
    and ed.Customer = ed2.Customer
    

    Results of CTE are not cached or stored, so you can't reuse it.

    EDIT:

    Based upon your requirement that all the records from CTE should be in final result, this is a new query:

    ;WITH summary AS(
    SELECT ed2.customer,ed2.saledate,
    ROW_NUMBER()OVER(PARTITION BY ed2.customer
    ORDER BY ed2.saledate)AS rk
    FROM Filteredxportdocument ed2)
    
    SELECT  
        ed.totalamountincvat, 
        ed.saledate, 
        ed.name AS SaleRef, 
        ed.customer, 
        ed.customername, 
        comp.numberofemployees, 
        comp.companyuid
    FROM 
    summary ed2 
    left join exportdocument ed 
        on ed.Customer = ed2.Customer
        and ed.statecode = 0  
        AND ed.saledate BETWEEN ed2.saledate AND DATEADD(M,12,ed2.saledate)
    INNER JOIN FilteredAccount comp 
        ON ed.customer = comp.accountid
    WHERE 
         s.rk=1