March 4, 2009 at 1:34 pm
I have two tables. Generically speaking, the first table has customer_number, item_number. The second has order_number, item_number, customer_number. The first table does not have all the customers and items from the second table.
I need to get a count of customers by order number for each customer in the first table. Meaning, I need to know how many customers and item numbers in the first table came from their first order, or their second order or their third order etc...If there are 15 customers and items, but 10 were ordered on their first visit and five were ordered on their second visit (the five ordered something else on their first visit), I think my results would look like:
customers____________ vist_when_order_was_placed
10___________________1
5____________________2
...but I can't quite get there.
Anyone?
Thanks!
March 4, 2009 at 1:37 pm
Can you post sample data and DDL of your tables 😉
* Noel
March 4, 2009 at 2:26 pm
-- Although I used sequential CUSTOMER_ID's here
-- the unique identifier for each customer is LOCATION_ID + CUSTOMER_ID
-- There can be a CUSTOMER_ID of 1 at each location. ITEM is also
-- unique at each location. There is no order number in #SPECIAL_ORDERS.
-- I need to show that the first 10 customers in #SPECIAL_ORDERS
-- came from their first vist and the last 5 came from their second visit,
-- either by date or order number, I don't know.
CREATE TABLE #ORDERS(
LOCATION_ID INT
,ORDER_NO INT
,ITEM_NO INT
,CUSTOMER_ID INT
,VISIT_DATE DATETIME)
CREATE TABLE #SPECIAL_ORDERS(
LOCATION_ID INT
,ITEM_NO INT
,CUSTOMER_ID INT
,VISIT_DATE DATETIME)
INSERT INTO #ORDERS VALUES(1,1,2,1, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,2,2,2, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,3,2,3, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,4,2,4, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,5,2,5, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,6,2,6, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,1,2,7, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,2,2,8, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,3,2,9, '2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,4,2,10,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,1,1,11,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,2,1,12,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,3,1,13,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,4,1,14,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,5,1,15,'2009-01-01 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,7,1,1, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,8,1,2, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,9,1,3, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,10,1,4, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,11,1,5, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(1,12,1,6, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,5,1,7, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,6,1,8, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,7,1,9, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(2,8,1,10, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,6,2,11, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,7,2,12, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,8,2,13, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,9,2,14, '2009-01-02 00:00:00:000')
INSERT INTO #ORDERS VALUES(3,10,2,15,'2009-01-02 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,1, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,2, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,3, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,4, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,5, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(1,2,6, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(2,2,7, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(2,2,8, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(2,2,9, '2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(2,2,10,'2009-01-01 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(3,2,11, '2009-01-02 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(3,2,12, '2009-01-02 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(3,2,13, '2009-01-02 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(3,2,14, '2009-01-02 00:00:00:000')
INSERT INTO #SPECIAL_ORDERS VALUES(3,2,15, '2009-01-02 00:00:00:000')
March 4, 2009 at 2:54 pm
Hello g8r
I'm not sure if I understood, but for the example result of your first post it might be just:
SELECT Visit_Date, COUNT(*)
FROM #Special_Orders
GROUP BY Visit_Date
Greetings
Flo
March 4, 2009 at 3:50 pm
Thank you Flo. But I think that while that may show me how many items were ordered on each date, it will not show me how many customers in the special_orders table are there from their first visit, and how many are there from their second vist. Thank you for you reply.
March 4, 2009 at 4:05 pm
Okay, next try.
If you want the count of visits of your customers within the special_orders use a aggregated sub-query. (In your sample data every customer/location has exactly two entries within the orders).
SELECT so.CUSTOMER_ID, so.LOCATION_ID, o.visit_count
FROM #SPECIAL_ORDERS so
JOIN (SELECT CUSTOMER_ID, LOCATION_ID, COUNT(*) visit_count
FROM #ORDERS
GROUP BY CUSTOMER_ID, LOCATION_ID) o
ON so.CUSTOMER_ID = o.CUSTOMER_ID AND so.LOCATION_ID = o.LOCATION_ID
Greets
Flo
March 4, 2009 at 6:50 pm
Thank you, that is very close. Unfortunately what I am trying to get to is a summary of customers and items from the Special_Orders table in relation to which trip they made to the location. So I need to see, using this data, is that 10 customers in the special_orders table are in there from items ordered on their first visit and 5 are in there from items ordered on their second visit. In the sample data I created, the first 10 made another order at the location at a later date but are not in special_orders for the second visit but rather the first, nor are the other 5 in there from their first visit, but rather their second. I'm not really concerned about location as it was just a way to join, along with customer. So I need to see something like this:
Visit_number Customers
1 10
2 5
Thanks for helping me out!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply