SQL & PL/SQL :: Comparing Date Ranges Against All Rows In Same Table
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?
I can give you the logic first i'll sort the start_date(already sorted in given example), then i'll compare the 2'nd id start date with 1'st id end date if it is less than the 1'st id end date, which means overlapping is there, then i'll group those 2 id's in to same group if not group them into 2 different groups.
I am new to oracle, I have request to build a query,
we have table that generates data from 7am to 20pm for eavery hour it generates 4 rows and has 43 session values as 43 columns.
Now i want to find for every hour which is the hights session value at what time. in one hour it runs four times like 7, 7:15, 7:30 and 7:45 and each row has date, time and 43 session columns in table...
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
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...)?
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.
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.
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.
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)
I am working in form 6i, database 9i. I have datablock on table t1.
table t1: name(varchar2), date(varchar2)
datablock: name(varchar2), date(varchar2)[i have insert date with time stamp]
for date column, i am inserting date with time stamp.While querying data, user just enters only date(no time stamp), i should be able to query data. I tried in data block where condition
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).
since the orgid 1 has changed the dept from org1 to org2 I do not want this to be appeared in the final count. Results should only include the orgid 2 since it didn't changed any dept.
I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.
I'm trying to run a script that will sum column values from a table using only a certain period of time in example below only January submissions. However, some of those rows have the same date and I want to have the latest time row of that day in the count.
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE i_e NUMBER(10); BEGIN FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL) LOOP FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
I am trying to run the following queries in my database.
Q-1)select * from item where trunc(approval_date)='21-MAY-13' When Q-1 is executed it returned row that has approval_date of 21-MAY-13 from database. Q-2)select * from item where approval_date=to_date('21-MAY-13') When Q-2 is executed it says no rows selected.
I am trying to retrieve all rows with the date of May 23rd, 2008. Our data is stored with the date and time.
How do I format the where clause so that the time is taken off? I am still a newbie with oracle and what I have is this:
Select Distinct Table1.*, Table2.* from Table1, Table2 where Table1.file_name = Table2.file_name and Table1.downloaded_date = Table2.date_loaded and downloaded_date = '23/May/2008'
Of course, it's the last line not working because I don't know how to format it and no rows are retrieved when there are definately May 23rd rows stored in there.
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.
Due to some business requirements a table field needs to change from date to timestamp in order to handle the millisecs.
1>When i alter the row , for a table with 150 million recs will there be a conversion. Is there a recommended way to convert the field. Mind you this field is used as a part of composite PK.
2> There is a interfacing application which connects and copies the data to its system and is using the date type, will that application be able to continue to work without any changes, if it does not care about the millisecs.
3> Will there be performance impact on an existing application that uses the date field to sort
I want to get top two rows based on ACCT_UNIT & order by status_date, if there is only one row on acct_unit, get one row. IF more than two rows available, want to get the top two rows based on status_date.
I have a particular activity like R1 where I need to find the results for some periods as above. To be clear, if the activity is completed in between Jan 2011 to March 2011, then can I get data as like the above format?
I thought I would make use of the following query, but I am not getting the proper results when applying it to a real table with more than 20 mln records:
SELECT trunc(R_DUR/6)*5+1 as range_start_rdur, trunc(R_DUR/6)*5+5 range_end_rdur, sum(noofan) as no_of_an, sum(sumofrdur) as sum_of_rdur, sum(sumofchdur) as sum_of_chdur, [Code] ...........
