February 16, 2012 at 3:06 am
This code works perfectly,
SELECT distinct Customer_Number, SUM(Price) As Total
FROM Customer_Offer x1
WHERE
EXISTS
(SELECT * FROM Customer_Offer x2 WHERE x2.Customer_Number = x1.Customer_Number
AND x2.Product_Offer = 'prod1')
AND NOT EXISTS
(SELECT * FROM Customer_Offer x3 WHERE x3.Customer_Number = x1.Customer_Number AND
x3.Product_Offer IN ( 'prod2','prod3', 'prod4') )
GROUP BY Customer_Number
ORDER BY Customer_Number;
however I would like to find customers who have bought prod1 and prod2 and not bought prod3 and prod4, I tried using this code
SELECT distinct Customer_Number, SUM(price) As Total
FROM Customer_Offer x1
WHERE
EXISTS
(SELECT * FROM Customer_Offer x2 WHERE x2.customer_number = x1.customer_number
AND x2.product_offer IN ('prod1', prod2)
AND NOT EXISTS
(SELECT * FROM Customer_Offer x3 WHERE x3.customer_number = x1.customer_number AND
x3.product_offer IN ( 'prod3','prod4') )
GROUP BY Customer_number
ORDER BY Customer_Number;
but get a syntax error! is anyone able to point me in the right direction for what I am trying to achieve?
I have also tried using an additional EXISTS like this SELECT distinct Customer_Number, SUM(Price) AS Customer_Value
FROM Customer_Offer x1
WHERE
EXISTS
(SELECT * FROM Customer_Offer x2 WHERE x2.Customer_Number = x1.Customer_Number
AND x2.Product_Offer = 'prod1')
AND EXISTS
(SELECT * FROM Customer_Offer x2 WHERE x2.Customer_Number = x1.Customer_Number
AND x2.Product_Offer = 'prod2')
AND NOT EXISTS
(SELECT * FROM Customer_Offer x3 WHERE x3.Customer_Number = x1.Customer_Number AND
x3.Product_Offer IN ( 'prod3', 'prod4') )
GROUP BY Customer_Number
ORDER BY Customer_Number;
This also doesnt work, the syntax seems correct as it returns no columns so leaving it to be a problem with my logic!
February 16, 2012 at 3:13 am
soulchyld21 (2/16/2012)
however I would like to find customers who have bought prod1 and prod2 and not bought prod3 and prod4, I tried using this code
SELECT distinct Customer_Number, SUM(price) As TotalFROM Customer_Offer x1
WHERE
EXISTS
(SELECT * FROM Customer_Offer x2 WHERE x2.customer_number = x1.customer_number
AND x2.product_offer IN ('prod1', prod2)
AND NOT EXISTS
(SELECT * FROM Customer_Offer x3 WHERE x3.customer_number = x1.customer_number AND
x3.product_offer IN ( 'prod3','prod4') )
GROUP BY Customer_number
ORDER BY Customer_Number;
but get a syntax error! is anyone able to point me in the right direction for what I am trying to achieve?
Your syntax error is "prod2". Try
SELECT distinct Customer_Number, SUM(price) As Total
FROM Customer_Offer x1
WHERE
EXISTS
(SELECT * FROM Customer_Offer x2 WHERE x2.customer_number = x1.customer_number
AND x2.product_offer IN ('prod1', 'prod2'))
AND NOT EXISTS
(SELECT * FROM Customer_Offer x3 WHERE x3.customer_number = x1.customer_number AND
x3.product_offer IN ( 'prod3','prod4') )
GROUP BY Customer_number
ORDER BY Customer_Number;
February 16, 2012 at 3:27 am
Something like this should also work:
; With CustomerSelection AS
(
SELECT
Customer_Number,
SUM (CASE WHEN Product_Offer = 'prod1' THEN 1 ELSE 0 END) AS Prod1
SUM (CASE WHEN Product_Offer = 'prod2' THEN 1 ELSE 0 END) AS Prod2
SUM (CASE WHEN Product_Offer = 'prod3' THEN 1 ELSE 0 END) AS Prod3
SUM (CASE WHEN Product_Offer = 'prod4' THEN 1 ELSE 0 END) AS Prod4
FROM
Customer_Offer
GROUP BY Customer_Number
)
SELECT
Customer_Number
SUM(Price) AS Customer_Value
FROM
Customer_Offer
LEFT JOIN CustomerSelection on Customer_Offer.Customer_Number=CustomerSelection.Customer_Number
WHERE
CustomerSelection.Prod1 > 0
AND CustomerSelection.Prod2 > 0
AND CustomerSelection.Prod3 = 0
AND CustomerSelection.Prod4 = 0
February 16, 2012 at 5:23 am
I seem to have overcome my initial issue but come up to another dead end (so to say) and cant make out what your query is doing exactly, I am trying to write a query that will give me customers who have bought product1 and product2 but not bought product3 and product4, in my table, Customer_offer I have records of which customer has bought a certain product obviously some customers have bought more than one product, I seem to be having trouble with the fact that Im getting customers who have bought one product of the two ('BACNZCT1','BAC1PTS') and not both using this query, could anyone help me to understand where Im getting it wrong and how I can fix it! Customers who have bought BACNZCT1 have to have bought BAC1PTS (Because BACNZCT1 is an add on) so how can i structure my query to not give me those who have bought BAC1PTS ONLY but both!
SELECT DISTINCT Customer_Number, SUM(Price) As Total_Value FROM Customer_Offer x1
WHERE
EXISTS (SELECT * FROM Customer_Offer x2
WHERE
EXISTS(SELECT * FROM Customer_Offer WHERE x2.Customer_Number = x1.Customer_Number AND x2.Product_Offer IN('BACNZCT1','BAC1PTS')))
AND NOT EXISTS (SELECT * FROM Customer_Offer x3 WHERE x3.Customer_Number = x1.Customer_Number AND x3.Product_Offer IN ( 'BACNLCT1', 'BACGSCT1'))
GROUP BY Customer_Number
ORDER BY Customer_Number
February 16, 2012 at 6:07 am
This was removed by the editor as SPAM
February 16, 2012 at 9:38 am
The first section (the CTE) is to determine if a customer has ever bought a certain item:
SELECT
Customer_Number,
SUM (CASE WHEN Product_Offer = 'prod1' THEN 1 ELSE 0 END) AS Prod1
SUM (CASE WHEN Product_Offer = 'prod2' THEN 1 ELSE 0 END) AS Prod2
SUM (CASE WHEN Product_Offer = 'prod3' THEN 1 ELSE 0 END) AS Prod3
SUM (CASE WHEN Product_Offer = 'prod4' THEN 1 ELSE 0 END) AS Prod4
FROM
Customer_Offer
GROUP BY Customer_Number
You can use the value of the columns: Prod1,Prod2,Prod3,Prod4 to determine if the customer has bought the item.
If the customer has purchased prod1 10 times (10 lines in Customer_Offer) the value of column PROD1= 10,
If the customer has purchased prod2 5 times (5 lines in Customer_Offer) the value of column PROD2= 5,
If the customer has purchased prod3 0 times (0 lines in Customer_Offer) the value of column PROD2= 0,
So if the value of a certain column =0 then the combination of the product/customer does not exist. With a where clause you can then filter the customers based on that value: if you want to get only the customers who have bought Prod4 and nothing else:
Where PROD1=0 AND PROD2=0 AND PROD3=0 AND PROD4>0
if you want to get only the customers who have bought Prod2 and prod3 and not prod1:
Where PROD2>0 AND PROD3>0 AND PROD1=0
if you want to get the customers who have bought at least Prod2
Where prod2>0
February 16, 2012 at 4:27 pm
soulchyld21 (2/16/2012)
I seem to be having trouble with the fact that Im getting customers who have bought one product of the two ('BACNZCT1','BAC1PTS') and not both using this query, could anyone help me to understand where Im getting it wrong...
Absolutely. Read the article at the first link in my signature line below and post some readily consumable test data using the methods from that article. That'll give everyone some common ground with you to compare notes with and easily discuss possible problems like your having.
Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply