| SOURCE | ||||
| DEPARTMENT_NAME | ANALYST_OFFICE | ANALYST_REGION | MONTH_YEAR | SUM(CNT) |
| Corporate Ratings | Hong Kong | Asia Pacific | 01/01/2009 00:00:00 | 130 |
| Corporate Ratings | Hong Kong | Asia Pacific | 02/01/2009 00:00:00 | 107 |
| Corporate Ratings | Hong Kong | Asia Pacific | 03/01/2009 00:00:00 | 170 |
| Corporate Ratings | Hong Kong | Asia Pacific | 04/01/2009 00:00:00 | 237 |
| Corporate Ratings | Hong Kong | Asia Pacific | 05/01/2009 00:00:00 | 131 |
| Corporate Ratings | Hong Kong | Asia Pacific | 06/01/2009 00:00:00 | 213 |
| Corporate Ratings | Hong Kong | Asia Pacific | 07/01/2009 00:00:00 | 99 |
| Corporate Ratings | Hong Kong | Asia Pacific | 08/01/2009 00:00:00 | 81 |
| Corporate Ratings | Hong Kong | Asia Pacific | 09/01/2009 00:00:00 | 269 |
| Corporate Ratings | Hong Kong | Asia Pacific | 10/01/2009 00:00:00 | 326 |
| Corporate Ratings | Hong Kong | Asia Pacific | 11/01/2009 00:00:00 | 243 |
| Corporate Ratings | Hong Kong | Asia Pacific | 12/01/2009 00:00:00 | 195 |
| Corporate Ratings | Hong Kong | Asia Pacific | 01/01/2010 00:00:00 | 24 |
| Corporate Ratings | Hong Kong | Asia Pacific | 02/01/2010 00:00:00 | 60 |
| Corporate Ratings | Hong Kong | Asia Pacific | 03/01/2010 00:00:00 | 89 |
| Corporate Ratings | Hong Kong | Asia Pacific | 04/01/2010 00:00:00 | 95 |
| Corporate Ratings | Hong Kong | Asia Pacific | 05/01/2010 00:00:00 | 84 |
| Corporate Ratings | Hong Kong | Asia Pacific | 06/01/2010 00:00:00 | 64 |
| Corporate Ratings | Hong Kong | Asia Pacific | 07/01/2010 00:00:00 | 58 |
| Corporate Ratings | Hong Kong | Asia Pacific | 08/01/2010 00:00:00 | 186 |
| Corporate Ratings | Hong Kong | Asia Pacific | 09/01/2010 00:00:00 | 51 |
| Corporate Ratings | Hong Kong | Asia Pacific | 10/01/2010 00:00:00 | 58 |
| Corporate Ratings | Hong Kong | Asia Pacific | 11/01/2010 00:00:00 | 476 |
| Corporate Ratings | Hong Kong | Asia Pacific | 12/01/2010 00:00:00 | 305 |
| Corporate Ratings | Hong Kong | Asia Pacific | 01/01/2011 00:00:00 | 29 |
| Corporate Ratings | Hong Kong | Asia Pacific | 02/01/2011 00:00:00 | 40 |
| Corporate Ratings | Hong Kong | Asia Pacific | 03/01/2011 00:00:00 | 87 |
| Corporate Ratings | Hong Kong | Asia Pacific | 04/01/2011 00:00:00 | 192 |
| Corporate Ratings | Hong Kong | Asia Pacific | 05/01/2011 00:00:00 | 14 |
| Corporate Ratings | Hong Kong | Asia Pacific | 06/01/2011 00:00:00 | 5 |
| Corporate Ratings | Hong Kong | Asia Pacific | 08/01/2011 00:00:00 | 23 |
| TARGET | |||||
| DEPARTMENT_NAME | ANALYST_OFFICE | ANALYST_REGION | MONTH_YEAR | SUM(CNT) | SUM(ROLLING_12_MONTHS_CNT) |
| Corporate Ratings | Hong Kong | Asia Pacific | 01/01/2009 00:00:00 | 130 | 130 |
| Corporate Ratings | Hong Kong | Asia Pacific | 02/01/2009 00:00:00 | 107 | 237 |
| Corporate Ratings | Hong Kong | Asia Pacific | 03/01/2009 00:00:00 | 170 | 407 |
| Corporate Ratings | Hong Kong | Asia Pacific | 04/01/2009 00:00:00 | 237 | 644 |
| Corporate Ratings | Hong Kong | Asia Pacific | 05/01/2009 00:00:00 | 131 | 775 |
| Corporate Ratings | Hong Kong | Asia Pacific | 06/01/2009 00:00:00 | 213 | 988 |
| Corporate Ratings | Hong Kong | Asia Pacific | 07/01/2009 00:00:00 | 99 | 1,087 |
| Corporate Ratings | Hong Kong | Asia Pacific | 08/01/2009 00:00:00 | 81 | 1,168 |
| Corporate Ratings | Hong Kong | Asia Pacific | 09/01/2009 00:00:00 | 269 | 1,437 |
| Corporate Ratings | Hong Kong | Asia Pacific | 10/01/2009 00:00:00 | 326 | 1,763 |
| Corporate Ratings | Hong Kong | Asia Pacific | 11/01/2009 00:00:00 | 243 | 2,006 |
| Corporate Ratings | Hong Kong | Asia Pacific | 12/01/2009 00:00:00 | 195 | 2,201 |
| Corporate Ratings | Hong Kong | Asia Pacific | 01/01/2010 00:00:00 | 24 | 2,095 |
| Corporate Ratings | Hong Kong | Asia Pacific | 02/01/2010 00:00:00 | 60 | 2,048 |
| Corporate Ratings | Hong Kong | Asia Pacific | 03/01/2010 00:00:00 | 89 | 1,967 |
| Corporate Ratings | Hong Kong | Asia Pacific | 04/01/2010 00:00:00 | 95 | 1,825 |
| Corporate Ratings | Hong Kong | Asia Pacific | 05/01/2010 00:00:00 | 84 | 1,778 |
| Corporate Ratings | Hong Kong | Asia Pacific | 06/01/2010 00:00:00 | 64 | 1,629 |
| Corporate Ratings | Hong Kong | Asia Pacific | 07/01/2010 00:00:00 | 58 | 1,588 |
| Corporate Ratings | Hong Kong | Asia Pacific | 08/01/2010 00:00:00 | 186 | 1,693 |
| Corporate Ratings | Hong Kong | Asia Pacific | 09/01/2010 00:00:00 | 51 | 1,475 |
| Corporate Ratings | Hong Kong | Asia Pacific | 10/01/2010 00:00:00 | 58 | 1,207 |
| Corporate Ratings | Hong Kong | Asia Pacific | 11/01/2010 00:00:00 | 476 | 1,440 |
| Corporate Ratings | Hong Kong | Asia Pacific | 12/01/2010 00:00:00 | 305 | 1,550 |
| Corporate Ratings | Hong Kong | Asia Pacific | 01/01/2011 00:00:00 | 29 | 1,555 |
| Corporate Ratings | Hong Kong | Asia Pacific | 02/01/2011 00:00:00 | 40 | 1,535 |
| Corporate Ratings | Hong Kong | Asia Pacific | 03/01/2011 00:00:00 | 87 | 1,533 |
| Corporate Ratings | Hong Kong | Asia Pacific | 04/01/2011 00:00:00 | 192 | 1,630 |
| Corporate Ratings | Hong Kong | Asia Pacific | 05/01/2011 00:00:00 | 14 | 1,560 |
| Corporate Ratings | Hong Kong | Asia Pacific | 06/01/2011 00:00:00 | 5 | 1,501 |
| Corporate Ratings | Hong Kong | Asia Pacific | 08/01/2011 00:00:00 | 23 | 1,466 |
Solution:
select ETR_RA_AGG_FACT_KEY,department_name,ANALYST_OFFICE,ANALYST_REGION,month_year,sum(cnt),sum(ROLLING_12_MONTHS_CNT)
from (
select ETR_RA_AGG_FACT_KEY,DEPARTMENT_NAME,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION,sum(NO_OF_RATING_ACTIONS) as cnt,
sum(NO_OF_RATING_ACTIONS)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),1) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),2) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),3) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),4) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),5) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),6) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),7) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),8) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),9) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),10) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+
nvl(lag(sum(NO_OF_RATING_ACTIONS),11) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)
as Rolling_12_months_cnt from ETR_RATING_actions_AGG_FACT
group by ETR_RA_AGG_FACT_KEY,DEPARTMENT_NAME,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION
)
where month_year between to_date('01/01/2009','dd/mm/yyyy') AND TRUNC(SYSDATE)
group by ETR_RA_AGG_FACT_KEY,department_name,month_year,ANALYST_OFFICE,ANALYST_REGION
No comments:
Post a Comment