Last changed Data with T as status in Oracle sql

My Data is given below

In the below sample latest record has T and last occurrence of T was updated on 3-Apr-17 so that row needs to be displayed

EMP EFFDT STATUS
11367 15-Apr-15 A
11367 14-Jun-15 A
11367 10-Aug-15 T
11367 2-Apr-17 A
11367 3-Apr-17 T *
11367 10-Apr-17 T


In the below sample latest record has T and last occurrence of T was updated on 23-Feb-18 so that row needs to be displayed

EMP EFFDT STATUS
20612 4-Sep-16 A
20612 23-Feb-18 T *
20612 20-Jul-18 T


In the below sample latest record has T and that is the only occurrence so display it

EMP EFFDT STATUS
20644 12-Jul-15 A
20644 8-Aug-16 A
20644 6-Oct-16 T*


In the below sample latest record does not has T so no need to display

EMP EFFDT STATUS
21155 18-May-17 T
21155 21-Jun-17 A
21155 13-Mar-18 T
21155 15-Aug-18 A


My Desired Output should be (* marked records)

EMP EFFDT STATUS
11367 3-Apr-17 T
20612 23-Feb-18 T
20644 6-Oct-16 T


was actually updated on 3-Apr-17

10-Apr-17

4 Answers
4

This is an island and gap problem.

In the cte you try to found out what island have T as last update (t=0)

SQL DEMO

WITH cte as (
SELECT "EMP",
"EFFDT",
SUM(CASE WHEN "STATUS" <> 'T'
THEN 1
ELSE 0
END) OVER (partition by "EMP" ORDER BY "EFFDT" DESC) as t
FROM Table1
)
SELECT "EMP", MIN("EFFDT") as "EFFDT", MAX('T') as "STATUS"
FROM cte
WHERE t = 0
GROUP BY "EMP"


OUTPUT

| EMP | EFFDT | STATUS |
|-------|-----------------------|--------|
| 11367 | 2017-04-03 00:00:00.0 | T |
| 20612 | 2018-02-23 00:00:00.0 | T |
| 20644 | 2016-10-06 00:00:00.0 | T |


For debug you can try

SELECT *
FROM cte


to see how t values are created

t

WITH cte1
AS (
SELECT A.*
,lag(STATUS, 1, 0) OVER (
PARTITION BY EMP ORDER BY EFFDT
) AS PRIOR_STATUS
FROM Table1 A
)
SELECT EMP
,STATUS
,MAX(EFFDT) AS EFFDT
FROM cte1 A
WHERE A.STATUS = 'T'
AND A.PRIOR_STATUS <> 'T'
GROUP BY EMP
,STATUS


SQL Fiddle here: http://sqlfiddle.com/#!4/458733/18

alter session set nls_date_format = 'dd-Mon-rr';


Solution (including simulated data in with clause):

with

with
simulated_data (EMP, EFFDT, STATUS) as (
select 11367, to_date('15-Apr-15'), 'A' from dual union all
select 11367, to_date('14-Jun-15'), 'A' from dual union all
select 11367, to_date('10-Aug-15'), 'T' from dual union all
select 11367, to_date( '2-Apr-17'), 'A' from dual union all
select 11367, to_date( '3-Apr-17'), 'T' from dual union all
select 11367, to_date('10-Apr-17'), 'T' from dual union all
select 20612, to_date( '4-Sep-16'), 'A' from dual union all
select 20612, to_date('23-Feb-18'), 'T' from dual union all
select 20612, to_date('20-Jul-18'), 'T' from dual union all
select 20644, to_date('12-Jul-15'), 'A' from dual union all
select 20644, to_date( '8-Aug-16'), 'A' from dual union all
select 20644, to_date( '6-Oct-16'), 'T' from dual union all
select 21155, to_date('18-May-17'), 'T' from dual union all
select 21155, to_date('21-Jun-17'), 'A' from dual union all
select 21155, to_date('13-Mar-18'), 'T' from dual union all
select 21155, to_date('15-Aug-18'), 'A' from dual
)
-- End of simulated data (for testing only).
-- SQL query (solution) begins BELOW THIS LINE.
select emp, min(effdt) as eff_dt, 'T' as status
from (
select emp, effdt, status,
row_number() over (partition by emp, status
order by effdt desc) as rn,
min(status) keep (dense_rank last order by effdt)
over (partition by emp) as last_status
from simulated_data
)
where last_status = 'T' and status = 'T' and rn <= 2
group by emp
;


Output:

EMP EFF_DT STATUS
---------- --------- ------
11367 03-Apr-17 T
20612 23-Feb-18 T
20644 06-Oct-16 T


Explanation:

In the subquery, we add two columns to the input data. Column RN gives a rank within each partition by EMPNO and STATUS, in descending order by EFFDT. LAST_STATUS used the analytic version of the LAST() function to assign either T or A as the last status for each EMP (and it attaches this value to EVERY row for the EMP, regardless of each row's own STATUS).

EMPNO

STATUS

EFFDT

LAST_STATUS

LAST()

T

A

EMP

EMP

STATUS

In the outer query, we are only interested to retain the EMP where the last status was T. For those rows, we only want to retain the rows where the actual status of the row is in fact T (we know this will always include the last row for that EMP, by the way, and it will have RN = 1). Moreover, we are only interested in those rows where RN is 1 or possibly 2 (if there are at least two rows with status T for that EMP). Of these either one or two rows with status T for a given EMP, we want to get the EARLIEST date. That will be the ONLY date if there is no row with RN = 2 for that partition; otherwise, it will be the date from the earlier row, with RN = 2.

EMP

T

T

EMP

RN = 1

RN

T

EMP

T

EMP

RN = 2

RN = 2

In the outer SELECT we select the EMP, the earliest date, and the status we already know, it is T (so we don't need any work for this - actually it is not clear why the third column is even needed, since it is known beforehand it will be T in all rows).

SELECT

EMP

T

T

Assuming that A and T are the only statuses, this should work.

WITH cte1
AS (
SELECT A.EMP, A.EFFDT, A.STATUS
,min(STATUS) OVER (
PARTITION BY EMP ORDER BY EFFDT RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS MIN_STATUS
FROM Table1 A
)
SELECT
cte1.EMP
,MIN(cte1.EFFDT) AS EFFDT
,MIN(cte1.STATUS) as STATUS
FROM cte1
WHERE cte1.MIN_STATUS = 'T'
GROUP BY EMP


EDIT: well, if you have another statues, let's make it more robust. Actually, it's almost the same as juan-carlos-oropeza proposed, but he missed "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" part.

Ooops, it IS the same solution: juan-carlos-oropeza used order by DESC istead of unbounded following.

with emp_status_log (EMP, EFFDT, STATUS) as
(
select 11367, to_date('15-Apr-15', 'dd-Mon-yy'), 'A' from dual union all
select 11367, to_date('14-Jun-15', 'dd-Mon-yy'), 'A' from dual union all
select 11367, to_date('10-Aug-15', 'dd-Mon-yy'), 'T' from dual union all
select 11367, to_date( '2-Apr-17', 'dd-Mon-yy'), 'A' from dual union all
select 11367, to_date( '3-Apr-17', 'dd-Mon-yy'), 'T' from dual union all
select 11367, to_date('10-Apr-17', 'dd-Mon-yy'), 'T' from dual union all

select 20612, to_date( '4-Sep-16', 'dd-Mon-yy'), 'A' from dual union all
select 20612, to_date('23-Feb-18', 'dd-Mon-yy'), 'T' from dual union all
select 20612, to_date('20-Jul-18', 'dd-Mon-yy'), 'T' from dual union all

select 20644, to_date('12-Jul-15', 'dd-Mon-yy'), 'A' from dual union all
select 20644, to_date( '8-Aug-16', 'dd-Mon-yy'), 'A' from dual union all
select 20644, to_date( '6-Oct-16', 'dd-Mon-yy'), 'T' from dual union all

select 21155, to_date('18-May-17', 'dd-Mon-yy'), 'T' from dual union all
select 21155, to_date('21-Jun-17', 'dd-Mon-yy'), 'A' from dual union all
select 21155, to_date('13-Mar-18', 'dd-Mon-yy'), 'T' from dual union all
select 21155, to_date('15-Aug-18', 'dd-Mon-yy'), 'A' from dual
)
,
-- End of simulated data (for testing only).
/* SQL query (solution) begins BELOW THIS LINE.
with--*/
cte1 as
(
select sl.*
,sum(decode(sl.STATUS, 'T', 0, 1)) OVER (
PARTITION BY sl.EMP ORDER BY sl.EFFDT RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS non_t_count
from emp_status_log sl
)
select
cte1.emp
, min(cte1.effdt) as effdt
, min(cte1.status) as status
from cte1
where cte1.non_t_count = 0
group by cte1.emp


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.