September 22, 2014 at 1:23 pm
Hi Guys,
I have the following query:
;WITH CTE_LastOrder (CustomerID, LastOrderDate) As
(
SELECT CustomerID, MAX(PaymentDate) LastOrderDate
FROM [Order]
GROUP By CustomerId
)
SELECT * from dbo.Customer C
JOIN CTE_LastOrder LO ON C.CustomerId = LO.CustomerId
WHERE LO.LastOrderDate > (dateAdd(YEAR,-1, GetDate()))
That should select all customers from the customers table where they haven't had a Paid Order (ascertained via PaymentDate not being null) in the last 12 months - this could also include customers who don't appear in the Order table at all.
The query is giving me results but shows a LastOrderDate for date ranges in 2014..
Any further info needed, just give me a shout.
Thanks in advanced as always.
Michael
September 22, 2014 at 1:31 pm
michaeleaton 36224 (9/22/2014)
Hi Guys,I have the following query:
;WITH CTE_LastOrder (CustomerID, LastOrderDate) As(
SELECT CustomerID, MAX(PaymentDate) LastOrderDate
FROM [Order]
GROUP By CustomerId
)
SELECT * from dbo.Customer C
JOIN CTE_LastOrder LO ON C.CustomerId = LO.CustomerId
WHERE LO.LastOrderDate > (dateAdd(YEAR,-1, GetDate()))
That should select all customers from the customers table where they haven't had a Paid Order (ascertained via PaymentDate not being null) in the last 12 months - this could also include customers who don't appear in the Order table at all.
The query is giving me results but shows a LastOrderDate for date ranges in 2014..
Any further info needed, just give me a shout.
Thanks in advanced as always.
Michael
What you might want to do is find who has paid then left join on that. Your criteria should be on the order table (cte)
September 22, 2014 at 1:48 pm
In the first part you get the last payment date for each customer, from orders table:
;WITH CTE_LastOrder (CustomerID, LastOrderDate) As
(
SELECT CustomerID, MAX(PaymentDate) LastOrderDate
FROM [Order]
GROUP By CustomerId
)
This looks OK to me.
But bellow:
SELECT * from dbo.Customer C
JOIN CTE_LastOrder LO ON C.CustomerId = LO.CustomerId
WHERE LO.LastOrderDate > (dateAdd(YEAR,-1, GetDate()))
there is an inner join of all the customers with the results of the first part. This will get the details only for the customers which are included in the first select, which have an order , I mean the customers who have not ordered yet are not included. Even so the filter in the where clause is meant to return the Customers having the last order paid recently, this year, and not to exclude them.
So what I would do is:
1. move the filter in the CTE so that the CTE will return less records and we get a little bit of optimization.
2. remove the join in the second select
3. change the filter of the second select
;WITH CTE_LastOrder (CustomerID, LastOrderDate) As
(
SELECT CustomerID, MAX(LastOrderDate ) LastOrderDate
FROM [Order]
GROUP By CustomerId
HAVING MAX(LastOrderDate ) > (dateAdd(YEAR,-1, GetDate())) -- (1)
)
SELECT * from dbo.Customer C
-- (2) JOIN CTE_LastOrder LO ON C.CustomerId = LO.CustomerId
WHERE -- LO.LastOrderDate > (dateAdd(YEAR,-1, GetDate()))
-- (3)
C.CustomerId NOT IN ( SELECT CustomerID FROM CTE_LastOrder )
I hope this helps
Cheers ,
Iulian
September 22, 2014 at 2:28 pm
Thanks djj.
Also, Martin, thank you very much - that seems to have worked - the only thing that is missing is the LastOrderDate in the result set as I want to see when their last order was.. I don't want to touch the query too much as I don't want to break it! I did try adding an INNER JOIN to the select at the bottom of the query but i got duplicated results - so unsure where to do it..
Query:
;WITH CTE_LastOrder (CustomerID, LastOrderDate) As
(
SELECT CustomerID, MAX(PaymentDate ) LastOrderDate
FROM [Order]
GROUP By CustomerId
HAVING MAX(PaymentDate ) > (dateAdd(YEAR,-1, GetDate())) -- (1)
)
SELECT * from dbo.Customer C
WHERE
C.CustomerId NOT IN ( SELECT CustomerID FROM CTE_LastOrder )
September 22, 2014 at 2:53 pm
michaeleaton 36224 (9/22/2014)
Thanks djj.Also, Martin, thank you very much - that seems to have worked - the only thing that is missing is the LastOrderDate in the result set as I want to see when their last order was.. I don't want to touch the query too much as I don't want to break it! I did try adding an INNER JOIN to the select at the bottom of the query but i got duplicated results - so unsure where to do it..
Query:
;WITH CTE_LastOrder (CustomerID, LastOrderDate) As(
SELECT CustomerID, MAX(PaymentDate ) LastOrderDate
FROM [Order]
GROUP By CustomerId
HAVING MAX(PaymentDate ) > (dateAdd(YEAR,-1, GetDate())) -- (1)
)
SELECT * from dbo.Customer C
WHERE
C.CustomerId NOT IN ( SELECT CustomerID FROM CTE_LastOrder )
In this case is better to leave the CTE not filtered and to make a filter in the second select, like this:
;WITH CTE_LastOrder (CustomerID, LastOrderDate) As
(
SELECT CustomerID, MAX(PaymentDate ) LastOrderDate
FROM [Order]
GROUP By CustomerId
)
SELECT CTE.LastOrderDateas LastOrderDate
, *
from dbo.Customer C LEFT OUTER JOIN CTE_LastOrder AS CTE
ON C.CustomerID = CTE.CustomerID
WHERE
CTE.LastOrderDateIS NULL -- for the customers who never ordered
OR CTE.LastOrderDate <= (dateAdd(YEAR,-1, GetDate()))-- for the customers who have not paid in the last year
Cheers,
Iulian
September 23, 2014 at 10:01 am
I think this is simpler and will do what you need. You might want to tweak "<" to "<=", depending on exactly what you want the cutoff date to be.
SELECT CustomerID, MAX(PaymentDate) LastOrderDate
FROM [Order]
GROUP By CustomerId
HAVING MAX(PaymentDate) < DATEADD(YEAR, -1, GETDATE())
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply