Pick up

[cleberz]

Good morning gentlemen. How do I get in a table the previous record?

Ex:



The second line is wrong because the Field STRT_DT (01/11/16) is lower Than the END_DT field (12/30/16) of the first line.
there I need to get the previous line of strt_dt (01/11/16)

The last line is wrong because the STRT field (23/11/16) is less than The END_DT field (12/30/16) of the penultimate line.
Here I need to get the previous line of strt (23/11/16)

[spernega]

Good morning,

This is a table with vigils and you need to get the inconsistencies?

[dr_gori]

You can use analytical functions to look for the previous line.

For example: (in this case, grouping by department)

Select all

select hiredate 
, LAG(hiredate,1,NULL) 
  OVER (PARTITION BY deptno 
        ORDER BY hiredate, ename)  
From EMP; 

Select all

 
DEPTNO ENAME      HIREDATE    LAG(HIREDATE,1,NULL)OVER(PARTI 
------ ---------- ----------- ------------------------------ 
    10 CLARK      6/9/1981     
    10 KING       11/17/1981  6/9/1981 
    10 MILLER     1/23/1982   11/17/1981 
    20 SMITH      12/17/1980   
    20 JONES      4/2/1981    12/17/1980 
    20 FORD       12/3/1981   4/2/1981 
    20 SCOTT      4/19/1987   12/3/1981 
    20 ADAMS      5/23/1987   4/19/1987 
    30 ALLEN      2/20/1981    
    30 WARD       2/22/1981   2/20/1981 
    30 BLAKE      5/1/1981    2/22/1981 
    30 TURNER     9/8/1981    5/1/1981 
    30 MARTIN     9/28/1981   9/8/1981 
    30 JAMES      12/3/1981   9/28/1981 
 
14 rows selected

Or also have how to make the table all: (without clustering)

Select all

select hiredate 
, LAG(hiredate,1,NULL) 
  OVER (PARTITION BY NULL 
        ORDER BY hiredate, ename)  
From EMP;

Select all

 
HIREDATE    LAG(HIREDATE,1,NULL)OVER(PARTI 
----------- ------------------------------ 
12/17/1980   
2/20/1981   12/17/1980 
2/22/1981   2/20/1981 
4/2/1981    2/22/1981 
5/1/1981    4/2/1981 
6/9/1981    5/1/1981 
9/8/1981    6/9/1981 
9/28/1981   9/8/1981 
11/17/1981  9/28/1981 
12/3/1981   11/17/1981 
12/3/1981   12/3/1981 
1/23/1982   12/3/1981 
4/19/1987   1/23/1982 
5/23/1987   4/19/1987 

see this text with several examples of analytical functions: http://en.glufke.net/oracle/download/fu ... TICAS.html : -O