-- 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')