SQL & PL/SQL :: Finding Individual Dates Having Date Plus 2 Days
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.
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.
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.
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.
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)?
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 not defined the table ( I only have privileges to query data).
I am unable to copy and paste my real code here, and the actual results from the run, as my company will fire me if I do so... so here is how things approximately look like (tried to keep it as real as possible).
Let's say that the table CYCLE has client numbers (clientid), cycle number (cycleno), date of visit (visdt).
I am trying to create a query to calculate how many days there are between each two consecutive visits/cycles for a single client(let's say 1200004)
What I need to do is take 2 dates from a table named 'projects' and insert the number of days between them into a table named 'time_record'.How do I go about this?
I have a table with starting date and ending date.
I want to fetch monthwise days for given two dates
IE starting 25/12/2009 ending 25/03/2010
Result should like below.
Dec-09 7 Jan-10 31 Feb-10 28 Mar-10 25
I have tried this but it is not giving me the result which want..
select to_char(thedate,'mon-yy') mnth,count(to_char(thedate,'mon-yy')) days from (SELECT TRUNC(to_date('25/12/2009','dd/mm/yyyy'),'Y')+ROWNUM-1 THEDATE FROM ( SELECT 1 just_a_column FROM dual CONNECT BY LEVEL <= 366
I'd really like to get the difference expressed in hours, minutes and seconds. I tried:
, TO_CHAR(fcr.actual_completion_date, 'HH24:MI:SS') - TO_CHAR(fcr.actual_start_date, 'HH24:MI:SS') diff_But that doesn't work - I get an ORA-01722: invalid number error.
I also tried:
, TO_CHAR(fcr.actual_completion_date - fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') diff_But got an ORA-01481: invalid number format model error
how to caluclate days between two dates of single timestamp filed and with this
query Select * from m_activity_transaction where actn_opp_id in ( Select actn_opp_id from m_activity_transaction where ACTN_ACTV_ID = 218 Group by actn_opp_id
[code]...
and i nedd to caluclate no.of days between two dates like 27-JAN-12 11.06.20.000000 AM and 08-FEB-12 05.32.54.000000 PM where actn_id is unique AND ACTN_OPP_ID IS NOT UNIQUE.
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 have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.
The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:
select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL)) from dwt_dvt_work_time where dwt_create_date > sysdate - 30 and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id
Here's the function: CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE) RETURN NUMBER IS total_days NUMBER(11,2); holiday_days NUMBER(11,2); [code]....
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.
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.
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.
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
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?
I have a Identifier column with start and stop dates along with description .
Two dates are separated by '-'. But the position of that character(-) is not constant always. Depending on the instr function I am able to divide the start and stop dates. But I am getting the performance problem because of huge data
I think the same logic will be implemented by regular expression also . How to write the equivalent logic by using regular expressions
I need to find the closest Date that matches a Particular Date. The Closest Date from the Group may be less than or greater than the Date I am trying to find.
I have two columns: VISIT_DATE and ACTUAL_DATE. The VISIT_DATE columns has many records with different dates while the ACTUAL_DATE column would only have one record per Student ID.
Here is an example of dates:
Visit Date Actual Date ========================== 01-APR-09 19-MAR-10 16-NOV-09 19-MAR-10 17-MAR-10 19-MAR-10 21-MAR-10 19-MAR-10 04-APR-11 19-MAR-10 15-JUN-11 19-MAR-10 19-SEP-11 19-MAR-10 24-FEB-12 19-MAR-10
The closest date to 19-MAR-10 are in fact 17-MAR-10 and 21-MAR-10. I would in that case need to pick up both records.
Is there a way I can generate a list of date using a simple SELECT statement to generate all dates between to date points? For example between SYSDATE AND SYSDATE-7?
One way I know is have a table and run PL/SQL script to put all dates in it and query that but was wondering if it can be achieve via SQL SELECT query at all without creating a table (querying DUAL).
In a table I have a column update_date and its type is DATE. Sample values from this column are as follows. I am using the following query to select all update_date lie between sysdate and sysdate-90.
select update_date from table1 where update_date between sysdate and sysdate-90
The above query retrun no data even data is there in the table for this range.
I have a table with three columns X, Y and Z.The data in Column z is of the type 20/1425SE, 13/1235NW.Is there a way to split the data entries where Z LIKE '%/% and insert them as two separate rows.
I don't want to have any entries with '/'. Can these be deleted along with splitting the data entries?
CREATE TABLE TEST(empno VARCHAR2(4), empname VARCHAR2(50), empstd NUMBER(2)) insert into test values(0001,'A',2); insert into test values(0002,'B',5); insert into test values(0003,'C',2); insert into test values(0004,'D',7); insert into test values(0005,'E',9);
Now I want to get empno for the particular employees based upon subscript and I have written below
DECLARE CURSOR cur_rec IS SELECT * FROM TEST; TYPE cur_type IS TABLE OF cur_rec%ROWTYPE; v_cur_rec cur_type; BEGIN OPEN cur_rec; FETCH cur_rec BULK COLLECT INTO v_cur_rec; [code]....
how to access individial item froma table of rowtype data.
CM: Added [code] tags, please do so yourself next time, see the orafaq forum.
