C
cchristensen
Hi - I have a table with customer order history, including the following
fields:
Customer Number
Order Number (could be multiple records for one customer number)
Order Date
Product Ordered
Order Retained (Y/N) - not yet populated
# Days to next order - not yet populated
etc
Am trying to do 2 things:
1. Populate/Update "Order Retained" field. Want to analyze subsequent
orders with the same customer number (where order date is greater than
baseline order date) and update Order Retained field to Y. I know I can do
this by creating a copy of the table and joining on customer number (setting
order date criteria > baseline order date), but wondering if there's a way to
do it without creating a second copy of the table.
2. Populate/Update # of Days to Next Order. For every record where Order
Retained = Y, want to calculate the number of days between baseline order and
the NEXT order (so say the baseline order was 1/01/07 and there are
additional orders on 1/10/07 and 1/15/07, want to calcualte the difference
between 1/01 and 1/10, without considering the 1/15 order).
Any help would be appreciated. Thanks!
fields:
Customer Number
Order Number (could be multiple records for one customer number)
Order Date
Product Ordered
Order Retained (Y/N) - not yet populated
# Days to next order - not yet populated
etc
Am trying to do 2 things:
1. Populate/Update "Order Retained" field. Want to analyze subsequent
orders with the same customer number (where order date is greater than
baseline order date) and update Order Retained field to Y. I know I can do
this by creating a copy of the table and joining on customer number (setting
order date criteria > baseline order date), but wondering if there's a way to
do it without creating a second copy of the table.
2. Populate/Update # of Days to Next Order. For every record where Order
Retained = Y, want to calculate the number of days between baseline order and
the NEXT order (so say the baseline order was 1/01/07 and there are
additional orders on 1/10/07 and 1/15/07, want to calcualte the difference
between 1/01 and 1/10, without considering the 1/15 order).
Any help would be appreciated. Thanks!