PLSQL - Percentual por ano

[Guilherme Alcântara 0]

Caros,

 

Estou com uma dúvida que parece ser fácil de resolver, mas tô meio empacado. A query abaixo indica a percentagem de gerentes e de empregados ativos para um determinado cliente para o ano atual. Só que eu preciso de um range entre o ano atual e os 4 anos anteriores. (ex.: 2007 a 2011).

 

Até consegui fazer funcionar, mas tive que fazer um union de 5 queries, só mudando o ano. Não sei se é a melhor abordagem.

 

Alguém tem alguma luz?

 

Espero ter sido claro o suficiente para me ajudarem.

 

De antemão, muito obrigado! :)

Guilherme

 

SELECT to_char(SYSDATE, 'YYYY') as tYear,
      round(SUM(decode(fhd.managerial_ind, 'Y', 1, 0)) / COUNT(fhd.managerial_ind) * 100) || '%' man_percent,
      round(SUM(decode(fhd.managerial_ind, 'N', 1, 0)) / COUNT(fhd.managerial_ind) * 100) || '%' emp_percent
FROM   fact_hr_date fhd

-- Employee
JOIN   dim_employee ee
ON     fhd.employee_key = ee.employee_key
      AND fhd.client_key = ee.client_key

-- Business unit
JOIN   dim_business_unit bu
ON     fhd.business_unit_key = bu.business_unit_key
      AND fhd.client_key = bu.client_key

-- Department
JOIN   dim_department dept
ON     fhd.department_key = dept.department_key
      AND fhd.client_key = dept.client_key

-- EEO Category
JOIN   dim_eeo_category eeo
ON     fhd.eeo_category_key = eeo.eeo_category_key

-- Location 
JOIN   dim_location loc
ON     fhd.location_key = loc.location_key

-- Where clauses 
WHERE  fhd.active_employee_ind = 1
      AND fhd.client_key = '2356'
      AND to_char(fhd.effective_end_date, 'yyyy') > to_char(SYSDATE, 'YYYY')
      AND to_char(fhd.effective_start_date, 'yyyy') <= to_char(SYSDATE, 'YYYY')

[Motta 645]

Faltou o Group by e tratar o Ano, tenta isto :

 

SELECT to_char(fhd.effective_start_date, 'YYYY') as tYear,
      round(SUM(decode(fhd.managerial_ind, 'Y', 1, 0)) / COUNT(fhd.managerial_ind) * 100) || '%' man_percent,
      round(SUM(decode(fhd.managerial_ind, 'N', 1, 0)) / COUNT(fhd.managerial_ind) * 100) || '%' emp_percent
FROM   fact_hr_date fhd

-- Employee
JOIN   dim_employee ee
ON     fhd.employee_key = ee.employee_key
      AND fhd.client_key = ee.client_key

-- Business unit
JOIN   dim_business_unit bu
ON     fhd.business_unit_key = bu.business_unit_key
      AND fhd.client_key = bu.client_key

-- Department
JOIN   dim_department dept
ON     fhd.department_key = dept.department_key
      AND fhd.client_key = dept.client_key

-- EEO Category
JOIN   dim_eeo_category eeo
ON     fhd.eeo_category_key = eeo.eeo_category_key

-- Location 
JOIN   dim_location loc
ON     fhd.location_key = loc.location_key

-- Where clauses 
WHERE  fhd.active_employee_ind = 1
      AND fhd.client_key = '2356'
      AND to_char(fhd.effective_end_date, 'yyyy') >= '2007'
      AND to_char(fhd.effective_start_date, 'yyyy') <= '2011'
GROUP BY to_char(fhd.effective_start_date, 'YYYY')