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.

seq date
------------------------------
101.01.10

205.01.10
306.01.10

410.01.10
512.01.10

613.01.10
714.01.10
815.01.10

916.01.10
1018.01.10

1119.01.10

[Code]...

The result should be (Don't use Pl/Sql)

seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10

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.

View 2 Replies


ADVERTISEMENT

Finding Individual Dates Having Dates 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.

seq date
------------------------------
101.01.10

205.01.10
306.01.10

410.01.10
512.01.10

613.01.10
714.01.10
815.01.10

916.01.10
1018.01.10

1119.01.10
1220.01.10
1321.01.10

1423.01.10

1526.01.10
1627.01.10

1729.01.10
1831.01.10

The result should be (Don't use Pl/Sql)

seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10

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.

View 2 Replies View Related

Get Date Difference In Days Between Two Dates In Reports?

Sep 11, 2010

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)?

what if i select y? it will override three files

what if i select n? default option

View 2 Replies View Related

Server Administration :: Overlapping Days Between 3 Date Ranges To Determine Active / Inactive Days

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

SQL & PL/SQL :: Days Between Dates

Mar 14, 2012

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)

clientid / cycleno / visdt
---------------------------
1200004 / 1 / 10OCT2011
1200004 / 2 / 31OCT2011
1200004 / 3 / 21NOV2011
1200004 / 4 / 05DEC2011
1200004 / 5 / 03JAN2012
...
1000005 / 1 / 04NOV2011
1000005 / 2 / 03DEC2011
1200004 / 1 / 10JAN2012
1200004 / 2 / 15FEB2012
.
.
.

The code below is the only one that kind of seemed to work, but it is definitely not giving me the right results.

SELECT cycleno1, visdt1, cycleno2, visdt2, to_date(visdt1) - to_date(visdt2) days

FROM (SELECT clientid, cycleno cycleno1, visdt visdt1,
LEAD (visdt, 1) OVER (ORDER BY cycleno) visdt2
FROM CYCLE) a

[Code]....

I am getting a mess of a result of the kind:

cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 1/ 18OCT2011 / -8
1 / 10OCT2011 / 2/ 18OCT2011 / -8
1 / 10OCT2011 / 3/ 18OCT2011 / -8
1 / 10OCT2011 / 4/ 18OCT2011 / -8
1 / 10OCT2011 / 5/ 18OCT2011 / -8

I need my result to look like:

cycleno1 / visdt1 / cycleno2 / visdt2 / days
--------------------------------------------
1 / 10OCT2011 / 2/ 31OCT2011 / 21
2 / 31OCT2011 / 3/ 21NOV2011 / 22
3 / 21NOV2011 / 4/ 05DEC2011 / 15
4 / 05DEC2011 / 5/ 03JAN2012 / 30
5 / 03JAN2012 / / /
.
.
.

View 3 Replies View Related

Number Of Days Between Dates?

Jan 14, 2008

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?

View 1 Replies View Related

SQL & PL/SQL :: Month Wise Days For Between Given Dates?

Apr 28, 2010

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

[code].....

View 3 Replies View Related

SQL & PL/SQL :: How Many Days Thursday And Friday Between Two Dates

Nov 13, 2011

I want function return me how many days Thursday and Friday between two dates.

View 1 Replies View Related

SQL & PL/SQL :: Show Number Of Days Between Two Dates?

Apr 18, 2013

i want to create function to show number of days between to days such as number of Friday days between to dates

View 13 Replies View Related

PL/SQL :: Oracle Dates And Working Days

Sep 5, 2012

Is there an Oracle date function that ignores public bank holidays and calculates working days only?

View 4 Replies View Related

PL/SQL :: Finding Difference Between Two Dates In Hh:mm:ss Format

Sep 20, 2012

I am running some SQL to try to find how long, in hours:mins:seconds a concurrent request in Oracle EBS takes to run.

I have a basic start here:

SELECT fcr.actual_start_date start_
, fcr.actual_completion_date end_
, fcr.actual_completion_date - fcr.actual_start_date diff_1
-- , TO_CHAR(fcr.actual_completion_date, 'HH24:MI:SS') - TO_CHAR(fcr.actual_start_date, 'HH24:MI:SS') diff_
FROM applsys.fnd_concurrent_requests fcr
[code].......    

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

View 2 Replies View Related

PL/SQL :: How To Calculate Days Between Two Dates Of One Timestamps Field

Sep 25, 2012

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.

View 6 Replies View Related

SQL & PL/SQL :: Split A Date Into New Dates According To Black Out Dates?

Mar 10, 2011

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 : ----------------[------]-----------

Result :

X1 : -----------[--]--------------------
X2 : -----------------------[--------]--

Sample 2:

Travel Date : -[--------------------------------]--

BO Date A : ----[------]-------------------------
BO Date B : -------------------------[---]-------
BO Date C : ----------------[---]----------------
BO Date D : ------------------[------]-----------

Result X1 : -[-]-------------------------------
Result X2 : -----------[--]--------------------
Result X3 : -----------------------------[--]--

How can I do it using PL SQL ?

View 5 Replies View Related

Function To Find Business Days Between Two Dates - Query Tuning

Mar 17, 2011

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]....

View 1 Replies View Related

SQL & PL/SQL :: Complex Days Computation / Multiple Number Of Same Transaction Dates

May 22, 2010

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.

----------------------------------------------------------------------------------------- -------
Transaction Narration DebitCreditBalanceNo of DaysProduct
DateDifference
----------------------------------------------------------------------------------------- --------
01/04/2009Opening Balance 45020450277346654
17/06/2009ByAmt5044521253424
29/06/2009By Amt1004352156678912
02/12/2009By Amt424310119476000
[code]....

improve the above code and get the desired output result.

View 5 Replies View Related

SQL & PL/SQL :: Oracle Stored Function / Procedure To Calculate Number Of Working Days Between Two Dates

Feb 17, 2010

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.

View 7 Replies View Related

SQL & PL/SQL :: Days Difference - Start And End Date

Jul 7, 2012

Tables
--------
ROWID START_DATEEND_DATE
101/26/2012 00:00:0001/31/2012 00:00:00
201/26/2012 00:00:0002/02/2012 00:00:00
302/03/2012 00:00:0003/31/2012 00:00:00

How to find out the days difference in sql query, start date of next records and end date of previous records as highlighted on blue color

query out put should give as below

start_date + next record ,end_date - Previous records should give the difference of
6
1

View 12 Replies View Related

PL/SQL :: Finding The Maximum Truncate (date)

Jul 31, 2012

I've the following data set

with t as
(
select 1 cor_id , 'SR' rt_cd,TO_Date( '05/12/2010 01:12:19 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 2 cor_id , 'SL' rt_cd,TO_Date( '05/12/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 3 cor_id , 'SR' rt_cd,TO_Date( '04/10/2010 02:11:15 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 4 cor_id , 'SL' rt_cd,TO_Date( '04/10/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual
)
select t1.*
from t t1for each iss_id =100 ,

there could me more than one record in the above data set, The requirement is i need to get the records where the trunc(dt) is maximum

I need to get 2 records with cor_id= 1 and 2.

how can i get those records

View 3 Replies View Related

Display Data From Current Date To Next 15 Days?

Jan 24, 2007

I have a Employee data as below with EID and DOB

EID DOB
----------------------
1 03/01/1950
2 14/01/1950
3 27/01/1950
4 02/02/1950
5 04/03/1950
6 12/03/1950
7 28/03/1950
8 28/02/1951
9 08/03/1951
10 28/03/1951

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.

View 8 Replies View Related

SQL & PL/SQL :: Query To Get Last 7 Days Of Record From Today Date

Mar 13, 2013

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

Here is my query, dont mind the ****,$$$ or ####

SELECT INFORMENT.PRODUCT_OFFER_PURCHASE.*******_#######, INFORMENT.INVOLVED_PARTY.INVOLVED_PARTY_ID, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_ADDED, 'YYYYMMDD'), INFORMENT.DEPOSIT_$$$$$$$.BAL_LEDGER_CURRENT, INFORMENT.PRODUCT_OFFER_PURCHASE.INTEREST_RATE, To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_OPEN, 'YYYYMMDD'), To_Char(INFORMENT.PRODUCT_OFFER_PURCHASE.DATE_CLOSE, 'YYYYMMDD'), INFORMENT.PRODUCT_OFFER_PURCHASE.*******_STATUS_CODE, [code]..........

View 16 Replies View Related

SQL & PL/SQL :: Identify Due Date For A Transaction - Working Days

Jul 5, 2011

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?

View 2 Replies View Related

SQL & PL/SQL :: Finding Date Value In Given String By Using Regular Expression

Jul 3, 2012

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

create table REG_EXP_TEST
(
TRANS_ID NUMBER(10),
TRANS_IDENTIFIER VARCHAR2(250)
) ;
insert into REG_EXP_TEST
(TRANS_ID, TRANS_IDENTIFIER)
values
[code].......

View 3 Replies View Related

PL/SQL :: Closest Date From A Group Of Dates

Dec 19, 2012

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.

View 4 Replies View Related

PL/SQL :: Generate All Dates Between To Date Points

Sep 8, 2012

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).

View 5 Replies View Related

SQL & PL/SQL :: Select Date Values From A Table Between Two Dates?

Feb 16, 2010

Oracle 10g

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.

Update_date

11-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10
08-FEB-10

[code]...

View 7 Replies View Related

SQL & PL/SQL :: How To Convert Days Into Weeks And Days (if Any) Oracle 9i

Apr 5, 2011

want to write Procedure to return weeks and days from given set of days.

let suppose we have 72 days to return weeks and days then return should be 7 weeks and 2 days.Can i use date function Or ?

View 5 Replies View Related

SQL & PL/SQL :: Sequence Into Individual Records

Feb 23, 2010

create table x (
field1 varchar2(10) );
insert into x values ('001-002');
insert into x value ('0150-0152');
insert into x value ('0100-0101');

create table y (
field varchar2(6));

recortd in table y
001
002
0150
0151
0152
0100
0101

View 15 Replies View Related

Forms :: Individual Tab Page Bar Color?

Aug 12, 2011

Can i set individual tab page bar color?

Like the attached screenshot..

View 3 Replies View Related

Insert Separated Data As Individual Rows

Sep 3, 2008

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?

View 3 Replies View Related

SQL & PL/SQL :: Access Individual Item From A Table Of Type ROWTYPE

Jun 1, 2010

Script for test:

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.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved