SQL & PL/SQL :: Compare Current Rows With Previous Rows

Apr 12, 2013

The Table having 3 columns

col0col1 , col2
P112
P112
P102
P113
P115

I want to retrieve the changes rows only.

Output like
col0col1 , col2
P112
P102
P113
P115

View 5 Replies


ADVERTISEMENT

PL/SQL :: Compare A Row Of A Table With Its Previous Row / Rows In A Query

Oct 1, 2013

create table a(sourcerow number(2),  test_level number(2),  dpn varchar2(1),  qty number(5)); T

he insert scripts are as follows:  

insert into a  values(1,3,'Y',5); insert into a values(2,2,'Y',4);  insert into a values(3,3,'N',3); insert into a  values(4,4,'Y',3);  insert into a  values(5,1,'N',6);  insert into a values(6,2,'N',5);  insert into a  values(7,2,'Y',4);  insert into a  values(8,3,'N',2);  insert into a values(9,4,'Y',2);  insert into a  values(10,1,'Y',3); .  SQL>select * from v$version;  Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production 

My logic should have the output as follows without 5th column:  sourcerowleveldpnqtyremark13Y522Y4This row in main table shouldn't be displayed in the query result as it has

dpn='N'44Y3*3=9In this row qty=9 will bedisplayed.

It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.this row will not be displayed as dpn='N'this row will not be displayed as dpn='N'72Y4*6=24in its previous row level value is same so it will check the previous to previous row  where level is 1(parent of current row) and dpn='N' ,then it will multiply the qty of that row with current row and display the value in qty column.this row will not be displayed as dpn='N'94Y2*2=4In this row qty=4 will be displayed.It will compare the value of level column with its previous row level col,if it is the parent of the current(ex.current is 4 and previous is 3 parent of 4),then it will check the dpn of previous row ,if dpn='N' then qty of parent will be multiplied with qty of current row and displayed under qty column.101Y3It will not check for the previous rows as level 1 doesn't have any parent. 

View 5 Replies View Related

Compare Current With Previous Row Until Mismatch

Nov 2, 2011

We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:

CODEemp_salary
----------
emp_id NUMBER(15) NOT NULL
effective_date DATE NOT NULL
salary NUMBER(15) NOT NULL

Primary key - emp_id, effective_date..This table is yearly partitioned...I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:

CODEWITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary

[code]....

For emp_id 1, if we ran this query for 10/31/2011, then it has to compare the 10/31 salary with 10/29 and do the same until the salary mismatches. In this case, salary salary mismatch occurs on 10/20, so the stale salary period is from 10/31 to 10/21 which is 7 days.Below query will give that result:

CODE
WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary

[code]...

View 1 Replies View Related

SQL & PL/SQL :: Compare Previous Records With Current?

Aug 29, 2013

I am trying to set the min date if there is no gap between dates.compare previous date2 value with current date1,if they are same then my new date will be min(date1).

source data

date1 iddate2 new_date
1/2/20111234/2/2011
4/2/20111237/2/2011
7/2/201112310/2/2011
10/2/20111231/2/2012
1/2/20121234/2/2012
4/2/20121237/2/2012
12/17/20121233/17/2013
3/17/20131236/17/2013

and I am expecting the out put like this

date1 id date2 new_date
1/2/20111234/2/20111/2/2011
4/2/20111237/2/20111/2/2011
7/2/201112310/2/20111/2/2011
10/2/20111231/2/20121/2/2011
1/2/20121234/2/20121/2/2011
4/2/20121237/2/20121/2/2011
12/17/20121233/17/201312/17/2012
3/17/20131236/17/201312/17/2012

how to achieve this with SQL

View 3 Replies View Related

Performance Tuning :: Compare Current Row Values With Previous One Until Mismatch

Nov 2, 2011

We have employee salary table which will have salary of an employee on daily basis (only working days). Below is the table structure:

emp_salary
----------
emp_id NUMBER(15) NOT NULL
effective_date DATE NOT NULL
salary NUMBER(15) NOT NULL
Primary key - emp_id, effective_date
This table is yearly partitioned

I have to find out how long the salary is not changed for an employee from given date, and last salary. I am using below query to do this:

WITH salary_tab AS
(SELECT effective_date, salary,
(CASE
WHEN (LAG (salary) OVER (PARTITION BY emp_id ORDER BY effective_date ASC) =
salary
)
THEN 0
ELSE 1
END
) changed_ind
FROM emp_salary
WHERE emp_id = 12345
[code]....

The cost of this query is 1677 and it is taking around 60 msec to complete. When I run this query for around 2000 employees in a loop it is taking around 3 minutes to complete.

The main bottleneck of this query is in the with clause where I am processing the entire history instead of stopping after first change.

View 7 Replies View Related

SQL & PL/SQL :: Get Previous Value Of Rows?

Dec 29, 2010

How to get the previous value of row with calling function to add value in SELECT statement for the row value.

Consider the example Table A1 having column a with values 1,NULL,NULL,NULL

SELECT CASE WHEN a IS NULL THEN (prev_row_value+function_return_Value) ELSE a END as A from A1

And my result-set should be like

a
----------------------
1
1+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Prev_Row_Value+(Return Value Of Function)
Below is sample code but doesn't fulfill my criteria

[code]....

Output is

A A2
---------------------------
1 1
3
3
3

View 8 Replies View Related

SQL & PL/SQL :: Executing Rows With Previous Row

Nov 3, 2010

i have tablestructure like this

empno ename sal
1 sam 1000
2 tom 2000
3 ric 3000
4 mac 4000
5 doy 5000

i want TO WRITE SELECT QRY WHICH WILL GO like this

empno ename sal prevemp prevename presale
1 sam 1000 0 0 0
2 tom 2000 1 sam 1000
3 ric 3000 2 tom 2000
4 mac 4000 3 ric 3000
5 doy 5000 4 mac 4000

means when each current row executes it shld show details from previous row also means when details of tom is executing it also shows sam details

View 4 Replies View Related

SQL & PL/SQL :: Compare Two Rows Of Cursor?

Oct 6, 2010

I have an audit table FEE_RULE_AUD with the following structure.

FEE_RULEVARCHAR2(10 BYTE)
AUDIT_DATETIMESTAMP(6)
AUDIT_ACTIONVARCHAR2(30 BYTE)
AUDIT_USERVARCHAR2(8 BYTE)
NAMEVARCHAR2(30 BYTE)
FEE_BASISCHAR(1 BYTE)
FEES_ONCHAR(1 BYTE)
SECURITYCHAR(10 BYTE)

I have to generate a report for the audit with in the dates specified(range). I got the set of record for the specified date range by using the following query:

select * from fee_rule_aud where TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))>20090629
and TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))<=20100710 order by fee_rule,audit_date

this query returned some five records, now I have to iterate through this and compare 1st and 2nd row in first iteration (1st row will have the new value and 2nd row will have old vale). If there is any difference then insert into audit_log table which has the following structure:

Fee_rule , column_name, old_value,new_value

This process has to repeat for all the 5 rows like comparing 1st,2nd rows and 2nd,3rd rows and 3rd,4th rows and so on if it has more rows.

get the two rows and compare ?

View 16 Replies View Related

PL/SQL :: How To Compare Two Rows And Retrieve The Values

Aug 15, 2012

I have two tables which have identical schemas, one table (tbl_store) is used to hold the latest version, and the other table (tbl_store_audit) holds previous versions. When the latest record is updated, it is inserted into the tbl_store_audit table as a revision, and the updated details are used as the latest record.

For example: The latest version is held in tbl_store, however the tbl_store_audit may hold 5 records which are the past records used before changes were made - these are seen as revisions.

I want to be able to compare what has changed between each revision in the tbl_store_audit table. For example: Out of the 10 columns, the change between revision 1 and revision 2 was the size from XL to XXL. The change between revision 3 and revision 4 was the size XS to M and price 4.99 to 10.99, and so on.

Eventually i will create an APEX report that will show the user the revision number and what was changed from and to.

I seen in a previous post i need to note my oracle version: Oracle version 10.2.0.4.0

View 16 Replies View Related

PL/SQL :: Compare Two Tables And Delete The Common Rows From Table 1

Jul 23, 2012

I want to compare two tables , and delete the common rows from the first table

Here is what i have done :

Create table test1(Test1C1 Number,
Test1C2 varchar2(50));

Create table test2(Test2C1 Number,
Test2C2 varchar2(50));

Insert into test1 values(1,'testdata1');
Insert into test1 values(2,'testdata2');
Insert into test1 values(3,'testdata3');
[code].......

it deletes all the records from Table test1. What should I modify here ? or should I write a different query ?

The desired contents in table test1 will be

2 testdata2
4 testdata4
6 testdata6
8 testdata8
10 testdata10

View 5 Replies View Related

PL/SQL :: Compare Two Rows From Same Table And Show Field And Difference?

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

Reports & Discoverer :: Dynamically Displaying Current Value In Rows

Mar 24, 2011

where I have to display the value of current cell in next following cells.The table structure is as follows :

ttdate - date
individualplanid - varchar(10); - train number
sch_deptime - number(8); - scheduled departure time in milli seconds
sch_arrtime - number(8); - scheduled arrival time in milli seconds
stn - varchar(10); - station short name
dep_delay - number(8); - dep delay in milli seconds
arr_delay - number(8); - arr delay in milli seconds

The delay is filled continuously by application software continuously.I want to make a query where I want a calculated field which does the prediction of train arrival on coming stations. This shall be done as the delay + sch_deptime for all the next stations. Following is a dataset :

individualplanid sch_deptime sch_arrtime stn arr_delay dep_delay

BO646NULL57900000BVINULL80000
BO646NULL58140000KILENULL40000
BO646NULL58320000MDDNULL20000
BO646NULL58530000GMNNULLNULL
BO646NULL59160000ADHNULLNULL
BO646NULL59550000STCNULLNULL
BO646NULL59940000BANULLNULL
BO646NULL60540000DDRNULLNULL
BO646NULL61200000BCLNULLNULL
BO64661800000NULLCCG12000NULL
[code]....

The last column (EAT) is calculated one. It shall be like as the dep_delay is updated the EAT for the following records shall be sch_deptime + dep_ delay. I did was something like this. The calculated field is cum_depdelay which is only the delay ( not sch_deptime + dep_delay ).

select ttdate,individualplanid td,station,sch_deptime,sch_arrtime,act_depdelay,
sum(act_depdelay) over ( partition by individualplanid order by sch_deptime rows between current row and unbounded following ) c_depdelay
from logtime where ttdate='14-Mar-2011' and individualplanid='BO646' order by sch_deptime,sch_arrtime;
[code]...

how shall i proceed ?

View 2 Replies View Related

SQL & PL/SQL :: Updating Current Column With Previous Columns?

Dec 22, 2011

I need to update the current column with sum of the previous column values. Following are the creation scripts

DROP TABLE TEST_LOG;
CREATE TABLE TEST_LOG
(

[Code]....

Above query is working fine to retrieve the previous column values.But when we are updating the SUM_PRE_COLS column with those values it's not working fine.

I tried by using the following query

UPDATE TEST_LOG T SET SUM_PRE_COLS =
( SELECT LAG(T2.KEY0, 1, 0) OVER(ORDER BY T2.KEY0) + LAG(T2.KEY1, 1, 0) OVER(ORDER BY T2.KEY0)
FROM TEST_LOG T2 WHERE T2.ROWID= T.ROWID);

View 5 Replies View Related

Server Administration :: Find Out Difference Of Data For Some Tables Between Current And Previous Day

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

PL/SQL :: Select 4 Rows And Then Next 4 Rows?

May 27, 2013

in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?

I have fifty rows inside the table

select * from student where rownum between 0 and 4 order by stuid ->it work

select * from student where rownum between 5 and 9 order by stuid -> did not work

what is the correct way to do it ?

View 10 Replies View Related

SQL & PL/SQL :: Subdivide Rows Into Rows

Apr 10, 2013

drop table test
/
create table test
(
lib varchar2(100)
)
/
insert into test values ('111/aaa/bbb/ccc');
insert into test values ('222/aaa/bbb/ccc');
insert into test values ('333+444/aaa/bbb/ccc');
insert into test values ('333/aaa/bbb/ccc');
insert into test values ('222+333+444/aaa/bbb/ccc');
insert into test values ('222+333+444+555/aaa/bbb/ccc');

I want to get this :

lib count
--------------- -----
111/aaa/bbb/ccc 1
222/aaa/bbb/ccc 3
333/aaa/bbb/ccc 4
444/aaa/bbb/ccc 3
555/aaa/bbb/ccc 1

View 4 Replies View Related

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

Dec 14, 2007

I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

PL/SQL :: Code Erases Previous Month Record While Updating Current Month Record

May 16, 2013

Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53                      24
2013             March          loc1          55007          78               57                      28
2013             April          loc1          54345          72               58                  77The code:

UPDATE missing_volume g

[Code]....

The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:

yr          mnth          location     volume          actual_missing          expected_missing     estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013            January          loc1          48037          24               57                         
2013             February     loc1          47960          3660               53
2013             March          loc1          55007          78               57
2013             April          loc1          54345          72               58                   77

why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).

View 5 Replies View Related

SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows

Oct 6, 2010

I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query

MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762

and I need to display this as

COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762

View 3 Replies View Related

Reports & Discoverer :: How To Compare Current Page Number With Total Page Number

Feb 19, 2013

I have a report created in Reports6i. I have two fields at the bottom

1.Page Total
2.Voucher Total

I do not want to print the Page Total if the total number of pages = 1.

How can I achieve this?

View 1 Replies View Related

SQL & PL/SQL :: Put Two Rows Into One Row?

Sep 14, 2013

We are using PL/SQL Release 11.2.0.2 .I would like to pull a query with each student each day an attendance record.Our database setup an AM and PM Period for all elementary students. I will pull if they absent both periods(AM, PM), then count that as one day absent.The hard part is I need to put the AM absent code and PM absent code - which is basically to put two records for each student's AM and PM absent code into one row.

Below is the query I use, but it violates the key of database, for PK is studentid+ attendance date. My query result turns out for some students they have different attendance code in AM vs PM, there are two records returned.

SELECT DISTINCT TO_CHAR(S.Student_Number) ,
TO_CHAR(Am.Schoolid) ,
Sps_School_Year('C') ,
TO_CHAR(Am.Att_Date,'MM/DD/YYYY') ,
(

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Best Way To Get Several Rows From SCD For Each Key

Nov 20, 2012

We have large (millions of records) Slow changind dimension (SCD) type 2 (see "Creating another dimension record " URL>.....We need to get several rows from this SCD for each key (AGREEMENT_ID) in a SQL query - to join to facts table and get several data points of each agreement (on several different points in time) stored in SCD.Here is SCD table structure:

CREATE TABLE AGREEMENT
(
"AGREEMENT_ID" NUMBER(*,0) NOT NULL ENABLE,
"ACTUAL_DATE" DATE NOT NULL ENABLE,
"ACTUAL_END_DATE" DATE NOT NULL ENABLE,
"OPEN_DATE" DATE NOT NULL ENABLE,
"LIMIT" NUMBER(23,8)
--++ a lot of other fields not needed for this task ....
CONSTRAINT "PK_MD_AGREEMENT" PRIMARY KEY ("AGREEMENT_ID", "ACTUAL_DATE") USING INDEX
)

The 1st simple approach would be to join facts to SCD as many (N) times as many different points of time you need - resulting in N Full Table Scans for SCD:

select ...
from fact,
AGREEMENT agr1,
AGREEMENT agr2,
AGREEMENT agr3
where
fact.AGREEMENT_ID = agr1.AGREEMENT_ID and agr1.open_date between actual_date and actual_end_date
and fact.AGREEMENT_ID = agr2.AGREEMENT_ID and :dateBOP between actual_date and actual_end_date
and fact.AGREEMENT_ID = agr3.AGREEMENT_ID and :dateEOP between actual_date and actual_end_date

2nd approach: 1 Full Table Scan for SCD + group by:

select ...
from fact,
(
Select AGREEMENT_ID,
max(case when open_date between actual_date and actual_end_date then LIMIT end) LIMIT_At_Open_DATE,
max(case when :dateBOP between actual_date and actual_end_date then LIMIT end) LIMIT_At_BeginOfPeriod_DATE,
max(case when :dateEOP between actual_date and actual_end_date then LIMIT end) LIMIT_At_EndOfPeriod_DATE

from agreement

-- ++optionally WHERE for those 3 dates, but possibly with no effect on non-partitioned table? Or WHERE to put less data on MAX() input (3 row for each agreement instead of 4...1000 without WHERE?)

group by AGREEMENT_ID
) agr
where fact.AGREEMENT_ID = agr.AGREEMENT_ID

View 2 Replies View Related

PL/SQL :: ANY ALL When No Rows

Jun 5, 2013

Simple question, Why comparison operator ANY returns FALSE if no rows returned, and why operator ALL return TRUE if no rows returned? I dont know is this some kind of language or math assumption or is this just oracle rule?

View 18 Replies View Related

SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

May 30, 2012

What is the difference between the following . In my schema all are giving the same results with some different format

SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;

SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM

View 1 Replies View Related

SQL & PL/SQL :: Verify Current Date Is Greater Than 15th Of Current Month

Sep 22, 2010

I need to verify if the current date is grater than the 15th of the current month. If its grater than the 15th of the current month i need to do an action or if else its lesser than 15th of the current month i need to do an other operation.

View 5 Replies View Related

Converting 2 Rows Into 1

Aug 16, 2007

i have 2 tables

A B
id a.id
name
type

There are at most 2 entries of a in b. Depending on the value of the type column in B, this determines whether the entry should be male or female. I want to have a select statement that will retrieve 2 rows into one row essentially like below, how is this done:

id male_name female_name
1 paul paula

the column names will appear as such, if its a 0 its a male name if its 1 its a female name, there will generally be 2 entries in B for 1 value of a.

View 2 Replies View Related

Throughput Rows Per Second

Nov 7, 2012

I would want to know how can we predict how many rows are fetched per second for a particular query. What are the factors which are responsible for this.hoe does this whole process of fetching records from Source happens. Like when a query is fired how does it try to access the table and fetch records and how what are the factors whichc are responsible for this and how can we predict how many rows can be fetched per sec

View 8 Replies View Related

SQL & PL/SQL :: How To Get Reduplicate Rows

Jul 12, 2011

There are many reduplicate rows in a big table,what is the best way to get the reduplicate rows?

View 5 Replies View Related

SQL & PL/SQL :: Rows Into Column

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







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