sql1411 (10/1/2012)
Hi Ray,No. The customerID is different for each customer. Below is the revised INSERT statement
GO
INSERT INTO dbo.CustomerPurchase ( PurchaseDate, CustomerID, CustomerName, PurchaseDetails)
SELECT '2012-06-01 00:00:00.000',1001,'Wilson Menthis','Purchased Item 6AB2'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1001,'Wilson Menthis','Loyalty Customers'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1001,'Wilson Menthis','20%DiscountReceived on Office Furniture'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1002,'John Gray','Purchased $250 worth of Miscellaneous Products'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1003,'Kevin Chang','Loyalty Customers'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1003,'Kevin Chang','Returned OfficeDesk due to defect on the surface'
GO
Then Use the Customer ID on the derived table and join so its more explicit
--Select some data from the table for the particular day
SELECT *
FROM #CustomerPurchase cp
INNER JOIN (SELECT CustomerID
FROM #CustomerPurchase
WHERE PurchaseDate = '2012-06-01 00:00:00.000'
AND PurchaseDetails = 'Loyalty Customers') AS lc
ON cp.CustomerID = lc.CustomerID
-- Remove this if you don't care what date the customers purchases were for, or Add < if you only want purchases before this date
AND cp.PurchaseDate = lc.PurchaseDate