Oracle Getting the Latest Change Date based on a Criteria

user_3770648 0
Viewed 59

I have this following table tbl_subscriptions. 

+-----------+--------+----------+--------+
| Sub.Date  |   ID   | POSITION | STATUS |
+-----------+--------+----------+--------+
| 8/22/2018 | 254515 | BROWN    | OPEN   |
| 8/21/2018 | 254515 | ORANGE   | CLOSE  |
| 8/20/2018 | 254515 | RED      | CLOSE  |
| 8/19/2018 | 254515 | ORANGE   | CLOSE  |
| 8/18/2018 | 254515 | BLUE     | CLOSE  |
| 8/17/2018 | 254515 | BLUE     | CLOSE  |
| 8/16/2018 | 254515 | BLUE     | CLOSE  |
| 8/15/2018 | 254515 | BLUE     | CLOSE  |
| 8/14/2018 | 254515 | ORANGE   | CLOSE  |
| 8/13/2018 | 254515 | BLUE     | CLOSE  |
+-----------+--------+----------+--------+

I am really struggling to get the below. I have thousands of entries like this with various IDs. I need to get all Open entries along with when it was recently changed from Blue to Other Position. See below, recently, the Position was changed from Blue to Orange on 8/19/2018. So, I want to see the data like this.

+-----------+--------+----------+--------+-----------------+
| Sub.Date  |   ID   | POSITION | STATUS | Pos. Changed on |
+-----------+--------+----------+--------+-----------------+
| 8/22/2018 | 254515 | BROWN    | OPEN   | 8/19/2018       |
+-----------+--------+----------+--------+-----------------+

I am not even getting an idea how to achieve this. Is this at least possible with Oracle query.

oracle11g oracle sql
3 Answers

You may use lag windows function as :

select ttt.*,
(
  select max(tt.sub_date)
    from
  (  
    select t.*,
           lag(POSITION) over (partition by ID order by Sub_Date ) lg       
      from tab t 
  ) tt
  where STATUS != 'OPEN'
    and tt.POSITION = 'ORANGE'
    and tt.lg = 'BLUE'
) as changed_on
  from tab ttt
 where STATUS = 'OPEN';

SQL Fiddle Demo

user_58413060

Here's one way:

WITH your_table AS (SELECT to_date('22/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'BROWN' position, 'OPEN' status FROM dual UNION ALL
                    SELECT to_date('21/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'ORANGE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('20/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'RED' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('19/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'ORANGE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('18/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('17/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('16/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('15/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('14/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'ORANGE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('13/08/2018', 'dd/mm/yyyy') sub_date, 254515 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('21/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'BROWN' position, 'OPEN' status FROM dual UNION ALL
                    SELECT to_date('20/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'ORANGE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('19/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'RED' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('18/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'ORANGE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('17/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('16/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'BLUE' position, 'OPEN' status FROM dual UNION ALL
                    SELECT to_date('15/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('14/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('13/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'ORANGE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('12/08/2018', 'dd/mm/yyyy') sub_date, 254516 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('29/08/2018', 'dd/mm/yyyy') sub_date, 1 ID, 'BLUE' position, 'OPEN' status FROM dual UNION ALL
                    SELECT to_date('28/08/2018', 'dd/mm/yyyy') sub_date, 1 ID, 'BLUE' position, 'CLOSED' status FROM dual UNION ALL
                    SELECT to_date('27/08/2018', 'dd/mm/yyyy') sub_date, 1 ID, 'BLUE' position, 'OPEN' status FROM dual UNION ALL
                    SELECT to_date('26/08/2018', 'dd/mm/yyyy') sub_date, 1 ID, 'YELLOW' position, 'CLOSED' status FROM dual),
        mid_res AS (SELECT sub_date,
                           ID,
                           position,
                           status,
                           CASE WHEN LAG(position) OVER (PARTITION BY ID ORDER BY sub_date) = 'BLUE' AND position != 'BLUE' THEN sub_date END blue_to_non_blue_dt
                    FROM   your_table),
            res AS (SELECT sub_date,
                           ID,
                           position,
                           status,
                           blue_to_non_blue_dt,
                           last_value(blue_to_non_blue_dt IGNORE NULLS) OVER (PARTITION BY ID ORDER BY sub_date) latest_blue_to_non_blue_dt
                    FROM   mid_res)
SELECT sub_date,
       ID,
       position,
       status,
       latest_blue_to_non_blue_dt
FROM   res
WHERE  status = 'OPEN'
ORDER BY ID, sub_date DESC;

SUB_DATE            ID POSITION STATUS LATEST_BLUE_TO_NON_BLUE_DT
----------- ---------- -------- ------ --------------------------
29/08/2018           1 BLUE     OPEN   
27/08/2018           1 BLUE     OPEN   
22/08/2018      254515 BROWN    OPEN   19/08/2018
21/08/2018      254516 BROWN    OPEN   18/08/2018
16/08/2018      254516 BLUE     OPEN   13/08/2018

This works by first finding the rows where the previous row was BLUE and the current row is not BLUE. Then it populates that value across subsequent rows before finally filtering to show only the open rows.

I have assumed that you can have multiple OPEN rows for an id, and that you'd want the latest changed date for each OPEN row, so I have added in extra sample data to demonstrate how that would look (id's 1 and 254516).

user_44793090

You can do this just using window functions:

select t.*
from (select t.*,
             max(case when position = 'ORANGE' and prev_position = 'BLUE' then sub_date end) over (partition by id) as change_date
      from (select t.*,
                   lag(position) over (partition by ID order by sub_date) as prev_position
            from t
           ) t
      ) t
where status = 'OPEN';
user_11440350
Related