CODEWITHSUNDEEP
sqloracle-databaseview
Martin Keown
Martin Keown

Reputation: 58

Oracle Creating a view with specific records from a table

I would like to create a view from the following table called TB_STATUS.

ID  EVENT_DATE       REOPEN_DATE
1   16/1/2013 16:16 
2   16/1/2013 16:38 
3   16/1/2013 18:18 
4   15/1/2013 0:00  
5   16/1/2013 16:36  16/1/2013 0:00
6   16/1/2013 17:12  16/1/2013 0:00
7   4/1/2014 9:00    4/1/2014 0:00
8   5/1/2014 14:00   5/1/2014 0:00
9   4/1/2014 11:00  
10  4/1/2014 16:00  
11  4/8/2013 11:00  
12  15/8/2013 1:00

Expected view content is MIN(EVENT_DATE) in between REOPEN_DATE as follows:

ID  EVENT_DATE       REOPEN_DATE
    MIN(EVENT_DATE)
                     REOPEN_DT
    MIN(EVENT_DATE) 
                     REOPEN_DT
    MIN(EVENT_DATE) 
                     REOPEN_DT
    MIN(EVENT_DATE)

Expected result for the above dataset will be as follows:

ID  EVENT_DATE       REOPEN_DATE
4   15/1/2013 0:00  
5   16/1/2013 16:36  16/1/2013 0:00
2   16/1/2013 16:38 
6   16/1/2013 17:12  16/1/2013 0:00
11  4/8/2013 11:00  
7   4/1/2014 9:00    4/1/2014 0:00
9   4/1/2014 11:00  
8   5/1/2014 14:00   5/1/2014 0:00

Any help would be greatly appreciated.

Upvotes: 0

Views: 38

Answers (1)

MT0
MT0

Reputation: 167962

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TB_STATUS(
  ID          INTEGER PRIMARY KEY,
  EVENT_DATE  DATE NOT NULL,
  REOPEN_DATE DATE
);

INSERT INTO TB_STATUS
SELECT  1, TIMESTAMP '2013-01-16 16:16:00', NULL FROM DUAL UNION ALL
SELECT  2, TIMESTAMP '2013-01-16 16:38:00', NULL FROM DUAL UNION ALL
SELECT  3, TIMESTAMP '2013-01-16 18:18:00', NULL FROM DUAL UNION ALL
SELECT  4, TIMESTAMP '2013-01-15 00:00:00', NULL FROM DUAL UNION ALL
SELECT  5, TIMESTAMP '2013-01-16 16:36:00', TIMESTAMP '2013-01-16 00:00:00' FROM DUAL UNION ALL
SELECT  6, TIMESTAMP '2013-01-16 17:12:00', TIMESTAMP '2013-01-16 00:00:00' FROM DUAL UNION ALL
SELECT  7, TIMESTAMP '2014-01-04 09:00:00', TIMESTAMP '2014-01-04 00:00:00' FROM DUAL UNION ALL
SELECT  8, TIMESTAMP '2014-01-05 14:00:00', TIMESTAMP '2014-01-05 00:00:00' FROM DUAL UNION ALL
SELECT  9, TIMESTAMP '2014-01-04 11:00:00', NULL FROM DUAL UNION ALL
SELECT 10, TIMESTAMP '2014-01-04 16:00:00', NULL FROM DUAL UNION ALL
SELECT 11, TIMESTAMP '2013-08-04 11:00:00', NULL FROM DUAL UNION ALL 
SELECT 12, TIMESTAMP '2013-08-15 01:00:00', NULL FROM DUAL;

CREATE VIEW TB_STATUS_MINS AS
SELECT ID,
       EVENT_DATE, -- CASE WHEN REOPEN_DATE IS NULL THEN EVENT_DATE END,
       REOPEN_DATE
FROM   (
  SELECT t.*,
         LAG( REOPEN_DATE, 1, DATE '1970-01-01' ) OVER ( ORDER BY EVENT_DATE ) AS prev_reopen
  FROM   TB_STATUS t
)
WHERE  reopen_date IS NOT NULL
OR     prev_reopen IS NOT NULL

Query 1:

SELECT *
FROM   TB_STATUS_MINS

Results:

| ID |           EVENT_DATE |          REOPEN_DATE |
|----|----------------------|----------------------|
|  4 | 2013-01-15T00:00:00Z |               (null) |
|  5 | 2013-01-16T16:36:00Z | 2013-01-16T00:00:00Z |
|  2 | 2013-01-16T16:38:00Z |               (null) |
|  6 | 2013-01-16T17:12:00Z | 2013-01-16T00:00:00Z |
|  3 | 2013-01-16T18:18:00Z |               (null) |
|  7 | 2014-01-04T09:00:00Z | 2014-01-04T00:00:00Z |
|  9 | 2014-01-04T11:00:00Z |               (null) |
|  8 | 2014-01-05T14:00:00Z | 2014-01-05T00:00:00Z |

Upvotes: 2

Related Questions