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