CODEWITHSUNDEEP
sqloracle-databasedate
kzmlbyrk
kzmlbyrk

Reputation: 593

SQL last status change date

I am trying to get the dates of last status changes. Below is an example data table. In brief I want to query the minimum DATE value of the latest STATUS (ordered by CHANGE_NO) for each PRODUCT_ID. Mentioned values are the ones filled with yellow.

enter image description here

So far, I could get only the latest dates for each product.

SELECT
  *
FROM
  (
    SELECT
      PRODUCT_ID, CHANGE_NO, STATUS, DATE
      ,MAX(CHANGE_NO) OVER(PARTITION BY PRODUCT_ID) MAX_CHANGE_NO
    FROM TABLE
    ORDER BY PRODUCT_ID, CHANGE_NO
  ) 
WHERE   MAX_CHANGE_NO = CHANGE_NO

Please kindly share the link if there is already a question/answer for a similar case; I've searched but couldn't find any.

Note: I am using Oracle SQL.

Thanks in advance.

Upvotes: 0

Views: 2114

Answers (2)

user5683823
user5683823

Reputation:

Here's one way to do this with analytic functions (avoiding joins).

with
     test_data ( product_id, change_no, status, dt ) as (
       select 1, 1, 'A', date '2016-10-10' from dual union all
       select 1, 2, 'B', date '2016-10-11' from dual union all
       select 1, 3, 'C', date '2016-10-12' from dual union all
       select 1, 4, 'D', date '2016-10-13' from dual union all
       select 2, 1, 'Y', date '2016-02-02' from dual union all
       select 2, 2, 'X', date '2016-02-03' from dual union all
       select 2, 3, 'X', date '2016-02-04' from dual union all
       select 3, 1, 'H', date '2016-06-20' from dual union all
       select 3, 2, 'G', date '2016-06-21' from dual union all
       select 3, 3, 'T', date '2016-06-22' from dual union all
       select 3, 4, 'K', date '2016-06-23' from dual union all
       select 3, 5, 'K', date '2016-06-24' from dual union all
       select 3, 6, 'K', date '2016-06-25' from dual
     )
-- End of test data (not part of the solution). SQL query begins below this line.
select   product_id,
         max(status) keep (dense_rank last order by change_no) as status,
         max(dt) as dt
from     (
           select product_id, change_no, status, dt,
                  case when lead(status) over (partition by product_id 
                                               order by change_no desc)
                            = status then 0 else 1 end as flag
           from   test_data
         )
where    flag = 1
group by product_id
order by product_id    --    if needed
;

Output

PRODUCT_ID  STATUS  DT
----------  ------  ----------
         1  D       13/10/2016
         2  X       03/02/2016
         3  K       23/06/2016

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT * FROM (
SELECT PRODUCT_ID, CHANGE_NO, STATUS,DATE, MIN(DATE) OVER(PARTITION BY PRODUCT_ID,STATUS) as MIN_DATE_OF_LATEST_STATUS
FROM (SELECT PRODUCT_ID, CHANGE_NO, STATUS, DATE
      ,FIRST_VALUE(STATUS) OVER(PARTITION BY PRODUCT_ID ORDER BY CHANGE_NO DESC) LATEST_STATUS
     FROM TABLE
     ) T
     WHERE STATUS = LATEST_STATUS
              ) T
WHERE DATE = MIN_DATE_OF_LATEST_STATUS
  • Use the FIRST_VALUE window function to get the latest status for each product_id
  • Get the MIN date for those status rows
  • Finally get those rows where min_date = date

If change_no isn't needed in the final result, the query can be simplified to

SELECT PRODUCT_ID, STATUS, MIN(DATE) as MIN_DATE_OF_LATEST_STATUS
FROM (SELECT PRODUCT_ID, CHANGE_NO, STATUS, DATE
      ,FIRST_VALUE(STATUS) OVER(PARTITION BY PRODUCT_ID ORDER BY CHANGE_NO DESC) LATEST_STATUS
     FROM TABLE
     ) T
WHERE STATUS = LATEST_STATUS
GROUP BY PRODUCT_ID, STATUS

Upvotes: 0

Related Questions