How to merge 5 queries in single query in procedure to decrease the execution time -


i want in project. have 1 table called employee_details. has 4 columns:

user_id varchar2(255))  user_name(varchar2(255)) nickname(varchar2(255)) created_on(date) 

now have written 5 separate queries in procedure fetch data users registered/created_on during period sysdate-1, sysdate-30, sysdate-60, sysdate-90 , on sysdate want fetch data in single query, execution time of procedure can decreased. please me, , please let me know if more details required.

so far have written following queries:

1.   select count (distinct mth.user_id)             employee_details mth                             mth.created_on < to_date (todate, 'dd/mm/rrrr')              , mth.created_on >= to_date (fromdate, 'dd/mm/rrrr')                                       - 90; 2.   select count (distinct mth.user_id)             employee_details mth                             mth.created_on < to_date (todate, 'dd/mm/rrrr')              , mth.created_on >= to_date (fromdate, 'dd/mm/rrrr')                                       - 60; 3.   select count (distinct mth.user_id)             employee_details mth                             mth.created_on < to_date (todate, 'dd/mm/rrrr')              , mth.created_on >= to_date (fromdate, 'dd/mm/rrrr')                                       - 30; 4.   select count (distinct mth.user_id)             employee_details mth                             mth.created_on < to_date (todate, 'dd/mm/rrrr')              , mth.created_on >= to_date (fromdate, 'dd/mm/rrrr')                                       - 1; 5.   select count (distinct mth.user_id)             employee_details mth                             mth.created_on < to_date (todate, 'dd/mm/rrrr')+1              , mth.created_on >= to_date (fromdate, 'dd/mm/rrrr'); 

where fromdate , todate going replaced sysdate procedure call.

get no of days count (based on period want i.e. 1, 30, 60 or 90 etc..) parameter procedure , try below:

this example last 3 days created records (today dt. 1st april, 2015)

select * emp_temp created_on >= date_add(curdate(), interval -3 day); 

enter image description here

hope may you...

thanks,

~chandan


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -