SQL & PL/SQL :: Query To Split Period Into Date Ranges?
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.
--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,,,,,
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.
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.
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 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)
MEASURE_VALUE(ID, VALUE) containing measure values. I would like to calculate the average of a specific id interval. In my case the id is the position where the value was captures.
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).
I have a table having a code column A and a date column D1 which represents the days when the code was inserted. I can have more then 1 date for a code.
The idea is to create another column (an id) which will correspond to all the inserted days of a code for a period of 4 month. if, after 4 month of the first insert, another records appears, then another id will be generated for my code. If a code has the date included in one of the intervals existing already, then it will just receive the corresponding id.
Example: id code date id1 cd1 01/01/2010 id1 cd1 04/03/2010 id1 cd1 22/04/2010 id2 cd1 27/05/2010 id2 cd1 21/06/2010
If a cd1 appears now on 22/05/2010, then i have to give it id2.
Scenario 1 Query should check for priority record(25), if the start_date and end_date of that priority record is the max in that group, records will not have any split.output will be the same.
DC Store St Date End date Priority 955 3 1/1/2010 12/31/9999 25 966 3 4/5/2011 10/10/2011 50 977 3 10/12/2011 12/12/2012 100
output
DC store St Date End date Priority Rank 955 3 1/1/2010 12/31/9999 25 1 966 3 4/5/2011 10/10/2011 50 2 977 3 10/12/2011 12/12/2012 100 3
Scenario 2 If priority record is not covering the max range, then split the records as shown below,
1. during the time period 1/1/2011 & 4/30/2011 there were no other DC for that store so rank would be 1
2. the next range would be 5/1/2011 to 6/29/2011 we have 2 records in service so the record with low priortiy would be ranked 1 and second priority would be ranked 2
3. similarly, for 6/30/2011 to 10/1/2011 we have 3 records in service and it will be ranked accordingly on the priority.
DC Store St Date End date Priority 966 3 6/30/2011 10/1/2011 25 955 3 5/1/2011 11/30/2011 50 977 3 1/1/2011 12/31/2011 100
output
DC store St Date End date Priority Rank 977 3 1/1/2011 4/30/2011 100 1 955 3 5/1/2011 6/29/2011 50 1 977 3 5/1/2011 6/29/2011 100 2
[code]....
Scenario 3 This works similar to scenario 2
DC Store St Date End date Priority 966 3 2/1/2011 12/31/2011 25 955 3 1/1/2011 12/31/2012 50 977 3 5/1/2011 06/31/2011 100
output
DC store St Date End date Priority Rank 955 3 1/1/2011 1/31/2011 50 1 966 3 2/1/2011 12/31/2011 25 1 955 3 2/1/2011 12/31/2011 50 2 977 3 5/1/2011 6/30/2011 100 3 955 3 1/1/2012 12/31/2012 50 1
Note: Number of records in the input can vary and ther can be duplicates in the date interval
In employees table I am having employee_name column data as follows
Aaron, Mrs. Jamie (Jamie)Aaron, Mrs. Jenette (Jenette)Abbott, Ms. Rachel (Rachel) Breton, Mr. Jean Britz, Mrs. Sarie (Sarie) --> Now, I want to display the employee name like "Mrs. Jamie" (with out Surname and with out bracket included text),-->
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?
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel ( start_date, end_date ) AS SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE A | 2010 11 01 | 2011 02 11 B | 2011 01 20 | 2011 02 15 C | 2011 03 13 | 2011 04 10 D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE X1 | 2011 02 16 | 2011 03 12 X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]------------------------- B : ------[---]------------------------ C : --------------[---]---------------- D : ----------------[------]-----------
Travel Date : -[--------------------------------]--
BO Date A : ----[------]------------------------- BO Date B : -------------------------[---]------- BO Date C : ----------------[---]---------------- BO Date D : ------------------[------]-----------
Result X1 : -[-]------------------------------- Result X2 : -----------[--]-------------------- Result X3 : -----------------------------[--]--
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.
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: -
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.
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
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
"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)?
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] ...........
