October 1, 2012 at 2:34 pm
Here is my scenario along with the SQL commands,
USE dbo_test
--Create a sample table
GO
CREATE TABLE [dbo].[CustomerPurchase](
[PurchaseDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[CustomerName] [varchar](25) NULL,
[PurchaseDetails][varchar](500)NULL
) ON [PRIMARY]
GO
--Insert some sample data into the sample table
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',1001,'John Gray','Purchased $250 worth of Miscellaneous Products'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1001,'Kevin Chang','Loyalty Customers'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1001,'Kevin Chang','Returned OfficeDesk due to defect on the surface'
GO
--Select some data from the table for the particular day
SELECT * FROM dbo.CustomerPurchase
WHERE PurchaseDate = '2012-06-01 00:00:00.000'
--Now, from the given records, I want to get only the list of customers who are 'Loyalty Customers' and I want to see all of their purchase details entered for that day.
--From the above example, I don't want to see 'John Gray' on my list. But I want to see all the rows of other two customers, as both of them are 'Loyalty Customoers'
--I want to GROUP BY on PurchaseDate, CustomerID, CustomerName columns.
Thanks in advance.
October 1, 2012 at 2:56 pm
Is customer ID Supposed to be the same across all customers?
CREATE TABLE #CustomerPurchase(
[PurchaseDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[CustomerName] [varchar](25) NULL,
[PurchaseDetails][varchar](500)NULL
) ON [PRIMARY]
GO
--Insert some sample data into the sample table
GO
INSERT INTO #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',1001,'John Gray','Purchased $250 worth of Miscellaneous Products'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1001,'Kevin Chang','Loyalty Customers'
UNION ALL
SELECT '2012-06-01 00:00:00.000',1001,'Kevin Chang','Returned OfficeDesk due to defect on the surface'
GO
--Select some data from the table for the particular day
SELECT *
FROM #CustomerPurchase cp
INNER JOIN (SELECT *
FROM #CustomerPurchase
WHERE PurchaseDate = '2012-06-01 00:00:00.000'
AND PurchaseDetails = 'Loyalty Customers') AS lc
ON cp.CustomerName = lc.CustomerName
-- 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
October 1, 2012 at 3:01 pm
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
October 1, 2012 at 3:18 pm
Is someone a loyalty customer because they have an entry with PurchaseDetails of 'Loyalty Customers' on a particular day, or is that designation inherent to the customer himself/herself?
I think you may have a design issue here but I don't want to jump to conclusions as to what you're really trying to do.
October 1, 2012 at 3:21 pm
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
October 1, 2012 at 5:34 pm
Thats what I needed:-). Thank you.
October 2, 2012 at 5:09 pm
SELECT *
FROM dbo.CustomerPurchase cp
WHERE
cp.PurchaseDate = '20120601 00:00:00.000' AND
EXISTS(
SELECT 1
FROM dbo.CustomerPurchase cp2
WHERE
cp2.CustomerId = cp.CustomerId AND
cp2.PurchaseDate = '20120601 00:00:00.000' AND
cp2.PurchaseDetails = 'Loyalty Customers'
)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2012 at 5:15 pm
CELKO (10/1/2012)
Most of the work in RDBMS (80 to 95%) is done in the DDL; you have never learned how to design data, so your code looks like a loose leaf note book, full of narratives. Sorry you had only one purchase, that you think the customer’s name is an attribute of the purchase and not the customer. We have DATE data types. We have the ANSI/ISO syntax for insertion now, too.Tables have keys and should be normalized; you did neither. In short, this is not a valid schema. Here is a guess at doing it right:
CREATE TABLE Customer_Purchase_Comments
(purchase_order_nbr INTEGER NOT NULL,
comment_date DATE NOT NULL,
PRIMARY KEY (purchase_order_nbr INTEGER NOT NULL,
comment_date),
purchase_comment VARCHAR(500) NOT NULL);
CREATE TABLE Purchase_Orders
(purchase_order_nbr INTEGER NOT NULL PRIMARY KEY,
po_date DATE NOT NULL,
customer_id CHAR(10) NOT NULL
REFERENCES Customers(customer_id),
...));
CREATE TABLE Customers
(customer_id CHAR(10) NOT NULL PRIMARY KEY,
customer_name VARCHAR(35) NOT NULL,
..
customer_type CHAR(3) NOT NULL,
CHECK (customer_type IN ('LOY', ..)),
);
>> --Now, from the given records [sic: rows are not records], I want to get only the list of customers who are 'Loyalty Customers' and I want to see all of their purchase details entered for that day. <<
SELECT POC.purchase_order_nbr, POC.purchase_comment,
C.customer_id, C.customer_name
FROM Customer_Purchase_Comments AS POC,
Customers AS C,
Purchase_Orders AS PO
WHERE C.customer_type = 'LOY'
AND POC.comment_date = '2012-06-01'
AND PO.customer_id = C.customer_id;
untested
Given that a customer's name can change, perhaps it is an attribute of the purchase.
Likewise the address of the purchaser (or an address code that points to an actual address) can change and thus is an attribute of the purchase.
As a final example, the price is 100% an attribute of the purchase, since it can be based on many factors and it can change.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 2, 2012 at 7:10 pm
...
Given that a customer's name can change, perhaps it is an attribute of the purchase.
...
Given that nothing in the Universe is constant and everything can change, I would suggest to have one table CustomerPurchase and make all attributes to be attributes of purchase...
:hehe:
It's right for pointer to customer address and price to be attributes of the purchase. But,
it is wrong for customer name. Even if customer name has changed, you will most likely want to see and report all existing purchases with the new name not he old one, until you want datawarehouse changing dimension.
October 3, 2012 at 8:45 am
Eugene Elutin (10/2/2012)
...
Given that a customer's name can change, perhaps it is an attribute of the purchase.
...
Given that nothing in the Universe is constant and everything can change, I would suggest to have one table CustomerPurchase and make all attributes to be attributes of purchase...
:hehe:
It's right for pointer to customer address and price to be attributes of the purchase. But,
it is wrong for customer name. Even if customer name has changed, you will most likely want to see and report all existing purchases with the new name not he old one, until you want datawarehouse changing dimension.
For, say, medical implants and gun sales, to pick just two, is it legal for you not to know the name you sold to?? Just asking. I worked at a medical implant device maker and the FDA was very, very strict on what records had to be kept.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply