Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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 );
share|improve this answer
    
Thanks Jack the lag function works perfectly –  Rob Mar 30 '12 at 10:49
1  
Great, glad to hear it - and welcome to the site, do drop by again with questions or answers - both are very welcome :-) –  Jack Douglas Mar 30 '12 at 15:52
1  
@Rob: if the answer solved your problem, you should accept it. –  a_horse_with_no_name Apr 29 '12 at 16:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.