Counting orders and canceled orders only if not occurring in same month.
Posted on 2011-05-03
I am trying to count some orders/canceled orders where I don't want it to count if ordered/canceled during the same month (for example, someone ordered and canceled right away).
Due to some wierdness the way we handle products, orders and cancles can ocurr at the same time (same order#) or at different days or sequences (not same order#) and orders numbers are quite random and not necessarily unique.
I'm having a hard time getting started, I think, because a separate orders/cancels can offset each other.
Here some sample data:
Order# Customer Ordered Canceled Product
1 smith 2/28/2011 Website
2 smith 9/1/2010 2/27/2011 Website
3 jones 1/10/2011 Search
4 paul 2/1/2011 Website
5 paul 10/1/2010 11/1/2010 Website
6 jones 2/1/2011 Website
7 al 1/1/2011 Search
8 sam 3/10/2011 3/15/2011 Search
month/yr Product orders cancels
9/10 website 1 0
10/10 website 1 0
11/10 website 0 1
1/11 search 1 1
2/11 website 2 0
(for 2/11, only 2 orders, jones and paul, no cancels due to smith order/cancel on 28th and 27th)