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
ADVERTISEMENT
Aug 10, 2003
I want to calculate time difference b/w two time clocks, just like we calculate the date difference and answer is in days, In the same way i like to have answer in hrs,min and ss.
View 4 Replies
View Related
Feb 28, 2013
I need to subtract multiple numbers to get the difference between each numbers, the amount of numbers to subtract between each others can vary between 2 and 10.
create table table_1 (
col1 varchar2(5),
col2 number(6,0),
col3 number(12,0)
)
insert into table_1 values ('AAA', 34379, 11111);
insert into table_1 values ('AAA', 39032, 11111);
insert into table_1 values ('AAA', 54337, 11111);
insert into table_1 values ('AAA', 78005, 11111);
insert into table_1 values ('AAA', 66793, 22222);
insert into table_1 values ('AAA', 74323, 22222);
insert into table_1 values ('AAA', 81426, 22222);
Expected Output:
col1 col2 col3
AAA -4653 11111
AAA -15305 11111
AAA -23668 11111
AAA -7530 22222
AAA -7103 22222
View 6 Replies
View Related
Nov 29, 2011
during application migration, i got one table from MS Access, and have situation where two events are splited into 4 columns (start: date1 time1 and stop: dat2 and time2). How to properly calculate duration between these two events, and show it in format: hh:mi ?
CREATE TABLE ACCBTP_DCZASTOJ
(ID NUMBER(11,0),
NASTANAK_KVARA DATE,
VRIJ_NASTKVARA DATE,
VRIJEME_OPRAVKE DATE,
DATUM_OPRAVKE DATE);
[Code]....
View 6 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
Apr 9, 2013
I need the query to calculate minutes from difference of to number fields. Test case is as below.
DROP TABLE tmp;
CREATE TABLE tmp
(
code NUMBER(4),
stime NUMBER(4,2),
otime NUMBER(4,2)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]......
CODE STIME OTIME
---------- ---------- ----------
1065 20 19.49
1082 20 18.57
1279 19.3 18.59
2075 19.3 15.32
Required output is
CODE STIME OTIME HR_MIN MINUTES
---------- ---------- ---------- ------------- --------
1065 20 19.49 00 HR 21 MIN 21
1082 20 18.57 01 HR 03 MIN 63
1279 19.3 18.59 00 HR 31 MIN 31
2075 19.3 15.32 03 HR 58 MIN 238
View 8 Replies
View Related
Mar 24, 2011
I have two different date in my payroll software,
1-Shift_date shift date *used to contain shift timings
2-Attendace_datedate *used to contain employee IN timings
As you all know that shift is a setup form, where user input data once in the starting of software so the shift_date can be "01/jan/2011 16:00 pm" but attendance loads daily and attendance field data can be in this form "24/mar/2011 16:15 pm"..Now I want to calculate difference time between these two fields therefore I used this statement
SQL> Select to_char(attendance_date,'HH24:MI') to_char(shift_date,'HH24:MI') from dual;
but it is showing error: ORA-01722: invalid number...I used hours/minutes format mask in my query because you can see there is a difference of dates between these fields and it will be increase in the coming future and I need late hours and minutes.
View 2 Replies
View Related
Jan 10, 2012
how to calculate the difference between multiple dates at the same time..
Select to_date('10/10/2011','mm/dd/yyyy')
- to_date('09/10/2011','mm/dd/yyyy')
- to_date('08/10/2011','mm/dd/yyyy') from dual;
Giving me an error...
ORA 00932 : inconsisten data types:expected DATE Julkian got DATE..
View 8 Replies
View Related
Sep 22, 2011
create or replace function getDate(p_joing_date Date,p_sysdate)
Return Date;
IS
v_compltd_mnths;
BEGIN
SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL;
return v_compltd_mnths;
END;
that i have worte..
View 3 Replies
View Related
Mar 19, 2013
I am trying to get a record count of this table and i get 2 different output , select count(*) shows 68560 and after updating the stats the NUM_ROWS shows 68323, why the difference .
SQL> select count(*) from dcepcd;
COUNT(*)
----------
68560
SQL> select num_rows from dba_tables where table_name='DCEPCD';
NUM_ROWS
----------
68323
View 4 Replies
View Related
Dec 7, 2012
SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402
This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.
SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'
and owner='APP'; --Number of records 237213998
But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.
View 2 Replies
View Related
Dec 7, 2012
SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402
This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.
SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'
and owner='APP'; --Number of records 237213998But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.
View 3 Replies
View Related
Feb 2, 2012
Find the date difference. I need to find that how many days the task is pending, if ACT_NAME field switching from 'SET PENDING%' to 'RESUME PENDING%' by using ACTIONTAKENDATETEXT field in the History table.
Example as needed:
NoPendingDays = 23 (8+15)
I have attached Create table and Insert table values sample as SQL file.
View 10 Replies
View Related
Nov 16, 2009
When we execute select count(*) from table_name it returns the number of rows.
What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?
View 13 Replies
View Related
Nov 24, 2011
difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.
SQL> select * from t1;
A B C
---------- -------------------- --------------------
1 2 3
2
5
SQL> select rownum,a.* from t1 a;
ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....
View 3 Replies
View Related
Jun 30, 2010
I have a table which contains two columns containing the range of some products. These range are alphanumeric and i want to calculate the count of this range by a query. .
test case:
CREATE TABLE MARRIAGE_FILE_NAME
(
START_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
END_SERIAL_NO VARCHAR2(10 BYTE) NOT NULL,
CATCODE VARCHAR2(10 BYTE) NOT NULL,
[code]....
View 2 Replies
View Related
May 28, 2013
How do I get the max difference where each value can look at only the next value(ordered by date asc) on any particular day
example first row on 20090902 is 501.25 (earliest date)
756.2 - 501.25
4735.83 - 501.25
35.83 - 501.25
[code]....
View 19 Replies
View Related
Jul 20, 2007
How one can get date difference in days between two dates in Oracle reports ?
View 2 Replies
View Related
Jan 23, 2013
I am looking to subtract two columns and get the difference.
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual;
select to_char('06-NOV-2012 20:00','DD-MON-YYYY HH24:MI') - to_char(systimestamp,'DD-MON-YY HH24:MI') from dual
*
ERROR at line 1:
ORA-01722: invalid number
View 9 Replies
View Related
Aug 25, 2010
I encountered this problem when we recently migrated to 10g. The table nametab has two columns roll(number datatype) and name(varchar2(10) datatype).Following are the queries used and I have deliberately not given space between the "select" clause and the "from" clause.
In 9i
SELECT name,1FROM nametab where rownum < 2
NAME 1
---------- ----------
Bharath 1
Same query in 10g
SELECT name,1FROM nametab where rownum < 2
ORA-00923: FROM keyword not found where expected
Here, I have interchanged the select columns.
In 9i
SELECT 1,nameFROM nametab where rownum < 2
ORA-00923: FROM keyword not found where expected
In 10g
SELECT 1,nameFROM nametab where rownum < 2
ORA-00923: FROM keyword not found where expected
View 7 Replies
View Related
Apr 25, 2013
what are difference in between oracle 9i and 10g regarding with Sql queries?
View 1 Replies
View Related
Sep 28, 2011
I am looking for an explanation for having the following query:
SELECT PK_SERIAL_NUMBER FROM TABLE1 MINUS (SELECT FK_SERIAL_NUMBER FROM TABLE2);
Which normally returns the values of SERIAL_NUMBER that are not passed to the child table TABLE2
and the query:
SELECT PK_SERIAL_NUMBER FROM TABLE1 WHERE PK_SERIAL_NUMBER
NOT IN
(
SELECT FK_SERIAL_NUMBER FROM TABLE2
);
which returns 0 rows!
View 6 Replies
View Related
May 30, 2011
1. difference between v$sql and dba_hist_sqlstat?
2. as of i have understood, dba_hist_sqlstat gets refreshed from v$sql.
if i run this below query i get different outputs.. how?
by using v$sql table
WITH t AS
(SELECT force_matching_signature ,
SUM(executions_delta) executions,
COUNT(*) cnt ,
parsing_schema_name ,
MIN(MODILE1) MODULE1
[code].......
View 2 Replies
View Related
Sep 22, 2011
I listen that there is a big diff B/W indexing in oracle 8i and oracle 11g.
what way the indexing is different B/W 8i and 11g.
View 3 Replies
View Related
Sep 24, 2012
for the below table
create table RM_TR_INVESTMENT
(
AS_ON_DT DATE not null,
EXP_ID NUMBER(10) not null,
BO_REF_ID VARCHAR2(30),
FO_REF_ID VARCHAR2(30),
BK_PRODUCT_ID VARCHAR2(20),
BK_INV_TYPE_ID VARCHAR2(20),
[code]....
I want to find the sum of NP_AMT_CCY where difference between Start_Dt and Maturity_Dt >= 14 days and <=28 days.How can I build the query for the above statement ?
View 6 Replies
View Related
Dec 18, 2012
When I use this sql: CREATE USER testuser IDENTIFIED BY "welcome1 " to create user in Oracle DB 11.2.0.1.0. I can login with testuser/welcome1, please notice the welcome1 is not end of black space in login.
But when I do the same thing in Oracle DB 11.2.0.3.0. I can't use this user login with any password. I tried welcome1, "welcome1 " but all failed.
Why there are this difference between 11.2.0.1.0 and 11.2.0.3.0, and since in 11.2.0.3.0 the create sql are success, what password is correct when login?
View 7 Replies
View Related
Aug 17, 2013
I have two columns StartTime and EndTIme.
I have data like this
EndTime StartTime Diff(endtime-StartTime)
23:00 20:00 03:00
22:00 21:30 00:30
How can i get Diff(endtime-StartTime) from hh24:mi format
View 2 Replies
View Related
Oct 19, 2012
i want to know is there any difference between upgradation of SID and database. while upgrading my database from oracle 11.2.0.2 to 11.2.0.3 in DBUA it shows my SID is upgrading (ORCL) but i have few other databases ex (test and prod).
is it enough if we upgrade the SID or we must perform any other actions..
and
2nd QUERY :
i have 2 oracle homes in my server with different versions like (11.2.0.2 and 11.2.0.3)and i have few databases and there tablespaces. how to determine which database is created on which version and which database is upgraded from 11.2.0.2 to 11.2.0.3.
View 3 Replies
View Related
May 27, 2013
I have a question about the ADD VOLUME command, I can't understand the difference between ADD DISK and ADD VOLUME.What are the difference between them?When should I use each one?How can I control the stripping and mirroring (NORMAL and HIGH) adding VOLUMES in a DISKGROUP?Can I add a volume to a fail group?
View 1 Replies
View Related
Sep 1, 2012
What are all the differences between Coalesce and De-fragmentation? When i need to use Coalesce?
View 4 Replies
View Related
