This Stored Procedure
Hi all,
I have a table contains the following columns: ID, CustomerName, ProductID,PurchaseDate.
A customer might have purchase the same product in different dates, so I need to query the table to display ID, CutomerName, ProductID, number of times each Product have been bought by the same customer, and the date of last purchase of each product by the customer. This is my stored procedure but it is not return the right resulty.This is my table
ID ProductID CustomerName PurchaseDate
1 1 John Smith 10/3/2000
2 1 John Smith 20/5/2006
3 1 Sam 24/7/2003
4 1 Sam 20/10/2000
5 2 Sam 11/11/1985
4 2 Sarah 26/8/2001
5 2 John Smith 11/11/1998
6 2 John Smith 01/10/1980
and this is how I want to see the query result:
ProductID CustomerName NumberOfPurchaseTime LastPurchaseDate
1 John Smith 2 20/5/2006
2 John Smith 2 11/11/1998
1 Sam 2 24/7/2003
2 Sam 1 11/11/1985
2 Sarah 1 26/8/2001
This is my stored procedure
CREATE PROCEDURE [dbo].[CustomerHistry]
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT MAX (PurchaseDate) As "Latest purchase date:" ,COUNT( ProductID) AS " No. of this item been purchased ", CustomerName
FROM
[ CustomerHistry]
group by CustomerName
GO
I will be grateful if any of you kindly help me to fix this and it is very urgent.
regards rao
|
