SQL Query to find Customer on base of product purchase

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

Latest Posts