SQL & PL/SQL :: How To Get Difference Between Two Rows Of Same Column
Aug 3, 2013
Calculating the difference between the two rows of same columns? Example of my Scenario is as follows
I have a table as shown below, there are checks thats has been issued per week (to tell how its been issued per week ,if you see the week number ,there is a change in week no for period of 7 days i.e. what i have to show on my results tables based on week no and probably we may need to take into account for check no too.)
wekno ! checkno ! end date
------ ! ------ ! ------
1 ! 1 ! 1/7/2012
1 ! 2 ! 1/8/2012
1 ! 3 !1/9/2012
2 ! 1 ! 1/14/2012
2 ! 2 ! 1/15/2012
3 ! 1 ! 1/21/2012
result table and date range i picked up was 1/1/2012-1/31/2012
wekno ! checkno ! end date ! previous period ! Daysin period
------ ! ------ ! ------ ! ------------- ! ------------
1 ! 1 ! 1/7/2012 ! null (as previous period is 2011 and so prvs year i dont count) ! 7 this because we see change in wkno per 7 days
1 ! 2 ! 1/8/2012 !null becoz this one falls same week and therefore previous peroid would be the same as above ! 7 above reason
1 ! 3 ! 1/9/2012 ! null becoz this one falls same week and therefore previous peroid would be the same as above ! 7 above reason
2 ! 1 ! 1/14/2012 ! 1/7/2012 becoz this a second week as you see the change in wekno and therefore its previosu period is 1/7/2012 ! 7 above reason
2 ! 2 ! 1/15/2012 ! 1/7/2012 becoz this check falls on the same second week so previosu would be 1/7/2012 ! 7 above reason
3 ! 1 ! 1/21/2012 ! 1/14/2012 as a new week no starts this gonn be 1/4/2012 ! 7 above reason
View 2 Replies
ADVERTISEMENT
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
Sep 26, 2010
difference in the values that are returned?
select count(*) from aaa;
COUNT(*)
----------
1000001
select num_rows from dba_tables where table_name = 'AAA';
NUM_ROWS
----------
994202
View 5 Replies
View Related
Aug 28, 2013
How can I compare two rows from the same table and show the field and the difference?
Example:
select ename, job, mgr, hiredate, sal, deptno
from EMP t
where t.empno = 7782
or t.empno = 7788
ENAME JOB MGR HIREDATE SAL DEPTNO
1 CLARK MANAGER 7839 6/9/1981 2450.00 10
2 SCOTT ANALYST 7566 6/9/1981 3000.00 20
My report comparing fields should be:
FIELD BASE COMPARE DIFFERENCE
ENAME CLARK SCOTT --
JOB MANAGER ANALYST --
MGR 7839 7566 --
SAL 2450 3000 550 --(Show difference only for numbers)
DEPTNO 10 20 --
View 9 Replies
View Related
Aug 29, 2012
just i want add diiferece column in my select statement.For Example:-
select
Name,
Salary,
Designation,
date_first_payment,
date_last_payment,
(date_first_payment-date_last_payment) Datediff
from My_table
then its working
select *,(date_first_payment-date_last_payment) Datediff
from My_table
its not working..
View 7 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
Dec 24, 2010
through Difference between table Level, column Level ,row level constraints.
View 8 Replies
View Related
May 24, 2013
know when to use a formula column and when to use a function in program unit in oracle reports.
View 1 Replies
View Related
Mar 18, 2011
I have following table.
CREATE TABLE ORAFAQ
(
DPT NUMBER,
EMP NUMBER,
SAL NUMBER
)
[Code]....
DPTEMPSAL
1110
12100
131000
241500
25100
26500
27100
but i need data like
Dept
1
1 2 3 --3 columns show each employee
10 100 1000 --3 columns show salary below each employee
2
4 5 6 7
1500 100 500 100
View 1 Replies
View Related
Jul 14, 2012
I have two tables as follows:
TABLE_1
ID ENTRY_DATE VALUE
------- --------------- ----------
1 1-JUN-12 21
1 2-JUN-12 51
1 3-JUN-12 232
1 4-JUN-12 221
1 5-JUN-12 424
.
.
.
.
1 26-JUN-12 52
1 27-JUN-12 0
1 28-JUN-12 247
1 29-JUN-12 528
1 30-JUN-12 489
2 1-JUN-12
2 2-JUN-12
2 3-JUN-12
2 4-JUN-12
2 5-JUN-12
.
.
.
.
2 26-JUN-12
2 27-JUN-12
2 28-JUN-12
2 29-JUN-12
2 30-JUN-12
TABLE_2
ID DAY_1 DAY_2 DAY_3 DAY_4 DATE_5 .......... DAY_26 DAY_27 DAY_28 DAY_29 DAY_30
----- ---------- --------- --------- ---------- ---------- --------- --------- -------- -------- --------
1 21 51 232 221 424 52 0 247 528 489
2
There are millions of DISTINCT ID values in TABLE_1 and corresponding to each ID there are some values for all the days of a month. I need to insert these values in TABLE_2 in the above format.
View 1 Replies
View Related
Sep 25, 2012
How to convert station & date wise multiple rows to date wise column. I have a table TEST like this,
STN_Name Date State
DELHI1-Sep-201G
DELHI2-Sep-201B
DELHI3-Sep-201G
DELHI4-Sep-201G
DELHI5-Sep-201G
DELHI6-Sep-201M
DELHI7-Sep-201G
DELHI8-Sep-201G
DELHI9-Sep-201G
DELHI ......... ..
DELHI 30 Sep 2012 G
KOLKATA1-Sep-201G
KOLKATA2-Sep-201B
KOLKATA3-Sep-201B
KOLKATA4-Sep-201B
KOLKATA5-Sep-201G
KOLKATA6-Sep-201M
KOLKATA7-Sep-201G
KOLKATA8-Sep-201G
KOLKATA9-Sep-201B
I want output like this
STN_Name1-Sep-20122-Sep-20123-Sep-20124-Sep-20125-Sep-2012............Sep 2012
DELHI G B G G G M
KOLKATA G B B B G M
View 2 Replies
View Related
Mar 18, 2013
writing the query, Output is shown below. facing problem in when converting columns into rows.
CREATE TABLE TESTTAB (IDNUM VARCHAR2(3),PLAN1 VARCHAR2(5),AMT1 NUMBER,PLAN2 VARCHAR2(5),AMT2 NUMBER,PLAN3 VARCHAR2(5),AMT3 NUMBER,PLAN4 VARCHAR2(5),AMT4 NUMBER,PLAN5 VARCHAR2(5),AMT5 NUMBER)
INSERT INTO TESTTAB VALUES('100','A01',9,'','','','','D04',300,'E05',900)
INSERT INTO TESTTAB VALUES('101','','','B02',56,'C03',24,'','','','')
INSERT INTO TESTTAB VALUES('102','A01',33,'','','C03',545,'','','E05',23)
[Code]...
My OUTPUT SHOULD BE LLIKE
IDNUM PLAN AMT
100 A01 9
100 D04 300
100 E05 900
101 B02 56
101 C03 24
102 A01 33
102 C03 545
102 E05 23
.
.
104 C03 44
View 5 Replies
View Related
Mar 22, 2012
can we show the following rows into column upto 1000 using sql query?
A
1
2
3
4
[code]...
Result should like this 1,2,3,4,5,6,7,8,...... 1000 including comma(,)
View 3 Replies
View Related
May 31, 2011
I have a table with columns emp_i, LOC_C and SUBSID_C. I want to find all emp_i's with LOC_C OR SUBSID_C as always NULL. Please note that the value should be NULL, always, for all dates.
--------------------------------------------
EX:
emp_i----LOC_C---SUBSID_C--Eff_Date
100------20------30--------01/01/2011
101------NULL----10--------05/01/2011
102------NULL----NULL------02/01/2011
101------20------NULL------02/01/2011
102------NULL----NULL------05/01/2011
103------NULL----20--------01/01/2011
103------NULL----NULL------02/01/2011
--------------------------------------------
The query ---
should return 102 as LOC_C OR SUBSID_C is ALWAYS NULL.
should return 103 as LOC_C is ALWAYS NULL.
should not return 101, as LOC_C is not ALWAYS NULL.
In other words, the query should give list of emp_i who never ever had a non-null value for LOC_C OR SUBSID_C. The purpose is to find the emp_i for which the columns LOC_C and SUBSID_C are never used.
I tried the query:
---------------------------------------------------
SELECT DISTINCT ORG_EMP_I FROM tab1
WHERE ORG_GRP_I = 58
AND ORG_EMP_I NOT IN (
SELECT DISTINCT org_emp_i
FROM tab1 ap
WHERE ap.ORG_GRP_I = 58
AND trim(ap.LOC_C) IS NOT NULL OR ap.ORG_SBSID_C IS NOT NULL )
---------------------------------------------------
View 10 Replies
View Related
May 10, 2012
SELECT a.objname, c.property1, c.value1
FROM datatable a, datatabledet c
WHERE a.OBID=c.DATAOBID
and a.CLASSNAME='Class1';
OBJNAME PROPERTY1 VALUE1
280-419-1994psCls1Attr3Attr1Value3
280-419-1994psCls1Attr4Attr1Value4
280-419-1994psCls1Attr5Attr1Value5
[code]....
After query output we put through front end code to make it in the following way i.e. convert rows into columns but with respective data value:
psCls1Attr3 psCls1Attr4 psCls1Attr5 psCls1Attr1 psCls1Attr2 psCls1Attr7
280-419-1994 Attr1Value3 Attr1Value4 Attr1Value5 Attr1Value1 Attr1Value2
280-419-1995 Attr1Value11Attr1Value21Attr1Value71
how to output in the above format using SQL.
View 4 Replies
View Related
May 18, 2010
converting Rows to Column for a specific group.
Following is the query
SELECT Lpad(papf.employee_number, 6, '0') "File#",
papf.full_name,
Decode(ppee.processing_type, 'R', Decode(ppee.element_name,
'Regular Wages USD', peev.screen_entry_value,
'Regular Salary USD', Round(peev.screen_entry_value / 26, 2),
0),
[Code]...
Which gives me out put as
Example 1
Employee Number Name Value Rate Type Type Assignment Id
-----------------------------------------------------------------
4 Employee 1 800 N 5 Yr Bonus 64
4 Employee 1 1063 N 6 Months Bouns 64
4 Employee 1 24.04 H Reg Hourly Sal 64
5 Employee 2 6923.08 S Reg Sal 65
6 Employee 3 5961.54 S Reg Sal 66
6 Employee 3 15000 N 6 Months Bouns 66
I want to convert above out to look like following
Emp Num Name Value Rate Type Type Val 1 Rate Type 1 Type 1 Value 2 Rate Type 2 Type 2 Assignt Id
-------------------------------------------------------------------------------------------------------------------
4 Employee 1 24.04 H Reg Hourly Sal 800 N 5 Yr Bon 1063 N 6 Mon Bon 64
5 Employee 2 6923.08 S Reg Sal 65
6 Employee 3 5961.54 S Reg Sal 15000 N 6 MontBon 66
Example of Query Output 2.
Employee Number Name Value Rate Type Type Assignment Id
--------------------------------------------------------------------------------
4 Employee 1 800 N 5 Yr Bonus 64
4 Employee 1 1063 N 6 Months Bouns 64
4 Employee 1 1345 N Patent Bonus 64
4 Employee 1 24.04 H Reg Hourly Sal 64
5 Employee 2 6923.08 S Reg Sal 65
6 Employee 3 5961.54 S Reg Sal 66
6 Employee 3 15000 N 6 Months Bouns 66
Desired Output from Above Query. As you can see column have increased as one more row for same employee has increased
ENu Nm Val RTy Ty Val1 RTy1 Ty1 Val2 RTy2 Ty2 Val3 RTy3 Ty3 AsgId
-------------------------------------------------------------------------------------------------------------------
4 E1 24.04 H Reg Hour Sal 1063 N 6 Mon Bns 1345 N Pat Bon 800 N 5YB 64
5 E2 6923.08 S Reg Sal 65
6 E3 5961.54 S Reg Sal 15000 N 6 Mos Bns 66
View 7 Replies
View Related
Jun 25, 2012
I have a table with below structure
Id - Desc -repeat_times
1a - andy - 3
2b - santa - 5
I want to write a sql query which outputs two columnns like below. the particular row to be repeated as per the repeat_times column.
1a-1 andy
1a-2 andy
1a-3 andy
2b-1 santa
2b-2 santa
2b-3 santa
2b-4 santa
2b-5 santa
I have tried but am failing..
View 1 Replies
View Related
Aug 14, 2013
Currently I have a requirement where I need return data of different columns in rows.
For example: I have a table that contains monthly data for voice calls, sms count and mms count for each mobile. I will need to get the output as summary of voice calls, sms and mms counts in different rows. For this I am using an approach which I am not totally satisfied even though I am getting the required results (majorly because i am querying the table thrice for getting this data).
I would want to know about any alternate ways of implementation.
--Create table
Create table
UsageData
(Mobile varchar2(20),
CAL_MONTH Varchar2(20),
VOICE_CALL number(9),
SMS number(9),
MMS number(9)
);
--Data
insert into UsageData values ('9999','JAN',1,2,3);
insert into UsageData values ('9888','JAN',5,20,1);
insert into UsageData values ('9777','JAN',4,5,9);
insert into UsageData values ('9666','JAN',200,111,8);
insert into UsageData values ('9555','JAN',154,1534,3);
insert into UsageData values ('9444','JAN',0,2,212);
--Query
select 'VOICE_CALL' AS EVENT,sum(VOICE_CALL) AS UNITS from UsageData where cal_month ='JAN'
union all
select 'SMS',sum(SMS) from UsageData where cal_month ='JAN'
union all
select 'MMS',sum(MMS) from UsageData where cal_month ='JAN';
View 8 Replies
View Related
Mar 28, 2011
I huge table with million of rows and no of column more than 300.. is it possible to keep this table in some memory where oracle can access it fast as compare to disk memory.
View 8 Replies
View Related
Nov 20, 2011
how to convert rows data into column...
View 7 Replies
View Related
Mar 24, 2013
I have one table , with one column having 2,3 or 4 machine codes , i need to display them as each row per machine code will it be possible to do as i have thousands of records similar to the test case and which i had to do it manually in excel and then upload it back.
create table ow_oper_setup (wo_no varchar2(12),mrk_no varchar2(20),pos_no varchar2(30),mc_code varchar2(60))
insert into ow_oper_setup VALUES ('1270','1270001','W165','IR HO BV ')
insert into ow_oper_setup VALUES ('1270','1270001','W1332','IR BV ')
insert into ow_oper_setup values ('1270','1270001','W1367','RE HO SC BV ')
insert into ow_oper_setup values ('1270','1270001','W389','RE HO SC BV')
commit;
SELECT * FROM ow_oper_Setup;
WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR HO BV
12701270001W1332IR BV
12701270001W1367RE HO SC BV
12701270001W389RE HO SC BV
--i want the output in the following way or the same table data to be replaced as below
WO_NOMRK_NOPOS_NOMC_CODE
12701270001W165IR
12701270001W165HO
12701270001W165BV
12701270001W1332IR
12701270001W1332BV
12701270001W1367RE
12701270001W1367HO
12701270001W1367SC
12701270001W1367BV
12701270001W389RE
12701270001W389HO
12701270001W389SC
12701270001W389BV
View 12 Replies
View Related
May 19, 2010
I have a select statement
select name, address, qty
from names_table
and it returns a result such as
name|address|qty
alan,1 the street,5
bert,2 the road,3
what I want to do is create an output that takes the value in the qty column and repeates the number of rows e.g. the alan record 5 times and the bert record 3 times
name|address|qty
alan,1 the street,5
alan,1 the street,5
alan,1 the street,5
[code]...
how I might go about acheiving this?
View 14 Replies
View Related
Sep 14, 2010
i need to transpose a column to rows.
CREATE TABLE TEST(c1 VARCHAR(10),c2 VARCHAR(2),t1 NUMBER,t2 NUMBER);
INSERT INTO TEST VALUES('a','a',1,2);
INSERT INTO TEST VALUES('b','b',2,3);
INSERT INTO TEST VALUES('c','c',7,6);
INSERT INTO TEST VALUES('d','d',2,2);
Actual output.
C1C2T1T2
aa12
bb23
cc76
dd22
I need the output in this format
C1C2C3C4
aaT11
aaT22
bbT12
bbT23
ccT17
ccT26
ddT12
ddT22
ie the header t1 and t2 has to come in the output along with the corresponding values it has.the number of column as T1,T2 will be dynamic cannot predict before.
View 7 Replies
View Related
Mar 30, 2013
column sid format 'a5'
column serial# format 'a10'
column mins_running format 'a15'
column sql_text format 'a100'
set linesize 200
set pagesize 30
[Code]..
I am running this code, and the output shows multiple lines.
TRIM(S.SID) TRIM(S.SERIAL#) MINS_RUNNING SUBSTR(Q.SQL_TEXT,1,70)
---------------------------------------- ---------------------------------------- --------------- ----------------------------------------------------------------
700 46592 242.08 Select count(*) as count, case when count(*)>0 then 'FAIL' else
700 46592 242.08 'PASS' end as result
from (SELECT cv.code_value
FROM code_valu
[Code]...
Is there a way to wrap up the column for SQL_TEXT VARCHAR2(64) so that I can 1 row for the output?
View 14 Replies
View Related
Jul 3, 2012
I am having a table with following data:
DateProduct_nameQuantity_sold
12/6/2012Product11
12/7/2012product23
12/6/2012product34
12/7/2012Product12
12/7/2012product21
12/6/2012product35
12/7/2012product33
12/6/2012product24
Now I want this data in following manner:
For each date - how much is the Total quantity sold , sum of Product1 sold,Sum of product2 sold,etc. I am having more than 50 products in my table.
dateQuantity_soldProduct1Product2Product3
12/6/2012141 49
12/7/201292 43
I was trying this by decode but the sum of the product was not correct.
View 2 Replies
View Related
Sep 24, 2010
I have a table emp having data as
empno ename sal deptno
1001 manoj 1000 10
1001 sachin 2000 20
1003 manoj 30000 30
Now I wanted to write a query which will display the results like below. Row data need to be display in the columns
empno ename sal deptno 1001 manoj 10 20 1003 30
1001 manoj 1000 10 1001 manoj 10
1001 sachin 2000 20 1001 sachin 20
1003 manoj 30000 30 1003 manoj 1003 30
How to print the rows into column data?
View 1 Replies
View Related
Mar 10, 2011
Consider the following (example)table.,
TABLE_A
------------------------------
ID DEPT CRS
------------------------------
1 CS CS_100
2 SCIENCE SCI_150
3 MATH MATH_400
4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
--------------------------------------------
NO DEPT CRS
--------------------------------------------
1 MATH MATH_400, MATH_550, MATH_230
--------------------------------------------
instead of.,
--------------------------
NO DEPT CRS
---------------------------
1 MATH MATH_400
2 MATH MATH_550
3 MATH MATH_230
---------------------------
View 2 Replies
View Related
Dec 11, 2009
I need a single select query which converts all the rows into a single value . Below is my requirement :
Create table email_tbl(emailid varchar2(30));
insert into email_tbl('1@y.com');
insert into email_tbl('2@y.com');
insert into email_tbl('3@y.com');
insert into email_tbl('4@y.com');
Now , I need a single select query which gives me the below results.
1@y.com,2@y.com,3@y.com,4@y.com
I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
View 7 Replies
View Related
Dec 11, 2012
I have a table where i want the output to be a single column
ex: select from t1;*
query output_
rownum col_1
1 8217
2 6037
3 5368
4 5543
5 5232
i would like the result to be : *8217,6037,5368,5543,5232*
i did look on the web but can't find a solution that is easily understood.
View 9 Replies
View Related
Jun 25, 2012
I am trying to write a script where a particular post code from a table is having more than 3 telephone numbers.Both the columns are in the same table. How to fetch.
Table is P_Order
Columns are DELIVERY_POSTCODE and TEL_NO...
Condition DELIVERY_POSTCODE has more than 3 TEL_NO
View 1 Replies
View Related
