Calculate Working Days With Oracle Function

Mar 13, 2009

how do i can make oracle function for the following vb6 function.

This function is used for calculating working days between two date.

****************************************
Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' Note that this function does not account for holidays.

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

On Error GoTo Err_Work_Days
BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
[code]......

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Oracle Stored Function / Procedure To Calculate Number Of Working Days Between Two Dates

Feb 17, 2010

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.

View 7 Replies View Related

PL/SQL :: Oracle Dates And Working Days

Sep 5, 2012

Is there an Oracle date function that ignores public bank holidays and calculates working days only?

View 4 Replies View Related

PL/SQL :: Query To Calculate Business Days

May 20, 2013

i have table name order table . in that i have column name install date,order date.

the difference bet ween order date and install date should be greate than or equal to 3 working days.

fri and sat are holidays.

i need one query to find the difference betwwen orderdate and install date is greate than 3 working days

View 3 Replies View Related

Syntax To Calculate Number Of Days Difference

Sep 27, 2010

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)

ORDER BY Date_Diff ASC

View 6 Replies View Related

PL/SQL :: How To Calculate Days Between Two Dates Of One Timestamps Field

Sep 25, 2012

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.

View 6 Replies View Related

SQL & PL/SQL :: How To Calculate Working Day

Jan 1, 2013

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

Required output is

Emp No.In Date Out Date Type
736912/1/201212/1/2012W
736912/2/201212/2/2012R
736912/3/201212/3/2012W
736912/4/201212/4/2012W
736912/5/201212/5/2012W
736912/6/201212/6/2012L
736912/7/201212/7/2012H
736912/8/201212/8/2012H
736912/9/201212/9/2012R
736912/10/201212/10/2012A
736912/11/201212/11/2012W
736912/12/201212/12/2012W
736912/13/201212/13/2012W
736912/14/201212/14/2012W
736912/15/201212/15/2012L
736912/16/201212/16/2012L
736912/17/201212/17/2012L
736912/18/201212/18/2012W
736912/19/201212/19/2012W
736912/20/201212/20/2012W
736912/21/201212/21/2012W
736912/22/201212/22/2012W
736912/23/201212/23/2012R
736912/24/201212/24/2012A
736912/25/201212/25/2012A
736912/26/201212/26/2012A
736912/27/201212/27/2012W
736912/28/201212/28/2012W
736912/29/201212/29/2012W
736912/30/201212/30/2012R
736912/31/201212/31/2012W
778212/1/201212/1/2012W
778212/2/201212/2/2012W
778212/3/201212/3/2012W
778212/4/201212/4/2012W
778212/5/201212/5/2012W
778212/6/201212/6/2012W

Separate Query will be run for the following output.

Wroking Days
EmpnoAttend WeeklyRestsLeavesHolidays TotalAbsentsG. Total
7369 17 4 4 2 27 4 31
7782 6 0 0 0 6 25 31

If any body work on weekly rest or holiday, it will be considered as weekly rest and holiday. There working on these days will be treated separately.

View 10 Replies View Related

SQL & PL/SQL :: Oracle Function Working In DB And Not In Another?

Jun 16, 2010

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.

View 7 Replies View Related

SQL & PL/SQL :: Query To Get Consecutive Working Days From A Table

Jul 15, 2011

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.

View 5 Replies View Related

PL/SQL :: Count Of Working Days For Current Month

Sep 9, 2013

How can I get count of working days as of given date using SQL?

View 6 Replies View Related

SQL & PL/SQL :: Identify Due Date For A Transaction - Working Days

Jul 5, 2011

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?

View 2 Replies View Related

SQL & PL/SQL :: How To Convert Days Into Weeks And Days (if Any) Oracle 9i

Apr 5, 2011

want to write Procedure to return weeks and days from given set of days.

let suppose we have 72 days to return weeks and days then return should be 7 weeks and 2 days.Can i use date function Or ?

View 5 Replies View Related

SQL & PL/SQL :: Function To Calculate The Vacation?

Oct 31, 2011

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

View 4 Replies View Related

SQL & PL/SQL :: PMT Function Which Calculate Payment

Dec 14, 2010

PMT function which calculates the payment for a loan based on constant payments and a constant interest rate. I need to build PMT function in oracle.

View 2 Replies View Related

SQL & PL/SQL :: Calling Function Which Calculate Sum Of Salary

May 8, 2010

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

View 4 Replies View Related

Emulating MS Office NETWORK DAYS Function

Oct 29, 2010

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'.

View 3 Replies View Related

SQL & PL/SQL :: Write A Function To Calculate Difference Between Two Dates?

Sep 22, 2011

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;

that i have worte..

View 3 Replies View Related

PL/SQL :: Calculate Standard Deviation For Some Rows With STDDEV Function

Oct 10, 2012

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?

View 5 Replies View Related

Function To Find Business Days Between Two Dates - Query Tuning

Mar 17, 2011

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]....

View 1 Replies View Related

Server Administration :: Overlapping Days Between 3 Date Ranges To Determine Active / Inactive Days

Sep 2, 2011

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).

View 7 Replies View Related

SQL & PL/SQL :: IN Clause Is Not Working For Stored Function?

Mar 12, 2012

IN clause is not working for stored function.At same time, the LIKE conditon is working.

SQL> CREATE OR REPLACE FUNCTION GET_EMPLOYEES (in_asset_type in SECURITY_TYPE.asset_type%TYPE)
2 RETURN VARCHAR2

[Code].....

View 1 Replies View Related

Forms :: Why Function Keys Are Not Working In 9i

Dec 27, 2012

why function keys are not working in forms9i?

Like whenever I press F7 for query or F8 to fetch records or F10 to save records they are not functioning.

View 2 Replies View Related

PL/SQL :: DBMS_OBFUSCATION_TOOLKIT Function Not Working In Al32utf8 Character Set?

Sep 3, 2012

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"+

[code]....

View 23 Replies View Related

SQL & PL/SQL :: Parallel In Pipe-lined Table Function Not Working?

Feb 1, 2011

'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

View 2 Replies View Related

SQL & PL/SQL :: Oracle Query For Days Output

Aug 5, 2010

I want to pull out the output only when Day is 1 and 2 and 3. I don't want if either Day is 1 or Day is 2 or Day is 3 is present.

Test Case

--Creat Table
create table dummy_name
(name varchar2(5) not null,
day number(3) not null);

--Insert Values
insert into dummy_name values ('A', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 3);
insert into dummy_name values ('B', 1);
insert into dummy_name values ('B', 2);
insert into dummy_name values ('C', 1);
[code]...

View 8 Replies View Related

Oracle - SQL Query For Number Of Days Since Last Backup

Oct 20, 2011

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.

View 3 Replies View Related

Calculate Total Disk Space Used By Oracle Databases Across Whole Company?

Oct 18, 2013

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?

View 5 Replies View Related

ORA-00932 - Sample Code Working Fine In 10g And Not Working Now In 11g

Apr 1, 2013

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

View 3 Replies View Related

PL/SQL :: To_char Not Working / (||) Is Working With Join Query

Mar 22, 2013

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.

whats the diff between to_char and || symbol?

View 1 Replies View Related

Oracle 11g EM Not Working (Linux OS)

Jan 20, 2011

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?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved