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
ADVERTISEMENT
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
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
View Related
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
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
Oct 9, 2013
I want to find the months and days between 2 dates. For Eg. Date-1 : 25-Aug-2013Date-2 : 23-Oct-2013 If we consider every month as 30 days it should give 25-Aug-2013 to 30-Aug-2013 = 6 days 01-Sep-2013 to 30-Sep-2013 = 1 Month 23-Oct-2013 to 30-Oct-2013 = 8 days Total = 1 month and 14 days
View 33 Replies
View Related
Apr 8, 2013
I have a customers table like
ORDER_ID CUSTOMER_ID ORDER_DATE
__________________________________
1 | 100 |25-JAN-13
2 | 100 |25-JAN-13
3 | 100 |24-JAN-13
4 | 101 |23-JAN-13
5 | 101 |22-JAN-13
6 | 102 |21-JAN-13
7 | 102 |21-JAN-13
I would like to find the customer_ids who placed orders for two days.
View 3 Replies
View Related
Oct 1, 2012
We have a requirement where we need to pay allowance for the employees based on their number of working days. Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.
We have a fixed value for per month 300 Dirhams. But the Number of Days on March s 31 and Number of days in April is 30. So per day allowance for March day would be 300/31 and April would be 300/30.
We are looking for logic opr query which calculates first eh number of days in each month ( across months) and then calculate as below
Number of Working days in March is 31 - 3 + 1 = 29
Allowance A1 = (300 * 29 )/31
Number of Working days in April is 5 ( this also needs to find logical I am guess )
Allowance A2 = (300 * 5 )/30
Then A1 + A2.
The A(n) would be the total allowance where provided the number of month across.
View 10 Replies
View Related
Mar 10, 2012
Main Aim : To find all those id's who have taken all the tests within a rolling window of 45 days.
I have a table "MBS_FIRST_DATE" with the following data :This table has the patients who have the test along with the first date..This table is derived such that it has only one record with the first date of the test irrespective of the test.
create table MBS_FIRST_DATE
(
medical_record_number VARCHAR2(600),
requested_test_name VARCHAR2(39),
result_date DATE
[code]..
Process :will be explaining with a patient id :
1) Consider the patient 1001274 from mbs_first_date table.
2) This patient has an date of July 08th 2008 & test SBP from first table. (keep this test an an anchor).
3) For the patient above loop through the all_recs table with test & result date ordered for the patient. (excluding SBP)
4) The first record we have is CHL with 08/05/2009 (May 8th 2009)..
5) Since this record is not within 45 days from SBP date for the patient..we go to the next record of SBP for the patient.
6) The next record for SBP is 11/05/2009 (May 11th 2009) .
7) Consider the CHL date again which is with 08/05/2009 (May 8th 2009)..
Since both are within 45 days ..store both the values keeping SBP date as an anchor date as it's the test that's having minimum date from table 1. Even though there is one more CHL date which is within 45 days from SBP we don't care about it.
9) Go to the next test for the same patient which is DBP..
10) The DBP first date is July 08th 2008..
11) Since it's not within 45 days from previously stored SBP date (11/05/2009) ignore the record.
12) GO to the next record which is 10/05/2009..as this is within 45 days from SBP & already CHL (stored date) is within 45 days..Grab all the 3 dates as all are within 45 days from anchor date (SBP date).
SO the o/p will be
1001274 SBP 11/05/2009
1001274 CHL 08/05/2009
1001274 DBP 10/05/2009
Code which I wrote :I know some where I am missing the loop
declare
V_ID1 VARCHAR2(200) := '';
V_TEST1 VARCHAR2(200) := '';
V_DATE1 DATE := NULL;
[code]...
View 2 Replies
View Related
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
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
Feb 25, 2013
I want to find difference between the objects(tables(columns,constraints), views, sequences) in two schemas. One schema is staging and one is development . In development lot of changes are made , Is there a script or a opensource tool which will bring out the difference in columns, constraints in each table and sequences , views etc.
View 2 Replies
View Related
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
Aug 21, 2010
I need to query a table to get the time difference based on different dates.
SAMPLE DATA:
JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12
in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as
01:23:12-00:56:00 and the difference should be in minutes.
for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.
View 2 Replies
View Related
Aug 1, 2012
how to find the difference between time stamp in two variables.
Say in a java file i am getting start time and end time i am storing it in a string variables now i need to know what is the time difference of them in millisecond
Srting1 of java will give me some thing like
StartTime =2012-08-01 15:14:20.36 +5:30
Srting2 of java will give me some thing like
EndTime =2012-08-01 15:14:21.254 +5:30
I need to take the millisecond difference between them the code i have written in java and connecting oracle database and doing some logic's in java so i need to capture start time and end time of code.!
View 22 Replies
View Related
Jul 20, 2013
I have two tables tab1 and tab2 with below data Now requirement is to find difference between two table with respect to column "serial_no" which exist only in table tab1 so as per below at serial_no 2 CC4 in tab1 but CC2 in tab2 so this is different how to dump out this data
TAB1
SQL> select * from tab1;
PARENT CHILD ROW_ID SERIAL_NO
-------------------- -------------------- ---------- ----------
P1 CC1 101 1
P1 CC4 104 2
P1 CC5 105 3
P1 CC2 102 4
P1 CC3 103 5
TAB2
SQL> select * from tab2;
PARENT CHILD PREVIOUS_ROWID ROW_ID NEXT_ROWID
-------------------- -------------------- -------------- ---------- ----------
P1 CC1 0 101 102
P1 CC2 101 102 103
P1 CC3 102 103 104
P1 CC4 103 104 105
P1 CC5 104 105 0
create table tab1(parent varchar2(20), child varchar2(20),row_id integer,serial_no integer);
insert into tab1 values('P1','CC1',101,1);
insert into tab1 values('P1','CC4',104,2);
insert into tab1 values('P1','CC5',105,3);
insert into tab1 values('P1','CC2',102,4);
insert into tab1 values('P1','CC3',103,5);
create table tab2(parent varchar2(20), child varchar2(20),previous_rowid integer,row_id integer,next_rowid integer);
insert into tab2 values('P1','CC1',0,101,102);
insert into tab2 values('P1','CC2',101,102,103);
insert into tab2 values('P1','CC3',102,103,104);
insert into tab2 values('P1','CC4',103,104,105);
insert into tab2 values('P1','CC5',104,105,0);
View 8 Replies
View Related
Jul 8, 2010
i want to find out difference between two dates in day-month-year. How can i do it?
For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'
View 4 Replies
View Related
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
Aug 21, 2012
i need to multiply with previous row value?
my table have about to 100 columns and lakhs of rows
Ex:
date adj
------ -----
8/21/2012 1
1/1/2012 1
12/1/2011 0.5
8/1/2011 0.5
[code]....
My requirement is multiply the existing adj by adj
adj value coming as 0.5
present year always 1
Ex:
date adj
------ -----
8/21/2012 1
1/1/2012 1
.
.
12/1/2011 0.5
8/1/2011 0.5
[code]....
View 8 Replies
View Related
May 26, 2011
We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.
View 5 Replies
View Related
Oct 27, 2011
I have the task that I have to determine the number of parts that need to be produced based on the number of products sold for the day (each product consists of many parts).
I am using SQL 11g Express.
The report would look something like this:
{OrderDate PartID PartDesc NumOfParts(Total for that day)
10-24-2011 2001 12" X 12" Solid Shelf 108
10-24-2011 2003 12" X 24" Solid Shelf 32
10-24-2011 3001 96" Side Panel 50
[code].......
My issue is, I can't get the equation right to produce the total number of parts. I think I need to multiply ProductPart.NumOfParts by SUM(CustOrder.Qty) Group by CustOrder.SKU.
Below I have what the calculations should look like
SKU PartID Total Parts Needed by SKU
DVCK1212 1001 42 (7 orders * 6 shelves)
DVCK1812 1002 0 (NONE)
DVCK2412 1003 42 (7 orders * 6 shelves)
DVCK3012 1004 78 (13 orders * 6 shelves)
[code].......
Here's my script:
/**PRODUCT**/
CREATE table Product
(
SKU VARCHAR2(10) NOT NULL,
ProdDesc VARCHAR2(50) NOT NULL,
Price NUMBER(5,2),
PRIMARY KEY(SKU)
);
[code].......
Query 1
SELECT ProductPart.NumOfParts
FROM ProductPart,
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder
[code].......
Result: I am only getting a total of 2 parts
Query 2
SELECT ProductPart.Qty,
ProductPart.PartID,
ProductPart.PartDesc
(SELECT COUNT(CustOrder.SKU)
FROM CustOrder
GROUP BY CustomerOrder.SKU)TotalProducts,
[code].......
ORA-00936: missing expression
Query 3
SELECT Part.PartDesc.
ProductPart.NumOfParts
FROM ProductPart, Part
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder
[code].......
ORA-00933: SQL command not properly ended
Query 4
SELECT o.OrderDate AS "Date Ordered",
o.OrderDate + 5 AS "Date Due",
pp.PartID AS "Part No.",
pp.NumOfParts * COUNT(o.SKU) AS "Qty"
FROM CustOrder o
[code].......
ORA-00934: group function is not allowed here
View 2 Replies
View Related
Nov 26, 2012
I am writing a query and I am trying to multiply some of the columns with *1.50 and 0.75.
The columns I am trying to multiply are coke_rebate.volumecsd and coke_rebate.volumencb. I am getting an error in oracle sql command from web "ORA-00937: not a single-group group function"
Here is the code I am writing:
SELECT CUSTOMERS.CUSTID,
CUSTOMERS.MEMBERID,
CUSTOMERS.BNAME,
COKE_REBATE.COKEID,
SUM(COKE_REBATE.VOLUMECSD*1.50) "TOTALCSD",
[code]........
View 7 Replies
View Related
Feb 27, 2013
Have 2 small tasks for regexp..where we need to extract numbers from the text & multiply & get the result.
Input field varchar2 : 5x a day x 10 days
Output : 5 * 10 = 50
Select regexp_replace('5x a day x 10 days', '[^[:digit:]]' from dual;
The code extracts numbers ..but doesn't multiply & give the result.Have one more scenario as well.
Input field varchar2 : take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days
Output : 2 * 800 * 4 * 6 * 10 = 384000
Select regexp_replace('take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days', '[^[:digit:]]' from dual;
For the above code, if it's hours ..we need to convert into day by multiplying with the required factor to make it a day.
View 9 Replies
View Related
Mar 20, 2010
how to create a function that will multiply the number when the number has more than 3 decimal point.
Example.
123.012 - multiply by 10
123.0123 - multiply by 100
123.01234 - miltiply by 1000
Column A is the result of the number being multiplied according to decimal point.
Column B contains the multiplier used in column A.
View 10 Replies
View Related
Oct 19, 2012
Just started to learn oracle DBA. I am creating database manually.. I am facing following errors
I executed catproc.sql script it was running suddenlt oracle throwd following error.
CODEPL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package body created.
No errors.
Package body created.
BEGIN
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'
ORA-06512: at "SYS.DBMS_STATS", line 13210
ORA-06512: at "SYS.DBMS_STATS", line 13517
[code]...
Script detail's
CODECREATE DATABASE drprod
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/drprod/redo01.log') SIZE 100M , GROUP 2 ('/u01/app/oracle/oradata/drprod/redo02.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
[code]...
View 14 Replies
View Related
Sep 10, 2010
I disable the triggers when I program. what the program is doing behind the scenes. So if I insert into a table, the trigger would fire and insert into another table. Disabling the triggers shows me that I need to insert into both tables. This also allows me to have the primary key in my conversion tables match the database.
With that being said, can I tell the trigger to fire after the fact?
View 1 Replies
View Related
Jul 15, 2010
How can one lock a table manually?
View 7 Replies
View Related
Nov 13, 2012
I am trying to make database manually and i am successfully created it,but when i am trying to run catalog.sql and catproc.sql my database get dismount automatically. i also tried to upgrade it via search results on problem but i found this below error when i am typing startup.
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
View 3 Replies
View Related
Aug 30, 2013
I have a form with a datablock displaying 3 text item fields.But the actual oracle table have 8 columns.When ever I insert new data and save it, only the three records are being stored in backend whereas remaining column stores with null value.That is the basic function of the form.
I placed an another list item field in a control block which is non-database item.Now I want to store the list item value into a column along with the 3 columns.In a simple way, how to insert record manually along with the other records?
View 14 Replies
View Related
Dec 21, 2011
when i connected with oracle user i got error message like "
ERROR:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_APPLICATION_INFO.SET_MODULE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
"
View 1 Replies
View Related
