Rank Partition?

Question

I have a table which holds customer information with an Account state (working or Deactivated) for every single day.

I'm trying to reduce this information by showing start and end dates for the changes. So if a customer was Working for a 10 Months, deactivated for a Month and then Working for another Month I would have three rows of data instead of 365.

I have tried the following SQL:

SELECT   ACTUAL_DAY, ACCT_STAT,
    RANK() OVER (PARTITION BY ACCT_STAT ORDER BY ACTUAL_DAY ASC) AS Ranking
FROM     TABLE_A
WHERE    CUSTOMER = '123456'
AND      ACTUAL_DAY >= '19-Mar-2012'
GROUP BY ACTUAL_DAY, ACCT_STAT
ORDER BY ACTUAL_DAY;

The results I get are:

ACTUAL_DAY|ACCT_STAT|RANKING
============================
19/03/2012|W|1
21/03/2012|W|2
22/03/2012|W|3
23/03/2012|W|4
24/03/2012|D|1
25/03/2012|D|2
26/03/2012|D|3
27/03/2012|D|4
28/03/2012|W|5
29/03/2012|W|6

However I would like it to look like this so that I can then find the MIN and MAX dates fro each Rank to build my table

ACTUAL_DAY|ACCT_STAT|RANKING
============================
19/03/2012|W|1
21/03/2012|W|1    
22/03/2012|W|1
23/03/2012|W|1
24/03/2012|D|2
25/03/2012|D|2
26/03/2012|D|2
27/03/2012|D|2
28/03/2012|W|3
29/03/2012|W|3

I'm hoping there is a simple way to do this or a completely different way

Thanks

Answer 1

One way is to nest analytics by using lag to set a flag on each change of state and then sum them up in a second step (you don't need to specify a windowing clause because "If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.")

select actual_day, acct_stat, sum(counter) over (order by actual_day) as rnk
from ( select actual_day, acct_stat, 
       case when acct_stat<>(lag(acct_stat) over (order by actual_day)) then 1 else 0 end
         as counter
       from foo );