Need to find when the customers next vehicle purchase occured in less than 1 yr

[mannefromdetroit]

Example:

Customer Purchase VEHS

C1 20000229 VIN1
C1 20000506 VIN2
C1 20001227 VIN3
C1 20011011 VIN4
C1 20031024 VIN5

Need to determine when the customers next purchase occured in number of years other than the first vehicle in that particular Year.

Need to populate the following counts:

Total P <12 mon 12 -23 24-35 36-47
2000 3 2 1 0 1
2001 1 0 0 0 0
2002 0 0 0 0 0
2003 1 0 0 0 0

Please help me in this.

Thanks in Advance.
Sunny.

[cburnett]

Something like this should do the trick:

create table customer_purchases(customer_id   char(3) not null,
                                purchase_date date    not null,
                                vehicle_id    char(4) not null);
commit;
insert into customer_purchases values('C1', '2000-02-29', 'VIN1');
insert into customer_purchases values('C1', '2000-05-26', 'VIN2');
insert into customer_purchases values('C1', '2000-12-27', 'VIN3');
insert into customer_purchases values('C1', '2001-10-11', 'VIN4');
insert into customer_purchases values('C1', '2003-10-24', 'VIN5');
commit;
 
with initial(yr) as (values(year(current date)-10)),
       years(yr) as (select I.yr
                       from INITIAL I
                     union all
                     select Y.yr+1
                       from YEARS Y
                      where Y.yr < year(current date)),
      first_purchase(total, p, first_purchase_date) as
                    (select Y.yr                                   as Total,
                            sum(case year(CP.purchase_date)
                                   when Y.yr then 1
                                   else           0
                                end)                               as p,
                            min(case year(CP.purchase_date)
                                   when Y.yr then CP.purchase_date
                                   else           '9999-12-31'
                                end)                               as first_purchase_date 
                       from customer_purchases CP,
                            years               Y
                      group by Y.yr)
select FP.total,
       FP.p,
       sum(case
              when CP.purchase_date - FP.first_purchase_date <=     0 then 0
              when CP.purchase_date - FP.first_purchase_date <= 10000 then 1
              else                                                         0
           end)                                                    as this_year,
       sum(case
              when CP.purchase_date - FP.first_purchase_date <= 10000 then 0
              when CP.purchase_date - FP.first_purchase_date <= 20000 then 1
              else                                                         0
           end)                                                    as next_year,
       sum(case
              when CP.purchase_date - FP.first_purchase_date <= 20000 then 0
              when CP.purchase_date - FP.first_purchase_date <= 30000 then 1
              else                                                         0
           end)                                                    as two_years,
       sum(case
              when CP.purchase_date - FP.first_purchase_date <= 30000 then 0
              when CP.purchase_date - FP.first_purchase_date <= 40000 then 1
              else                                                         0
           end)                                                    as three_years
  from first_purchase     FP,
       customer_purchases CP
 group by FP.total,
          FP.p
 order by 1;
commit;
drop table customer_purchases;
commit;