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

Question

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

Answer 1

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

Answer 2

summary you will be able to use only once. Alternate solution is store summary into temp table and use that as many times as u want. Something like : Select * into #temp from Summary s where s.rk=1