SQL & PL/SQL :: Measure Time Overlap Between 3 Date Ranges
Mar 28, 2013
I have this requirement to pull the ACTIVE days that a participant holds true in a given month for a specific position he/she holds.
The three date ranges here are: LOA dates(leave of absence), XFER dates(TRANSFER in/out of the position),Quality compliance(QUAL start/end dates).
So,
INACTIVE DAYS = [(LOA + XFER + WW) - OVERLAPPING DAYS OF (LOA+XFER+QUAL)]
Here is the scenario:
---------------------
CREATE TABLE PAYEES
(
RUN_PERIOD DATE NOT NULL,
PAYEEID VARCHAR2(20 BYTE) NOT NULL,
LOA_START_DATE DATE,
LOA_END_DATE DATE,
[Code].....
---------------------------------------------------------
---------------------------------------------------------
Now Active days is to be pulled for Two payees - TESTD01 and TESTD02 for the period of June month.
Desired Results :
Payee InActive_days Active_Days Period
---------------------------------------------------------------------------------------
TESTD01 13 17 June
TESTD02 14 16 June
I am able to pull out the no of days on a individual basis(count of days for Xfer/LOA/Qual) but I am really not sure as to how should i go about determining the overlapping days between 3 date ranges to determine the ACTIVE/INactive days.
View 11 Replies
ADVERTISEMENT
Sep 3, 2010
Lets say I have a table,
Claim_id,dateA,dateB
it has 5 million rows
I need to see if any dateA,dateB of a claim_id falls within any other dateA,dateB of another claim ID
Basically
select * from table a, table b, where
(a.dateA,a.dateB) overlaps (b.dateA,b.dateB)
now I can write the query simply enough by aliasing the table 2x but no matter how I try I cant see a way to get around doing a Carteasion join
Index are ignored because it has to scan the full table anyway even if I hint the index
and the cost of the join ends up astronomical aka 5million rows X 5 million Rows....
And it ends up doing the full table scan a few times
AHHHHHHHHHHHHHHHHHH
this table is expected to grow to at least 20 million records
View 17 Replies
View Related
May 11, 2011
Lets say the contents of the table are like this -
key dte_start(number) dte_end(number)
1 20010101 20011231
1 20020101 20081231
1 20030601 20071231
1 20100101 20101231
2 20090101 20091231
2 20090401 20101231
2 20101231 20111231
2 20140101 20141231
3 20080101 20091231
3 20070101 20081231
3 20100101 20101231
3 20120101 20120630
I want the query to resolve overlapping dates as well as merge contiguous segment and leave non-contiguous segments as is. The final result of the query should be like this.
key dte_start(number) dte_end(number)
1 20010101 20081231
1 20100101 20101231
2 20090101 20111231
2 20140101 20141231
3 20070101 20101231
3 20120101 20120630
I tried using the lead over partition along with the standard overlap query and case logic to get the results but couldn't get it to work.
View 14 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 30, 2010
Can we insert multiple dates within give range in a single insert statement say from 1st Feb to 31st MAY 04,something like after insertion it should resemble like below
01-FEB-04
02-FEB-04
03-FEB-04
04-FEB-04
05-FEB-04
.
.
.
31-MAY-04
View 5 Replies
View Related
Aug 2, 2011
I have data such as 'hours', 'date' when and employee worked on the project. What I need is to select the total amount of hours per month of March, April, May, etc...
I know how to select data per single date but wonder how to do it per multiple dates. How does one select total amount of hours per multiple date ranges (March, April...)?
View 4 Replies
View Related
Oct 7, 2010
I have to get totals from a table using different criteria, which I do like this:
<QUERY>
SELECT DISTINCT
SUM(CASE WHEN MYCONDITION1 THEN 1 ELSE 0 END) AS TOTAL1,
SUM(CASE WHEN MYCONDITION2 THEN 1 ELSE 0 END) AS TOTAL2
FROM TABLE1, TABLE2
WHERE COMMON_CONDITION1 AND COMMON_CONDITION2
AND datevalue1 >= DATE1 AND datevalue1 <= DATE2;
<QUERY>
This works fine and I get the intended result.Now, I have to repeat this for every week for the last 12 months, excluding holidays period. So, I generate a set of date ranges which will be used in the queries. So, I repeat the above sql statement for all the date ranges, which is a lengthy process.How can I do that in a single shot and get all totals for each date range.
View 4 Replies
View Related
Oct 30, 2011
I'm looking to see if there's a solution to my problem that I can use within the context of my business application interface into an Oracle RDMS. I have access to write custom SQL statements and functions, but I am NOT able to create stored procedures using the interface I have.
The challenge I am having is comparing date ranges. I have a table containing two columns labelled START TS TIME and END TS TIME, both of type 'Date'. I have figured out how to query each row against a given Next Session Start and Next Session End and determine if each row overlaps that row.
I need a procedure that will be recursive: that is, set Next Session Start and Next Session End to START TS TIME and END TS TIME of the first row, compare all rows against it, then set Next Session Start and Next Session End to the next row, compare all rows, ... for all rows in the table. I want to know what the maximum number of matches is (i.e. the most time periods that overlap).
If I could use a stored procedure I could complete this query easily. Is there other techniques (i.e. functions) available to leverage in order compare each row of date ranges against ALL rows in the same table?
View 4 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
Jun 3, 2010
I am studying Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2),
ORA-14767: Cannot specify this interval with existing high boundsby giving an example other than given in the above mentioned document.
the example given in the document is as follows:
To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:
CREATE TABLE transactions
( id NUMBER
, transaction_date DATE
, value NUMBER)
PARTITION BY RANGE (transaction_date)
[code]/...
View 3 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
Jun 26, 2011
Is there any other ways to measure a sql run time more accurate and more efficient? I meant using a spool and set timming on like this.
[oracle@milo ~]$ cat measure_sql.sql
spool result.txt;
set timing on;
select * from t3 where empno=7788;
set timing off;
spool off;
[oracle@milo ~]$ sqlplus scott/tiger @measure_sql.sql
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 27 01:36:32 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
16 rows selected.
Elapsed: 00:00:00.01
View 12 Replies
View Related
Jun 4, 2012
When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.
If I try the same in SQLplus with to_date it works however fails in PL/SQL.
XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>
PL/SQL Extract:
CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......
View 3 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
Dec 23, 2012
I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:
DECLARE
v_latest_close DATE;
BEGIN
v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);
END;
the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM
View 4 Replies
View Related
Sep 16, 2011
I need to convert the type of GMT to Local time, during data entry. I have a source table and a target table.
To make the insert get the most value, I have no problems:
insert into destination (SELECT * FROM WHERE source pointnumber = (SELECT MAX (pointnumber) FROM source));
But since times are different between the tables, I want to convert the data obtained to GMT -4:30 Time (Caracas - Venezuela), before inserting it.
I can use a function?
View 1 Replies
View Related
May 21, 2013
I am having problems with the XMLTable function. I cant get it to see the entire date/time value in a date field. This wont work
select x1.* from XMLTABLE('/DOCUMENT' passing xmltype('<DOCUMENT><STR>abc def ghi</STR><NUM>1234</NUM><DT>2013-02-17T04:24:02</DT></DOCUMENT>') columns STR varchar2(25), NUM number, DT date) x1;
However if I change the DT tag to just the date only "2013-02-17" it works. Why wont Oracle see the entire date/time format even if its ISO 8601 compliant?
Oracle DB: 11.2.0.3.0
View 3 Replies
View Related
Feb 21, 2011
I have one table with 10 rows and its frequently updated now my question is that, how can it get the latest date of updation of that rows?
View 4 Replies
View Related
Apr 24, 2005
I only know to retrieve data (date & time) by the following statement : "select sysdate from ..."
but now I want to make the fields current date and current time separately and put in a adult file with these two fields. how to do it.
View 11 Replies
View Related
Sep 28, 2011
I had a database at microsoft access but i had move it to the oracle database.
When i check the datatype at oracle, date column is at DATE datatype. but it only display date. like in oracle. it display:
oracle_date.PNG ( 74.61K )
Number of downloads: 4
But when i link it to microsft access. like i will control the oracle from access. the result is at below:
access_date.PNG ( 136.11K )
Number of downloads: 3
So, my problem is how to display the date and time fully in oracle?
View 1 Replies
View Related
Mar 1, 2010
iam having a table A where column ABC is a varchar field with varchar2(50).
currently data stored is like this.
02-27-2009 11:01:33
02-27-2009 11:01:46
03-06-2009 09:07:18
now i want UTC Date time should be in format 2010-02-24T17:08:09Z. in the above format.
View 9 Replies
View Related
Apr 3, 2012
is there possible to retrieve date with its time in oracle ,if we didnot provide time while insertion? like hiredate column of emp table can we get hiredate of each emp with its time of joining. i am using "11g Enterprise Edition Release 11.2.0.1.0"
View 3 Replies
View Related
Nov 1, 2012
I have a question with Oracle sql developer, i've installed oracle 11g express edition, and i want to insert values 'date' and 'time', but i only got the resultat like this:
Who can tell me how can i do to show just date info in 'date' and same for the 'time'?
View 7 Replies
View Related
Jun 10, 2011
T1 = 06-Jun-2011 4:00PM
T2 = 10-Jun-2011 11:AM
Calculation is Required.
If at the end of Day Hours of T1 > 6 hours then it should calculate 1 Day.
same with T2. hours of T2(from begining of Day till end Time (11AM), it should again caculate if >6 hours then 1 Day.
remaining will be date calculation. this is how T2 - T1 should give us 5 Days.
Detail
06-Jun-2011 4:00PM to 06-Jun-2011 12:00AM (8 hours > 6 = 1 Day)
06-Jun-2011 12:00AM to 07-Jun-2011 12:00AM = (1 Day)
07-Jun-2011 12:00AM to 08-Jun-2011 12:00AM = (1 Day)
08-Jun-2011 12:00AM to 09-Jun-2011 12:00AM = (1 Day)
09-Jun-2011 12:00AM to 10-Jun-2011 12:00AM = (1 Day)
10-Jun-2011 12:00AM to 10-Jun-2011 11:00AM (11 hrs > 6 = 1 Day)
this is how 5Days calculation is required.
View 1 Replies
View Related
Apr 4, 2010
I am converting data from an old paradox table to a new oracle table, one of the problems im having is incompatibility with date and time formats:
some columns contain times in the format : "00:00:00" eg..... "15:00:00"
some columns have date in the format: "dd/mm/yyyy" eg....... "21/08/2000"
some columns have time and date eg.. "05/09/2000 15:49:39"
Currently I have the data held in tables within an access database, and in CSV format.
eg, I have dates like 03/04/2010 which i need to be 03-APR-10....
how I can get the following into Oracle date formats? there is over 1000 records so manual conversion is out of the question
View 21 Replies
View Related
Jul 21, 2011
I have the following piece of
SELECT decode(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'00000000000000',
NULL,
(to_date(substr('18900607|000000|20110718112109|23972|002',
17,
14),
'YYYY/MM/DD HH24:MI:SS')))
FROM dual;
When I run the above code I get an incorrect display ie. "18-JUL-11". when I remove the decode I get the correct display ie."2011/07/18 11:21:09 AM"
View 2 Replies
View Related
Apr 19, 2013
Create table time_in (
Emp_id varchar2(6),
at_date date,
time_in varchar2(6),
status varchar2(2)
)
[Code]....
-------------- required restul----------
emp_id at_date time_in at_date time_out
------ ------- ------- -------- --------
00009 16/03/2013 09:47 17/03/2013 09:45
00009 17/03/2013 09:48 17/03/2013 10:43
00009 18/03/2013 09:57
00009 19/03/2013 09:38 19/03/2013 05:22
00009 20/03/2013 09:48 20/03/2013 05:24
status 01 = time in
status 02 = time out
View 13 Replies
View Related
Aug 17, 2011
SQL> desc guide;
Name Null? Type
----------------------------------------- -------- ----------
GUIDE_ID NOT NULL NUMBER(20)
CHANNEL_ID NOT NULL NUMBER(5)
SHOW_DATETIME DATE
[code]....
when I run this query it only displays the date but no time.how to display it with the date and time?
View 2 Replies
View Related
May 13, 2013
I have a sql id,I want to figure out how many number of times that sql was executed since last 30 days.I can get some details from dba_hist_active_sess_history.
But how to get count(total executions in one day )like wise for 30 days or so.and How can i map SNAP_ID to date&time .
View 3 Replies
View Related
Dec 15, 2012
We have oracle 10g R2 on windows...
I have a table test and it contains date datatype column JDATE;
SQL> desc test
Name Null? Type
----------------------------------------- -------- --------------------
EMPNO NUMBER
EMPTYPE VARCHAR2(20)
SALARY NOT NULL NUMBER
JDATE DATE
DEPTNO NOT NULL NUMBERSELECT TO_CHAR(JDATE,'DD/MM/YYYY HH24:MI:SS') JDATE FROM DUAL;
JDATE
1/11/2010 4:17:29 PM
1/11/2010 4:15:47 PM
1/5/2010 3:50:44 PMIn the above case i want to update test table and increase the minut of each row by 1 minut.
like
for 1/11/2010 4:17:29 PM It would be like 1/11/2010 4:18:29 PM.
for 1/11/2010 4:15:47 PM it would be like 1/11/2010 4:16:47 PM.Can we do this...
View 4 Replies
View Related
