SQLTeam.com | Weblogs | Forums

Using results of WITH

sql2008

#1

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. I keep losing a large amount of the original set of data.

Can anyone help please?


#2

OR if anyone has a better way of doing this than using WITH. I have tried to use MIN to get the first date but it does not work very well and does not pull out all first sales for some reason.


#3

Hi

You could do this

;
WITH abc_cte
AS (
	SELECT *
	FROM #temp
	)
	,def_cte
AS (
	SELECT *
	FROM abc_cte
	WHERE date1 < date2
	)
SELECT *
FROM def_cte

#4

Thank you, will try this and let you know


#5

Would it be easier to just use the MIN(saledate) for the Customer as the starting point? That might be more efficient for SQL too - particularly if you have an index on [saledate]

SELECT	ed.totalamountincvat, ed.saledate, ed.name AS SaleRef, 
	ed.customer, ed.customername, comp.numberofemployees, 
	comp.companyuid
FROM	exportdocument AS ed 
	JOIN
	(
		SELECT	customer,	-- I assume this is a Unique Key
			MIN(saledate) AS MIN_saledate
		FROM	exportdocument AS ed
		WHERE	ed.statecode = 0
		GROUP BY customer
	) AS ed2
		 ON ed2.customer = ed.customer
	INNER JOIN FilteredAccount AS comp 
		ON ed.customer = comp.accountid
-- WHERE (ed.statecode = 0) 	-- No longer required, it constrains the GROUP BY instead
WHERE	    ed.saledate BETWEEN ed2.saledate 
	AND DATEADD(M,12,ed2.saledate)	-- *** THIS IS RISKY!! ***

There might be a smarter way using a CTE and "WITH" but I don't think that RowNumber() OVER() is it.

Watch out for your BETWEEN. Adding 12 months to a date will do that to the millisecond. What happens if you add 12 months to 29-Feb? (I'm not sure!!). Maybe the "12 months" business requirement is not critical, but I always prefer to do

WHERE     MyColumn >= @MyStartPoint
      AND MyColumn <  @MyEndPoint_PlusOne

in particular for dates where the date may include TIME and regular DATETIME datatype does not store precise milliseconds - so you can't compare up to and including "Midnight minus 1 ms", so easier to say "Less than tomorrow" rather than any sort of Less-or-equal, or the end-range of a BETWEEN.


#6

Yeah, well now I look at it my query can also be written as:

;WITH MyCTE
AS
(
	SELECT	customer,	-- I assume this is a Unique Key
		MIN(saledate) AS MIN_saledate
	FROM	exportdocument AS ed
	WHERE	ed.statecode = 0
	GROUP BY customer
)
SELECT	ed.totalamountincvat, ed.saledate, ed.name AS SaleRef, 
	ed.customer, ed.customername, comp.numberofemployees, 
	comp.companyuid
FROM	exportdocument AS ed 
	JOIN MyCTE AS ed2
		 ON ed2.customer = ed.customer
	INNER JOIN FilteredAccount AS comp 
		ON ed.customer = comp.accountid
-- WHERE (ed.statecode = 0) 	-- No longer required, it constrains the GROUP BY instead
WHERE	    ed.saledate BETWEEN ed2.saledate 
	AND DATEADD(M,12,ed2.saledate)	-- *** THIS IS RISKY!! ***

but its is as broad as it is long - so whichever you find more readable. I expect the query plan will be identical for both of them.


#7

Thank you Kristen, I will have a go at this however the last time I tried to use MIN it did not pick up all of the first sales which put me off of using it. Thank you for your help. Will be interesting to see if it works.


#8

Is it possible that any rows have NULL stored in saledate? That would muck out the MIN() operation!! (but it is something which can be worked around)