Hi all,
I have 3 different tables as follows:
EMP_SALARY:
JOBS:
DEPARTMENTS:
I am using below query to generate cumulative sum based on the year, month and department id:
SELECT "JOB" Job,"Month" Mon,"Year" Yr,SUM ("Total") total, "DEPT" Dept
FROM (WITH employee AS (
SELECT emp_id,job_id,TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM')MM,gbfy,dept_id,
CASE
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '01' THEN SUM (GB01)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '02' THEN SUM (GB01) + SUM (GB02)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '03' THEN SUM (GB01) + SUM (GB02) + SUM (GB03)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '04' THEN SUM (GB01) + SUM (GB02) + SUM (GB03) + SUM (GB04)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '05' THEN SUM (GB01) + SUM (GB02) + SUM (GB03) + SUM (GB04) + SUM (GB05)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '06' THEN SUM (GB01) + SUM (GB02) + SUM (GB03) + SUM (GB04) + SUM (GB05) + SUM (GB06)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '07' THEN SUM (GB01) + SUM (GB02) + SUM (GB03) + SUM (GB04) + SUM (GB05) + SUM (GB06) + SUM (GB07)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '08' THEN SUM (GB01) + SUM (GB02) + SUM (GB03) + SUM (GB04) + SUM (GB05) + SUM (GB06) + SUM (GB07) + SUM (GB08)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '09' THEN SUM (GB01) + SUM (GB02) + SUM (GB03) + SUM (GB04) + SUM (GB05) + SUM (GB06) + SUM (GB07) + SUM (GB08) + SUM (GB09)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '10' THEN SUM (GB01) + SUM (GB02) + SUM (GB03) + SUM (GB04) + SUM (GB05) + SUM (GB06) + SUM (GB07) + SUM (GB08) + SUM (GB09) + SUM (GB10)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '11' THEN SUM (GB01) + SUM (GB02) + SUM (GB03) + SUM (GB04) + SUM (GB05) + SUM (GB06) + SUM (GB07) + SUM (GB08) + SUM (GB09) + SUM (GB10) + SUM (GB11)
WHEN TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'), 'MM') = '12' THEN SUM (GB01) + SUM (GB02) + SUM (GB03) + SUM (GB04) + SUM (GB05) + SUM (GB06) + SUM (GB07) + SUM (GB08) + SUM (GB09) + SUM (GB10) + SUM (GB11) + SUM (GB12)
END EMP_SUM
FROM emp_salary
WHERE GBFY =TO_NUMBER (TO_CHAR (TO_DATE (TRUNC (SYSDATE), 'DD/MM/YY'),'YY')) AND DEPT_ID = 100
GROUP BY emp_id,job_id,gbfy,dept_id)
SELECT a.emp_id,b.job_title "JOB",a.mm "Month",a.gbfy "Year",a.emp_sum "Total",c.department_name "DEPT"
FROM employee a
JOIN JOBS b ON a.job_id = b.job_id
JOIN departments c ON a.dept_id = c.department_id
GROUP BY a.emp_id,b.job_title,a.mm,a.gbfy,a.emp_sum, c.department_name)
GROUP BY "JOB","Month","Year", "DEPT"
I am getting the output as follows:
Is there a better way to optimize the query and generate the cumulative sum. Because when i tried to run the same query in production environment where we have almost 3 to 4 lakh records it took me almost 1:30 sec to generate the data.
Kindly need your help