SQL & PL/SQL :: Difference Between Business Days And Hours?

Sep 19, 2010

format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate
vcActivityName = Process Request
usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)

i need to calculate time elasped for that type of activity following are the rules:

(If Process Request is the activity)
Working Days: Monday through Saturday
Hours of Operation: 9AM 5PM

only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be

6+ 8 + 1 = 15 hours + minutes.
format of date time: 2010-09-17 14:50:51.150
vcActivityName = Process Request
Don't worry about process request..

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Calculating The Business Hours

Aug 15, 2011

I have a field in Customers table called shipeddate....

I wnat to check the number of hours an item which has a shipeddate is in the store room to the current datetime...

But the business hrs of the store room are from 8am-5pm..

So when a shipped date is 4pm on MOnday

and i am checking on 9 am Tuesday the number of hrs shud be 1(4-5 of Monday)+1(8-9 of tuesday) =2hrss..

How can i achieve this...

View 2 Replies View Related

PL/SQL :: Calculate Business Hours Between Two Dates Within A Cursor

Jul 24, 2013

I need a similar function to determinate difference between two dates, but i need other business hours; Monday - Friday: 9:00 - 21:00 (this is OK)Saturday: 09:00 - 14:00  (and this is my problem, how to add this condition in this function) 

View 4 Replies View Related

PL/SQL :: Query To Calculate Business Days

May 20, 2013

i have table name order table . in that i have column name install date,order date.

the difference bet ween order date and install date should be greate than or equal to 3 working days.

fri and sat are holidays.

i need one query to find the difference betwwen orderdate and install date is greate than 3 working days

View 3 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

Forms :: Days Hours Min And Seconds Calculation

Nov 5, 2010

I am able to run this query in Sql Prompt.

DEFINE Today = TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')

SELECT TO_CHAR(hiredate,'DD.MM.YYYY:HH24:MI:SS') "Hiredate",
TO_CHAR(&Today,'DD.MM.YYYY:HH24:MI:SS') "Today",
trunc(86400*(&Today-hiredate))-60*(trunc((86400*(&&Today-hiredate))/60)) "Sec",

[Code]...

It gives proper / desired results. I want to use it on my Form, how can it be done?

View 3 Replies View Related

SQL & PL/SQL :: Get Difference And Convert Integer To Time In Hours And Minutes

Jun 14, 2012

How to get Time Difference between two DateTime Columns in Oracle 10g ?

View 10 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

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

Syntax To Calculate Number Of Days Difference

Sep 27, 2010

setting up the query/correcting the syntax below so that it calculates the 'number of days difference' between whatever the 'Biggest Date' field value is and whatever the 'current date' is using the 'sysdate'. So far, I've only managed to get the query to calculate the number of days difference (days past due) between the 'need date' and 'estimated delivery date'.

CODESELECT
To_Date(need_date, 'YYYYMMDD') Need_Dt,
To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Biggest_Date,  
To_Date(need_date, 'YYYYMMDD') - To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Date_Diff
        
FROM tableT

WHERE
need_date <=  (Case when estimated_delivery > ' ' THEN  estimated_delivery ELSE need_date END)

ORDER BY Date_Diff ASC

View 6 Replies View Related

Manually Find Difference In Days And Multiply By 24

Apr 25, 2011

1) First Problem:

Difference in hours is supposed to be 35 but it comes as 34.When i manually find the difference in days and multiply by 24 it comes as 34.999999999 and floor converts it as 34.. But when i execute the difference between dates alone and convert to 24 it shows as 35. So when i apply floor to this, it should be 35 instead of 34 right?

SQL> select (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss'))
3 from dual
4 ;
[code].....

1) Second Problem:

For the following query, difference of the dates in minutes is 2100.. so mod(2100,60) should give 0 but it's giving 60.. Separately if if put mod(2100,60) it is giving as 0 only.

SQL> SELECT MOD(((to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440),
3 60) AS duration,
4 (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
5 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440 as diffinmin
[code]....

View 2 Replies View Related

PL/SQL :: Difference Between Days Column And Day1 Column

Oct 25, 2013

I used this query: 

SELECT to_char(return_date_time,'dd/mon/yyyy hh24:mi:ss') Test,DECODE ('I', 'I', DECODE (1, NULL, NULL, 0, NULL, SYSDATE + 1)) Days,DECODE ('I', 'I', SYSDATE + 1) Day1FROM fm_curr_locnWHERE patient_id = 'DU00002765'  output:  TestDays Day126/oct/2013 00:00:00 26-OCT-13 10/26/2013 3:06:59 PM

 I want to know the difference between Days column and Day1 column and why the days column didn't show the time .

View 13 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 :: 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 :: First Business Day Of A Given Month?

Dec 6, 2010

I need to calculate first business day of a given month . Below is complete explanation

Business day=sould not include weekends and holidays.

In a table say ACTIVITY_XX I have all the month begin dates say 01-JAN-2010,01-FEB-2010,01-MAR-2010,01-APR-2010 and so on..and I have a HOLIDAY table where all the holidays are stored.

So using the above info I need to calculate the first business day for a given month. I guess this cannot be done by using a simple SQL query? I was wondering how could it be written using a PL/SQL function.

I'll be passing the month begin date as parameter..so the function should return the first business day for that month.

View 16 Replies View Related

PL/SQL :: Order By Business Units?

Mar 12, 2013

i have business units for which i have multiple department ids so just wanted to have dept ids order by business units

View 12 Replies View Related

Business Rules For Employee Table

Aug 19, 2013

We need some transformation rules on Source employee table which comes from SAP and want to load in Target table.Basic idea is we need to calculate time when position was not held by sub owner so . . . Etc

Source looks like:

POSITION_TCD START_DT END_DT SUBTYPE POSI_HOLDER

Example 1:

10005822 10/16/2003 11/20/2008 A008 105404
10005822 10/16/2003 3/31/2009 A999 105404
10005822 6/23/2008 7/5/2009 A008 124530
10005822 11/21/2008 8/31/2009 A008 105404
10005822 8/31/2009 4/16/2010 A008 105323
10005822 4/19/2010 12/31/9999 A999 131995
10005822 7/5/2010 12/31/9999 A008 131995

Example 2:

10084408 5/3/2010 12/31/9999 A008 130591
10084408 3/21/2011 5/17/2011 A008 132725
10084408 5/3/2010 1/2/2011 A999 130591

After business rules data should look like:

Target

Example 1:

POSITION_TCD POSI_HOLDER SUBS_OWNER EMP_ASSI_TYPE START_DT END_DT

10005822 105404 105404 Hold=Subs 10/16/2003 11/20/2008
10005822 124530 105404 Hold<>Subs 6/23/2008 3/31/2009
10005822 124530 No Substantive No Substan 4/1/2009 7/5/2009
10005822 105404 No Substantive No Substan 11/21/2008 8/31/2009
10005822 105323 No Substantive No Substan 8/31/2009 4/16/2010
10005822 No Holder 131995 No Holder 4/19/2010 7/4/2010
10005822 131995 131995 Hold=Subs 7/5/2010 12/31/9999

Example 2:

10084408 130591 130591 Hold=Subs 5/3/2010 1/2/2011
10084408 130591 No Substantive No Substan 1/3/2011 12/31/9999
10084408 132725 No Substantive No Substan 3/21/2011 5/17/2011

View 1 Replies View Related

Install Oracle E-Business Suite R12 On Mandriva - Domain Name?

Sep 9, 2010

I want to try installing Oracle E-business suite on Mandriva But when I try e.g. domainame I get blank.

A) How can I check my domainname ?How can I set it?Here is the hosts file

[Select all] [Show/ hide]
127.0.0.1 localdomain
192.168.0.2 david.domain.com david

View 3 Replies View Related

Reports & Discoverer :: Access To Multiple Business Areas?

May 5, 2011

I have been asked to see why a sister company cannot access multiple business areas through a single responsibility. At present, if they log in through a GL responsibility they can see all GL folders, items and the returned data. If they then go to the AP business area, they can see the folders and items, but when they run a report they get an error message 'Query returned no data' - but if they run the same query using an AP responsibility they get the correct results. I have looked at their user set up, and it seems fine i.e. has access to all business areas and required priveledges.

View 4 Replies View Related

SQL & PL/SQL :: How To Compare Hours

Jan 10, 2011

I want to write a function that gets:

1. event_date (dd/mm/yyyy)
2. event_start_time (??/??/???? HH24:MI)
3. event_end_time (??/??/???? HH24:MI)

I got a table called EVENTS that got 2 fields:

1. event_date (dd/mm/yyyy HH24:MI)
2. event_end_date (??/??/???? HH24:MI)

and want to check if there is an event in my EVENTS table that occurs in the same dd/mm/yyyy as the input, and can disturb the input event times. means:

input.event_start_time is between EVENTS.event_date
and EVENTS.event_end_date
and
input.event_end_time is between EVENTS.event_date
and EVENTS.event_end_date

but to compare only the hours here! (HH24:MI)
because the date (dd/mm/yyyy) is checked before..

I don't know how to cut only the hours out of the date and compare them, and don't know how to write the whole function.

View 2 Replies View Related

SQL & PL/SQL :: Displaying 24 Hours Data

Dec 27, 2010

I have a table which will the following type of data

"COL1""COL2"
1001"27-DEC-2010 02:00:00"
1002"27-DEC-2010 07:00:00"
1003"27-DEC-2010 09:00:00"
1004"27-DEC-2010 02:00:00"
1005"27-DEC-2010 12:00:00"

Here you can see that we have data for 27th Dec 2010 02,07,09 and 12 hours. I want a query which will show the full 24 hours data even if it doenst have any records. like the following,

COL1 COL2
0 2010122701
1001 2010122702
1004 2010122702
0 2010122703
0 2010122704
0 2010122705
0 2010122706
1002 2010122707

View 2 Replies View Related

SQL & PL/SQL :: How To Get Correct Minute Between Two Hours

Oct 22, 2012

i am using one query but not getting correct minutes.

here is my query:

v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS');
v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);

here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00
and v_totalHrsMin1 datatype is number.

here i should get value 370.
but i am getting value 350.

View 3 Replies View Related

SQL & PL/SQL :: Calculating Working Hours?

May 8, 2010

I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours

The following query take 63.499 sec

SELECT sql_calc_found_rows gstime,
MAX(stoptime) AS mx,
MIN(starttime) AS mn,

[Code].....

View 6 Replies View Related

PL/SQL :: Convert Number To Hours

Jan 1, 2013

I want to convert below MS-SQL query in oracle 10g.

for eg:

Select numasdate,
Cast(numasdate / 60 as Varchar) + ' hours ' +
Cast(numasdate % 60 as Varchar) + ' minutes'
as [TotalHoursAndMinutes]
From
#SampleTable

Output:

9436 157 hours 16 minutes
537 8 hours 57 minutes
9323 155 hours 23 minutes
12525 208 hours 45 minutes

View 8 Replies View Related

PL/SQL :: Generate Hours Range

Oct 4, 2012

I have a table with a date field. This field storage dates with hours like this:

01-08-2012 8:30:00
01-08-2012 8:15:00
01-08-2012 9:30:00
01-08-2012 9:40:00
02-08-2012 8:30:00
02-08-2012 9:30:00
02-08-2012 9:34:00
...

I have to generate a report with the day and the frequency :

__Day_______ Hour Range__CountRecords
WEDNESDAY 8 - 9 2
WEDNESDAY 9 - 10 2
THURSDAY 8 - 9 3

The block of the hour must be 1 hour (8-9,9-10 and so on)

how generate the hours range.

My database oracle version is 8i. (I know that is very old but I can't change because It´s a legacy system).

View 5 Replies View Related

PL/SQL :: How To Get Added Hours From Results

Jun 14, 2012

I am not getting added hours from results.

SELECT audittimestamp + interval (SELECT      EXTRACT(TIMEZONE_HOUR
FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.

View 7 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

DB Upgrade From 11.1 To 11.2 - SQL Statements Running For Hours

Feb 16, 2011

Recently we have upgraded from 11.1 to 11.2 . But after upgrade SQL statements that are running fine in 11.1 was running for hours in 11.2. Statistics are collected 100%...

View 2 Replies View Related

Fetching Data Older Than 2 Hours

Mar 11, 2013

I am fetching data which are older than 2 hours from now. TRANSACTION_TIME is varchar2 field in "MM/DD/RRRR HH:MI:SS PM" format.

SELECT * FROM TRANSACTION_DETAILS
WHERE TO_DATE(TRANSACTION_TIME,'MM/DD/RRRR HH:MI:SS PM') <(SYSDATE - 2/24);

Is there anything wrong in the query.

View 2 Replies View Related

SQL & PL/SQL :: How To Validate Dynamic Hours Value Using Decode

Nov 1, 2013

I want to validate dynamic hours value using decode in sql...

If value is greater than 24PM it should display some statement...

if value is less than 0AM it should display some statment...

View 10 Replies View Related







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