SQL & PL/SQL :: Overlapping Date Period
Jun 28, 2012
CREATE TABLE CONTRACT
(
EQT VARCHAR2(10 BYTE),
SUBTYP VARCHAR2(3 BYTE),
FROM_DT DATE,
TO_DT DATE
);
[Code]....
The data in contract and lease table show like below.
contract
EQT SUBTYPFROM TO
ABCD100000DRY1/1/20121/7/2012
ABCD100000DCH1/7/20121/21/2012
ABCD100000GAZ1/21/2012
lease
EQT CONTRACTFROM TO
ABCD100000LS11/2/20121/10/2012
ABCD100000LS21/10/20121/17/2012
ABCD100000LS31/17/2012
Now required output should be
EQT SUBTYP CONTRACTFROM TO
ABCD100000DRY 1/1/20121/2/2012
ABCD100000DRYLS11/2/20121/7/2012
ABCD100000DCHLS11/7/20121/10/2012
ABCD100000DCHLS21/10/20121/17/2012
ABCD100000DCHLS31/17/20121/21/2012
ABCD100000GAZLS31/21/2012
The period for contracts should be in sequential order and should not overlap and if contract does not exist for that date period, then it should return NULL.
View 9 Replies
ADVERTISEMENT
Jun 7, 2012
i want to find the data which has the more then one rates available for the given date. Here below i've posted a test table. how can find the products for which, there multiple dates/overlapping period is available through sql statement.
SQL> CREATE TABLE STO.TEMP
2 (
3 ID NUMBER(8),
4 ITEM_ID NUMBER(8),
5 RATE NUMBER(5,2),
6 FROMDATE DATE,
7 TODATE DATE
8 );
[code]....
View 8 Replies
View Related
Nov 16, 2013
I use Oracle 11.2.0.3.I have APPOINTMENT table:(ID NUMBER PK , FROM_TIME TIMESTAMP , TILL_TIME TIMESTAMP , NOTE VARCHAR2 (200))I want to prevent end users from making an appointment that overlaps with other appointments. I created this trigger:
Create or replace TRIGGER "APPOINTMENT_CK"
BEFORE
INSERT OR UPDATE ON APPOINTMENT FOR EACH ROW
BEGIN
IF
[code]....
But, it looks like I cannot use column name here.
View 11 Replies
View Related
Mar 30, 2013
I have two field date from and date to... i want to enter an employee in date range that range should not be enter again.
View 8 Replies
View Related
Sep 14, 2011
Following is the scenario:
CREATE TABLE EMP_VACATION(
EMP_NO NUMBER(3),
VAC_TYPE NUMBER(1),
START_DATE DATE,
END_DATE DATE,
START_PERIOD_DATE DATE,
END_PERIOD_DATE DATE,
PRIMARY KEY (EMP_NO,VAC_TYPE,START_DATE))
[code]....
--How to calculate START_PERIOD_DATE and END_PERIOD_DATE for every time insert reocrd, like for 4 cases?
--I need to check start_date with last period date for same employee.
And based upon calculate START_PERIOD_DATE and END_PERIOD_DATE,,,,,
View 7 Replies
View Related
Apr 10, 2012
I have TableA which has data like below.
ID NAME CRT_DTE
1 AB 03/05/1992
2 EF 15/04/1995
3 CD 20/08/1995
4 GH 01/01/1999
5 UV 08/07/2001
[code]....
I want a query which splits the total time period (from min crt_dte to max crt_dte) into year ranges.For eg, lets say a range of 5 years then I need to get results like below.
start_dte end_dte
---------- ----------
03/05/1992 03/05/1997
03/05/1997 03/05/2002
03/05/2002 03/05/2007
03/05/2007 09/03/2012
how to write this query.
View 6 Replies
View Related
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
Aug 15, 2012
I have a table with 4 records with start_date and end_date fields
start_date end_date
1/1/2003 1/4/2005
1/1/2004 5/3/2006
1/1/2007 1/4/2008
1/1/2008 6/3/2009
as you see records 1,2 and records 3,4 have overlapping dates. I need to combine them to get continuous intervals. The result should be:
start_date end_date
1/1/2003 5/3/2006
1/1/2007 6/3/2009
Is it possible to do by SQL query?
View 10 Replies
View Related
Oct 11, 2012
whether the overlapping Multiple series charts are possible in Oracle APEX or not .
I am trying to create two graphs for two data sets , one as marker and another as line , but its giving me issues such as
1) X axis is taking all the points of the first series suppose(
1,5,10,180) itsplotting these first . and its then starting labeling
the x-axis for another one which has values like
(20,50,100,1000,15000)
so my X-axis is now ---> 1,5,10,180, 20,50,100,10000,150000
In case i make my marker chart series as the 2nd sequence , then its drawing all the markers on the right side of the chart .
[URL].........
Workspace:- NR_WORKSPACE
Username:-user1
password:- H123$
whether this thing is achievable in oracle apex or not , is there any work around ?
View 0 Replies
View Related
Apr 27, 2011
I'm trying to write a cross tab query but which groups by periods.
Test Case: -
CREATE TABLE COMPANIES
(
"ID" NUMBER(9,0),
"CODE" NUMBER(8,0),
"DESCRIPTION" VARCHAR2(40 CHAR),
CONSTRAINT "PK_COMPANIES" PRIMARY KEY ("ID")
);
[Code]..
The cross tab is to have columns 1 - 31 which sum the quantity of the article sold. Represnting days of the month.
In addition the description of the company.
I would then like to end up with 3 entries in the results for each company, but these are for summing the quantity of articles by the following periods: -
Breakfast: 01:00 - 10:00
Lunch: 11:00 - 14:00
Dinner: 17:00 - 22:30
I hope that makes sense?
View 13 Replies
View Related
Dec 14, 2011
I need to divide the given time period into weeks from Monday to Sunday .There should not be overlapping of two months, for a week.Every month should start from First day of that month to next Sunday .Same thing can be done by following PL/SQL block . let me know if there is any simple way by using query instead of block .
declare
pid_from_date date := '01-JAN-11';
pid_to_date date := '31-dec-11';
ln_number number := 0;
ld_from_date date;
ld_to_date date;
begin
[Code]....
View 5 Replies
View Related
Aug 12, 2010
I need to get the number of rows based on one condition for certain time period. For example, writing a query for 3(any) hrs , i need to get the output as count of rows as hourly basis. Like these:
Date Time Count
12/08/2010 16:00 234
12/08/2010 17:00 345
12/08/2010 18:00 645
View 12 Replies
View Related
Sep 16, 2013
"ALTER PROFILE profile_name LIMITPASSWORD_LOCK_TIME 1"means the user account will be locked in a period of one day after FAILED_LOGIN_ATTEMPTS has gone over limit.How to set PASSWORD_LOCK_TIME less or more than one day (like not full days)?
View 2 Replies
View Related
Apr 17, 2013
In my production environment, I have a mostly working Oracle 12c Cloud Control environment, managing several database instances. On all of the databases, I am unable to use the Compare period ADDM feature ( Instance > Performance > AWR > Compare Period ADDM).
When I select that menu option, I see the message "To be able to use this feature some PL/SQL packages need to be loaded into the target database's monitoring schema, DBSNMP." I have been searching for further information in the specific packages that need to be loaded per the message, but neither Orafaq, [URL] nor Google seem to have those details.
View 5 Replies
View Related
Apr 21, 2011
I am trying to break down a row of data that has nine periods that I want to enter into a table one period at a time. I am trying to use "IF EXISTS ( SELECT * FROM TABLENAME WHERE FIELD1 IS NOT NULL) THEN" through all nine fields. It keeps returning lines that have a null column as well as not null columns. if there is a better way of doing this. Here is an example of what I am trying to do:
IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT9 IS NOT NULL) THEN
SELECT NUMBER, 1COL9, 2COL9, 3COL9, FIRST_DT9, LAST_DT9, 4COL9
FROM TABLE_NAME;
ELSE IF EXISTS (SELECT * FROM TABLE_NAME WHERE LAST_DT8 IS NOT NULL) THEN
[code]...
View 1 Replies
View Related
Aug 16, 2011
i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
View 13 Replies
View Related
Nov 18, 2011
Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.
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
Dec 3, 2012
Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,
1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE
2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added
:P1_SYSDATE := sysdate
and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.
I get the error
ORA-01790: expression must have same datatype as corresponding expression
I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.
View 1 Replies
View Related
Oct 24, 2013
When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
View 0 Replies
View Related
Sep 27, 2007
I am a novice in oracle
I have 2 columns in my table
->Col1 with experience in years entered as an integer
->Col2 with current date
I need to add another column as a date value adn for that i need to subtract Currentdate-Col1 when i tried currentdate-Col1 it just subtracted the days i need the formula to subtract years and give a date
I have worked in DB2 and all u need to do there was add the keyword years at the end but in oracle the same does not work
View 8 Replies
View Related
Jun 6, 2012
I am not able to load complete date along with time in the date column. here is my table desc
DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE
my control file is like this
LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)
the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12
BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)
View 10 Replies
View Related
Aug 16, 2012
I would like to inquire how do I code my select statement if my user enter a search date range(search startDt: 01/08/2012 and search endDt :30/09/2012) and I will like to retrieve only the 7records out from my table as shown below ?
STARTDT_ ENDDT_
01/08/2012 01/08/2012
01/06/2012 31/12/2012
01/08/2012 01/08/2012
01/09/2012 01/09/2012
01/09/2012 31/12/2012
01/07/2012 01/07/2012->not retrieved out
01/08/2012 01/08/2012
01/01/2012 31/12/2012
View 6 Replies
View Related
Apr 19, 2011
Write a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:
If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month".
If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month".
If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".
For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".
View 9 Replies
View Related
Jun 21, 2012
How can we partition a table based on date if it does not have a date column.
Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.
I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.
View 1 Replies
View Related
Apr 11, 2013
I have a small prolem thats best described like this....
a table called TONY with a field named VISITED (date as YYYYMMDD).
We want to populate the field TIMESTAMP (Last visited timestamp, 18 digits) using midnight or 00:00:00 on VISITED value.
Something like:
UPDATE TONY SET TIMESTAMP = �(whatever the formula is involving VISITED).
but i cannot figure out the best way to derive the TIMESTAMP value...
it's a date to epoch conversion, and i can find many examples of Epoch to date, but thats the wrong way around for me i'm afraid!
Oracle 11gR2 by the way...
View 4 Replies
View Related
Aug 16, 2010
i have a table with the following description
create table gl_periods(period_name varchar2(10),transactions number (2) );
with the data as :
period_name transactions
------------ --------------
JAN-10 12
FEB-10 12
MAR-10 8
APR-10 23
ADJ_TOM-10 25
MAY-10 37
JUN-10 41
JUL-10 10
PHY_JAY-10 6
AUG-10 14
SEP-10 22
My requirment is to find out the period names and transactions which are in valid date formats and are less than sysdate and the non date formats are adjustments made by different users for their transactions
View 8 Replies
View Related
Oct 18, 2012
I want to load data from a file using sqlldr.I have a table commissions
(
technician_id char(5)
, tech_name char(30)
, Comm_rcd_date DATE
, Comm_Paid_date DATE
, comm_amt number(10,2)
)
my file is
00001,TIMOTHY TROENDLY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0007,123.56
00002,KENNETH KLEMENZ,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0009,123.56
00003,SHUNDAR ARDERY,2011-03-04T01:45:12+0006,2011-03-04T01:45:12+0005,123.56
write a ctl file to load this data.
View 6 Replies
View Related
Apr 30, 2012
I want to get data for month to date. For example, If I pass today or any day date as parameter then i should get data for that month(month of passing date) up to passing(parameter) date. As well as i have to get year to date.For example, If I pass today or any day date as parameter then i should get data for that financial year(year of passing date) up to passing(parameter) date. how to get month to date and year to date data.
View 3 Replies
View Related
