February 8, 2015 at 6:25 pm
Hi Forum,
I'm having some trouble getting this query right as it is still producing results where Customer are still buying a Product. The issue is I want to find Customers who have recently (Last 2 orders or weeks) stopped ordering a certain Product. I have tried using Row_Number() Over partition by to get the last few Order dates & added the Product IDs as a 'Not In' clause to select Customers who did not order these Products recently. Any suggestions welcomed.
Thanks
February 8, 2015 at 7:43 pm
Please, post DDL and the query that you currently have. That will help all of us in troubleshooting,
Petr
February 8, 2015 at 7:57 pm
OK, here it is;
I'm trying to get the Customers who did NOT order Products '211' & '73' in their last 3 Orders.
I've tried firstly to get all the Customers last 3 Orders & then went for those that didn't include these Product IDs.
Any suggests welcomed.:-)
-----------------------------------------------
SELECT DISTINCT
AB.[CustomerCode], --(INT)
AB.[OrderDate], --(DATE)
AB.CustomerProductID --(INT)
FROM
(
SELECT
AA.[CustomerCode],
AA.[OrderDate],
AA.CustomerProductID,
AA.Rank_ID
FROM (
SELECT DISTINCT
c.[CustomerCode]
,CAST(oa.[OrderDate] AS DATE) AS OrderDate
,cp.CustomerProductID
,ROW_NUMBER() OVER (PARTITION BY c.[CustomerCode] ORDER BY oa.[OrderDate] DESC) AS Rank_ID
FROM customer] c
JOIN order o
ON c.CustomerCode = o.CustomerCode
join order_actual oa
ON o.OrderID = oa.OrderID
join order_product op
on oa.OrderID = op.OrderID
JOIN customer_product cp
on op.CustomerProductID = cp.CustomerProductID
) AA
WHERE AA.Rank_ID IN (1,2,3)
) AB
WHERE AB.CustomerProductID NOT IN (211, 73)
February 9, 2015 at 1:49 am
Please post the create table statements for each table, some sample data (as insert statements) and your expected results.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2015 at 5:51 am
my best guess base don the limited information.
SELECT *
FROM Customer
--nothing in last 3 monts
WHERE CustomerCode NOT IN(SELECT o.CustomerCode
FROM [order] o
INNER JOIN order_actual oa
ON o.OrderID = oa.OrderID
INNER JOIN order_product op
ON oa.OrderID = op.OrderID
WHERE o.OrderDate > Dateadd(mm, -3, Getdate())
AND op.Products IN( '211', '73' ))
--did order it over three months ago.
AND CustomerCode IN(SELECT o.CustomerCode
FROM [order] o
INNER JOIN order_actual oa
ON o.OrderID = oa.OrderID
INNER JOIN order_product op
ON oa.OrderID = op.OrderID
WHERE o.OrderDate < Dateadd(mm, -3, Getdate())
AND op.Products IN( '211', '73' ))
Lowell
February 9, 2015 at 12:40 pm
;WITH cteCustRecentOrders AS (
SELECT
AA.[CustomerCode],
AA.[OrderDate],
AA.CustomerProductID,
AA.Rank_ID
FROM (
SELECT
c.[CustomerCode]
,CAST(oa.[OrderDate] AS DATE) AS OrderDate
,op.CustomerProductID
,ROW_NUMBER() OVER (PARTITION BY c.[CustomerCode] ORDER BY oa.[OrderDate] DESC) AS Rank_ID
FROM customer c
JOIN [order] o
ON c.CustomerCode = o.CustomerCode
join order_actual oa
ON o.OrderID = oa.OrderID
join order_product op
ON oa.OrderID = op.OrderID
) AA
WHERE AA.Rank_ID IN (1,2,3)
)
SELECT DISTINCT
cro1.[CustomerCode], --(INT)
cro1.[OrderDate], --(DATE)
cro1.CustomerProductID --(INT)
FROM cteCustRecentOrders cro1
WHERE
NOT EXISTS(
SELECT 1
FROM cteCustRecentOrders cro2
WHERE
cro2.[CustomerCode] = cro1.[CustomerCode] AND
cro2.ProductID IN (211, 73)
)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 16, 2015 at 12:39 am
Hi,
Apologies for the delay in replying.
Both those solutions were very helpful.
Thank you !!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply