hi expert,
following query retrive about more then 70,000 record but it's too slow.
it's complaince report which show all employees record weither they are active or not.
bold date is input parametere for date.
SELECT
time_empl_id,
sum(time_hours) Actual_Hrs,
time_revm_period revm_date,
--CD
sum(decode(TICH.time_acti_code, 'ACPL', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CLSV', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CSSD', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'COCD', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'RSTP', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'REPL', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'RCSE', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'ONSC', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'TRCH', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'WHPL', time_hours, 0)) CD_TOTAL,
sum(decode(TICH.time_acti_code, 'ACPL', time_hours, 0)) Account_Planning,
sum(decode(TICH.time_acti_code, 'CLSV', time_hours, 0)) Claims_Services,
sum(decode(TICH.time_acti_code, 'CSSD', time_hours, 0)) Clnt_Strat_Stwrdship_Dialogue,
sum(decode(TICH.time_acti_code, 'COCD', time_hours, 0)) Compliance,
sum(decode(TICH.time_acti_code, 'RSTP', time_hours, 0)) Renewal_Strategy_Process,
sum(decode(TICH.time_acti_code, 'REPL', time_hours, 0)) Retail_Placement,
sum(decode(TICH.time_acti_code, 'RCSE', time_hours, 0)) Risk_Consulting_Services,
sum(decode(TICH.time_acti_code, 'ONSC', time_hours, 0)) Ongoing_Service_Commitments,
sum(decode(TICH.time_acti_code, 'PEMA', time_hours, 0)) Pema_id,
sum(decode(TICH.time_acti_code, 'TRCH', time_hours, 0)) Travel,
sum(decode(TICH.time_acti_code, 'WHPL', time_hours, 0)) Wholesale_Placement,
--CP
sum(decode(TICH.time_acti_code, 'ENNC', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CLTT', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CCSP', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CSSC', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'INMR', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'PREX', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'RWRK', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'TRNC', time_hours, 0)) CP_TOTAL,
sum(decode(TICH.time_acti_code, 'ENNC', time_hours, 0)) Entertainment,
sum(decode(TICH.time_acti_code, 'CLTT', time_hours, 0)) Client_Team_Transition,
sum(decode(TICH.time_acti_code, 'CCSP', time_hours, 0)) Compliance,
sum(decode(TICH.time_acti_code, 'CSSC', time_hours, 0)) Cnfrm_Compensation_wth_Client,
sum(decode(TICH.time_acti_code, 'INMR', time_hours, 0)) Insurer_Market_Relationship,
sum(decode(TICH.time_acti_code, 'PREX', time_hours, 0)) Prospecting_and_Expanded_Srvcs,
sum(decode(TICH.time_acti_code, 'RWRK', time_hours, 0)) Rework,
sum(decode(TICH.time_acti_code, 'TRNC', time_hours, 0)) Travel,
--MT
sum(decode(TICH.time_acti_code, 'ADMN', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'CNCS', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'ENMA', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'TRGV', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'TRMA', time_hours, 0)) MT_TOTAL,
sum(decode(TICH.time_acti_code, 'ADMN', time_hours, 0)) Administration,
sum(decode(TICH.time_acti_code, 'CNCS', time_hours, 0)) Compliance,
sum(decode(TICH.time_acti_code, 'ENMA', time_hours, 0)) Entertainment,
sum(decode(TICH.time_acti_code, 'TRGV', time_hours, 0)) Training,
sum(decode(TICH.time_acti_code, 'TRMA', time_hours, 0)) Travel,
--LV
sum(decode(TICH.time_acti_code, 'LVHO', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'FMLA', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'HOLI', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'OTAB', time_hours, 0)) +
sum(decode(TICH.time_acti_code, 'SICK', time_hours, 0)) LV_TOTAL,
sum(decode(TICH.time_acti_code, 'LVHO', time_hours, 0)) Company_Holiday,
sum(decode(TICH.time_acti_code, 'FMLA', time_hours, 0)) Family_and_Medical_Leave_Act,
sum(decode(TICH.time_acti_code, 'HOLI', time_hours, 0)) Holiday,
sum(decode(TICH.time_acti_code, 'OTAB', time_hours, 0)) Other_absence,
sum(decode(TICH.time_acti_code, 'SICK', time_hours, 0)) Sickness
FROM CA_TIME_ENTRIES_v TICH
WHERE time_revm_date = to_date('29-apr-2007')
group by time_empl_id,
time_revm_date,
time_revm_period,
time_clie_no
union
SELECT
empl_id,
0,
to_number(to_char(sysdate,'YYYYMM')),
--CD
0,
0 Account_Planning,
0 Claims_Services,
0 Clnt_Strat_Stwrdship_Dialogue,
0 Compliance,
0 Renewal_Strategy_Process,
0 Retail_Placement,
0 Risk_Consulting_Services,
0 Ongoing_Service_Commitments,
0 Pema_id,
0 Travel,
0 Wholesale_Placement,
--CP
0 CP_TOTAL,
0 Entertainment,
0 Client_Team_Transition,
0 Compliance,
0 Cnfrm_Compensation_wth_Client,
0 Insurer_Market_Relationship,
0 Prospecting_and_Expanded_Srvcs,
0 Rework,
0 Travel,
--MT
0 MT_TOTAL,
0 Administration,
0 Compliance,
0 Entertainment,
0 Training,
0 Travel,
--LV
0 LV_TOTAL,
0 Company_Holiday,
0 Family_and_Medical_Leave_Act,
0 Holiday,
0 Other_absence,
0 Sickness
FROM ca_employees
WHERE not exists -- here all employee except those not in bold date
(select 'x'
from te_per_time_entries
where time_revm_date = to_date('29-apr-2007')
and time_empl_number = empl_no);
please advice....
i tried my best but still it's taking about 45 min.....
i run above queries saperatly and it's work fine but when i put union then it become bad...
Thanks in advance.
|
] 
Blog
Search
Help
Members
Register
Login
Home
