I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
setting up the query/correcting the syntax below so that it calculates the 'number of days difference' between whatever the 'Biggest Date' field value is and whatever the 'current date' is using the 'sysdate'. So far, I've only managed to get the query to calculate the number of days difference (days past due) between the 'need date' and 'estimated delivery date'.
CODESELECT To_Date(need_date, 'YYYYMMDD') Need_Dt, To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Biggest_Date, To_Date(need_date, 'YYYYMMDD') - To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Date_Diff
FROM tableT
WHERE need_date <= (Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END)
how to caluclate days between two dates of single timestamp filed and with this
query Select * from m_activity_transaction where actn_opp_id in ( Select actn_opp_id from m_activity_transaction where ACTN_ACTV_ID = 218 Group by actn_opp_id
[code]...
and i nedd to caluclate no.of days between two dates like 27-JAN-12 11.06.20.000000 AM and 08-FEB-12 05.32.54.000000 PM where actn_id is unique AND ACTN_OPP_ID IS NOT UNIQUE.
I need a query to find out the working days. I have attached the sample script to create table and data. Here is the description of the tables.
Emp1 : To record the employe's information and weekly rest day. Attendance :- To record daily attendance. Leave_appovd : To record the approved leaves. Holiday : To record the holidays.
W = Attendance R = Weekly Rest L = Leave H = Holiday A = Absent
I have an oracle package that i am using to search for a string in a blob entry. I compiled the package and the package body in one environment, it had no errors, when i execute, i get my results.I went ahead and created the same package and function in another environment and it fails by giving me the below error
ORA-06503: PL/SQL: Function returned without value ORA-06512: at "SYSTEM.IMPACTUS_PCODE", line 158 for sysadm
I have used this on other environments often and have never had an issue.
I have a problem with a query. I have a table employee with data as
emp_id date day working_ind 1 01-Jan-2011 Mon Y 1 02-Jan-2011 Tue Y 1 03-Jan-2011 Wed Y 1 04-Jan-2011 Thu Y 1 05-Jan-2011 Fri Y 1 06-Jan-2011 Sat N 1 07-Jan-2011 Sun N 1 09-Jan-2011 Tue Y
Sundays/ Monday/ any public holiday the working_ind will be N. If the emp is absent on one day then there will be no record entered in the table (e.g. 8th jan there is no record). Each table has only one year data.
I need to retrieve for all employees when they worked for 30 consecutive days without being absent which does not include sat/ Sunday / holidays.
Its like: -- i need to order by emp_id and date -- get oly the data with working_ind as Y -- make sure that i get 30 consecutive days (from what ever i get above) where no days data is missing
I tried using lag and inner join but it does not seem to be working.
I need to identify a due date for a transaction (date) that should be completed within 15 working days from the date of the transaction date. For example, a request is submitted on Wednesday, December 29th 2010. This request should be then 'answered' on Thursday, January 20th 2011 at the latest.
I have a table that identify the status of every calendar day between 1960 to 2060; fields are: calendar day, calendar year, ..., day of week, week day flag, holiday flag.
In my example above, January 1st and 2nd are weekend days and Monday, January 3rd is an holiday.
How do I go getting all (calendar day + 15 working days) for all days of the year?
how can create function to calculate the vacation example if i takes 10 days vacation and Friday , Saturday only a normal vacation but The rest of the days is Within the vacation
how can calculate the vacation and make this function dynamic such as when takes the vacation 1-1-2011 and number of days vacation 10 not including the Friday, Saturday days
I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY ACCOUNTING 8750 RESEARCH 10875 SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
The MS Office 'NETWORK DAYS' function allows you to subtract one date from another and return the number of 'workdays' between them. So it ignores Sat & Sun. There is an additional parameter that allows you to ignore holidays as well.
I'd like to subtract a 'Request Date' from SYSDATE and get just the number of 'workdays'.
create or replace function getDate(p_joing_date Date,p_sysdate) Return Date; IS v_compltd_mnths; BEGIN SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL; return v_compltd_mnths; END;
I can easily calculate the standard deviation for some rows with the STDDEV function. But that returns me a value expressed in the same units as the things being measured. (In other words, if I have times of 1,12,6,11 and 8 seconds, I will be told that the average time is 7.6 seconds, and the standard deviation is 4.39 seconds).
What I'd like to be able to do is to say that the record with value 1 is (say) 3 std deviations away from the average. Or that the 6 second row is within 1 standard deviation.
I am trying to find records which are more than 3 standard deviations of the mean, because they are the outliers I am interested in.
I cannot see how to convert a STDDEV result into being a "number of standard deviations".
(If I have not expressed myself clearly, I'm using this sort of stuff URL....and I'm trying to find >3-sigma records)
simply to multiply the STDDEV result by 1, 2, 3 and so on, and then compare to the time? That is, is it OK to do the following:
Mean = 7.6 seconds (from my sample data mentioned in the above post) StdDev = 4.29 seconds
So 2sigma would be 4.29*2 = 8.78 seconds, plus or minus the mean?
So any record with a time value between -1.18 (7.6-8.78) and 16.38 (7.6+8.78) seconds would be within 2-sigma, assuming normal distribution?
I am still wondering if there's a nice function or something that returns the sigma value for any given set of records?
I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL)) from dwt_dvt_work_time where dwt_create_date > sysdate - 30 and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function: CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE) RETURN NUMBER IS total_days NUMBER(11,2); holiday_days NUMBER(11,2); [code]....
I have installed Oracle Database 11g.2 by database configuration assistant on windows XP as and adminstrator on my laptop(no connection to network),but when I want to create database I face this warning: error securing database control ,Datatbase control has been brought up in non-secure mode . to secure the database conntrol execute following command....(error is attached).
Earlie we used oracle 10g with WE8MSWIN1252 character set(single-byte character) that time the below PL/SQL block was running fine.That is we are passing 56 length character to SYS.DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT function .Now we migrated to 11g with al32utf8 charecter set.Now if we are using 56 length variables to pass the value then we are getting ORA-06502: PL/SQL: numeric or value error: character string buffer too small error.So i have changed the variable length to 86(Minimum 86 required)
But now i am getting different error
Error report: ORA-28232: invalid input length for obfuscation toolkit ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 84 ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 255 ORA-06512: at line 9 +28232. 0000 - "invalid input length for obfuscation toolkit"+
'Oracle fast parallel data unload into ASCII file(s)' in this blog: URL....I have compiled the code and created the objects and the directory in my DB...But when I execute :
SELECT * FROM TABLE( DATA_UNLOAD( CURSOR( SELECT /*+ PARALLEL(A, 2, 1) */ TABLE_NAME || '|' || COLUMN_NAME || '|' || DATA_TYPE FROM MYTABLE A [code]....
It is supposed to return 2 rows (because of parallel execution), but it just returns 1..Do I have to do something special in order to make parallel pipelined function work
Is there any Oracle query I can run to determine the "number of days since the last backup"? SQL Server provides this data but I cant seem to find the equivalent for Oracle. Looks like there may be some information like this via RMAN tables and if so I want to create the simplest query possible to obtain that information.
If we want to know the number of instances, number of RAC databases and whole total disk space used by oracle (not file system size),1. Any script can be ran from OEM grid control against all instances/databases? or2. we have a repository unix server which has all tnsnames of whole databases, any script we can run from there?
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS import java.sql.SQLData; import java.sql.SQLException; import java.sql.SQLInput; import java.sql.SQLOutput; import java.util.List; [code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership join oa_partner_usr_dtl partner_user on to_char(partner_user.partner_userid,'9999')=membership.created_by select * from oa_membership_dtl membership join oa_partner_usr_dtl partner_user on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
I am unable to configure EM(Oracle 11.2g) on Redhat Linux 5 Enterprise edition(32 bit). Environment variables are set to correct SID. If I try to run dbca the wizard does not display the Enterprise manager configuration screen. I have tried to create repository by using emca -config dbcontrol -db config.
But it throws exception that database instance is unavailable?
