Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get multiple rows of data based on one condition and grouping Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 2:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:15 AM
Points: 54, Visits: 96
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.
Post #1366764
Posted Monday, October 1, 2012 2:56 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
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
Post #1366779
Posted Monday, October 1, 2012 3:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:15 AM
Points: 54, Visits: 96
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
Post #1366781
Posted Monday, October 1, 2012 3:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 1:49 PM
Points: 1,104, Visits: 1,174
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.


└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
Post #1366789
Posted Monday, October 1, 2012 3:21 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:06 PM
Points: 1,478, Visits: 1,025
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

Post #1366790
Posted Monday, October 1, 2012 5:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 6, 2013 10:15 AM
Points: 54, Visits: 96
Thats what I needed. Thank you.
Post #1366837
Posted Monday, October 1, 2012 10:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:15 PM
Points: 1,945, Visits: 2,861
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


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1366859
Posted Tuesday, October 2, 2012 5:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 1,972, Visits: 2,918
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1367333
Posted Tuesday, October 2, 2012 5:15 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 1,972, Visits: 2,918
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1367335
Posted Tuesday, October 2, 2012 7:10 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

...
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...


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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1367369
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse