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
ADVERTISEMENT
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
May 22, 2010
The data in one of the temporary table has been compiled as below. Number of Days elapsed between two transaction dates is required to be computed and multiplied with the balance. There can be multiple number of same transaction dates. When previous date and the current transaction dates are same the resulting number of day difference should be 0. But when they are different the difference between them is to be computed. On the last day of the Financial Year i.e. '31/03' of any year the difference day should be shown as 1 so as to make 365 or 366 days in a year. Simply deducting d1 from d2 on 31st will not be suffice as the difference is one day less.
----------------------------------------------------------------------------------------- -------
Transaction Narration DebitCreditBalanceNo of DaysProduct
DateDifference
----------------------------------------------------------------------------------------- --------
01/04/2009Opening Balance 45020450277346654
17/06/2009ByAmt5044521253424
29/06/2009By Amt1004352156678912
02/12/2009By Amt424310119476000
[code]....
improve the above code and get the desired output result.
View 5 Replies
View Related
Sep 5, 2012
Is there an Oracle date function that ignores public bank holidays and calculates working days only?
View 4 Replies
View Related
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
View Related
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
Sep 9, 2013
How can I get count of working days as of given date using SQL?
View 6 Replies
View Related
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
Jan 10, 2010
My transaction of 1 day Jan7,2009 cannot be manipulated by any DML..What shud i do to unlock those records...??
View 10 Replies
View Related
Jul 24, 2011
I'm using Oracle 9i Enterprise edition, Is there a select statement to view transaction log for specific date?
View 1 Replies
View Related
Jul 7, 2012
Tables
--------
ROWID START_DATEEND_DATE
101/26/2012 00:00:0001/31/2012 00:00:00
201/26/2012 00:00:0002/02/2012 00:00:00
302/03/2012 00:00:0003/31/2012 00:00:00
How to find out the days difference in sql query, start date of next records and end date of previous records as highlighted on blue color
query out put should give as below
start_date + next record ,end_date - Previous records should give the difference of
6
1
View 12 Replies
View Related
Jan 24, 2007
I have a Employee data as below with EID and DOB
EID DOB
----------------------
1 03/01/1950
2 14/01/1950
3 27/01/1950
4 02/02/1950
5 04/03/1950
6 12/03/1950
7 28/03/1950
8 28/02/1951
9 08/03/1951
10 28/03/1951
I need a query to display the birth date of all emp in such a way that if i run a query today the dob which lies between today & today+15 days data must be displayed.
View 8 Replies
View Related
Sep 11, 2010
In my server , already 10g r2 is installated , now am installaling 11r2, during this, at final stage while running root.sh , it will propmt to override 3 files, oraenv,dbhome .. etc under /usr/local/bin in solaris sys, as these files are already owned by 10g owner ,what i have to select (y or n)?
what if i select y? it will override three files
what if i select n? default option
View 2 Replies
View Related
Jan 26, 2011
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
[Code]...
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
View 2 Replies
View Related
Mar 13, 2013
I am trying to get the last 7 days of record from today date, this query runs every night and I always want the last 7 days. Example - today is 3/13/2013 so I want record from 3/7/2013 to 3/13/2013 and tomorrow it would be 3/8/2013 to 3/14/2013
Here is my query, dont mind the ****,$$$ or ####
SELECT INFORMENT.PRODUCT_OFFER_PURCHASE.*******_#######, INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_ADDED, 'YYYYMMDD'), INFORMENT.DEPOSIT_$$$$$$$.BAL_LEDGER_CURRENT, INFORMENT.PRODUCT_OFFER_PURCHASE.INTEREST_RATE, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_OPEN, 'YYYYMMDD'), To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_CLOSE, 'YYYYMMDD'), INFORMENT.PRODUCT_OFFER_PURCHASE.*******_STATUS_CODE, [code]..........
View 16 Replies
View Related
Oct 31, 2011
I tried to insert date in date column from another table date column. Below is SP ans execute procedure.
CREATE OR REPLACE PROCEDURE test(
start_time Varchar
)
AS
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(start_time);
Insert into test_ora (report_date) (select start_time from
[code]....
execute test ('27-10-2011 12:01:01');
It gives error as Invalid number.
View 2 Replies
View Related
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
Aug 14, 2011
is the definition of my table :
CREATE TABLE DATEFETC
(
ID VARCHAR2(10 BYTE),
DT DATE
)
And these are the data that are available,(select * from DATEFETC)
IDDT
00108-09-2011
00208-10-2011
00308-11-2011
That's fine.
Now i am executing this query ,but this is returning no rows.Why ?
select * from datefetc where dt between to_date('08-08-2011','mm-dd-yyyy') and to_date('08-12-2011','mm-dd-yyyy')
View 1 Replies
View Related
Sep 20, 2011
I have been developing and oracle application therefore i have installed the oracle express edition 10g database software and the oracle developer 10g. I did some changes in sql*plus i mean i have changed the language(through 'nls_lang' to Arabic), i made a autonumber trigger, i changed the calender format (through 'alter_session...'), now all the changes are working fine from the oracle sql*plus but as i am running my form from form builder none of those changes are working.
i thought the oracle form and sql*plus don't have such link..i can add the tables i have created in sql*plus into the form.
View -1 Replies
View Related
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
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
Nov 14, 2012
Is it possible to find the schema name,table name and DB name if we know a value in table ?
View 6 Replies
View Related
Feb 1, 2011
I have table named TEST with NO (NUMBER) and DOJ (DATE) columns with primary key.
Post inserting a millions of records to it, i am inserting a duplicate NO and DATE.
How oracle identify (mechanism used) the latest record as duplicate?.
View 4 Replies
View Related
Jun 1, 2010
I'm trying to work out how to take a table like this:
IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09
And display the data like this:
IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
View 13 Replies
View Related
Apr 12, 2010
I have a two date fields in my form; valid from date and expiry date.
Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.
I need my expiry date to automatically show a date 15 years after this date?
View 8 Replies
View Related
Mar 21, 2011
Whenever there is a change in the c_flag, we need to identify the change and do some processing. How do I identify the change from either 1 to 0 or 0 to 1. The change can be either way. The point of importance is to identify the change.
CREATE TABLE tab1 ( id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
Start_Date DATE,
id_num NUMBER(10),
c_flag varchar2(2));
[code]....
View 2 Replies
View Related
Apr 15, 2010
I am looking for the ways to identify the Footprint of the My ID on the database, as it is used to run very heavy production reports.may include utilization of resources.
View 12 Replies
View Related
Apr 27, 2013
I'm trying to identify what PL/SQL or the exact code that's is causing massive UGA consumption. How can I identify it?
View 5 Replies
View Related
Apr 12, 2013
Quote:Every patch or patch set also has a patch number to identify it
All patch and patch set releases carry version numbers.
For ex : Oracle 10g Release 10.2.0.1, then an available patch set might be 10.2.0.2 , .... , 10.2.4.0 , 10.2.5.0
My QUES is : How can i identify Patch no ? How it is differ from patchsets no ?..
View 27 Replies
View Related
Sep 2, 2012
I am trying to write a code to identify the delimiter in the file ( which is in the form of table(id, raw) in system)then I take this delimiter and pass it as a parameter to SP which perform cleaning of this file(table) and creates another clean table.
The problem I am facing is until now the file was coming with one fixed (TAB) delimiter, but now it has come with different (SPACE), now here I want to develop the code to identify the delimiter place it in a variable an pass this as parameter to cleaning SP.
--here i want to develop code to identify delimiter from hosts_equiv file which has data as below
select * from hosts_equiv where left(raw,1) not in ('#','*') and isnull(raw,'')<>''
Raw id
---------------- ---
hiper USER1 1
hiper2 USER1 2
APX user2 3
Need to identify delimiter between e.g. hiper USER1 and pass it as a parameter to the raw_parse sp
declare Tab varchar(10)
set Tab = char(9)
exec raw_Parse
'hosts_equiv', -- From table ( entire file content is stored as table with Id record no sequence generated)
'CleanedHosts_Equiv', -- To table name, when passed it will clean the from table and places the cleaned data in to_table
[code]....
View 3 Replies
View Related
