Hi guys,
I have a sales table as below:
| dateid | custid | salesorder | amt |
| 2011-01-12 | A | 1 | 200 |
| 2011-04-13 | A | 2 | 220 |
| 2012-03-23 | A | 3 | 100 |
| 2012-05-18 | A | 4 | 150 |
| 2012-03-03 | B | 1 | 130 |
| 2012-06-08 | C | 1 | 80 |
| 2009-11-20 | D | 1 | 130 |
| 2012-09-17 | D | 2 | 180 |
When I select 2012-01-01<=dateid<=2013-12-31, I want get the following table:
| Status | No. of Client |
| New | 2 |
| Existing | 1 |
| Reactivated | 1 |
Defination:
New:Customers who have first purchase in 2012-01-01<=dateid<=2013-12-31,
Existing:Customer who both purchase in 2012-01-01<=dateid<=2013-12-31 and 2011-01-01<=dateid<=2012-12-31,
Reactivated:Customer who both purchase in 2012-01-01<=dateid<=2013-12-31 and before 2011-01-01.
That means for the 2012-01-01<=dateid<=2013-12-31,
A belongs to 'Existing',
B and C belong to 'New',
D belongs to 'Reactivated'.
Anyone has ideas?
Thanks a lot.
