SQL Query to find Customer on base of product purchase

Posted by Raj.Trivedi under Sql Server on 3/2/2013 | Points: 10 | Views : 1057 | Status : [Member] [MVP] | Replies : 2
Hello Team,

I have 3 tables in a Database.

CustomerDetails
ProductDetails
ProductBooking

create table CustomerDetails
(
CustomerID int identity(1,1)
CustomerName varchar(50)
)

create table ProductDetails
(
ProductID int identity(1,1),
ProductName varchar(50),
ProductPrice decimal(18,2)
)

create table ProductBooking
(
BookingID int identity(1,1),
ProductID int,
CustomerID int,
ProductPrice decimal(18,2),
Quantity int,
TotalPrice decimal(18,2)
)


I want to find the name of those customers who as purchased 5 different products with the product name.

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved



Responses

Posted by: Pandians on: 3/3/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check It Out!
SELECT PB.CustomerID,CD1.CustomerName, 

(
SELECT DISTINCT PD.ProductName + ',' FROM ProductBooking PB1 WITH(NOLOCK) JOIN ProductDetails PD WITH(NOLOCK)
ON (PB1.ProductID = PD.ProductID AND PB1.CustomerID = PB.CustomerID)
FOR XML PATH('')
) [Product Name],
COUNT(DISTINCT PB.ProductID) [Product Count] FROM ProductBooking PB WITH(NOLOCK)
JOIN CustomerDetails CD WITH(NOLOCK) ON (CD.CustomerID = PB.CustomerID)
JOIN CustomerDetails CD1 WITH(NOLOCK) ON (CD.CustomerID = CD1.CustomerID)
GROUP BY PB.CustomerID,CD1.CustomerName
HAVING COUNT(DISTINCT PB.ProductID) >=5


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sahoomanoj27 on: 3/14/2013 [Member] Starter | Points: 25

Up
0
Down
SELECT
PRODDET.ProductName AS ProductName
, CUSTDET.CustomerName AS CustomerName
, COUNT(*) AS [TOTAL PROD PURCHASE]
FROM
ProductBooking AS PROD WITH (NOLOCK)
JOIN CustomerDetails AS CUSTDET WITH(NOLOCK)
ON PROD.CustomerID=CUSTDET.CustomerID
JOIN ProductDetails AS PRODDET WITH(NOLOCK)
ON PROD.ProductID=PRODDET.ProductID

GROUP BY PROD.ProductID,CUSTDET.CustomerID

HAVING COUNT(*) >=5

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response