CODEWITHSUNDEEP
sqloracle-database
SSA_Tech124
SSA_Tech124

Reputation: 599

sql query to get the max date for a particular element

I have a table

PEEVF

EFFECTIVE_START_DATE EFFECTIVE_END_DATE PERSON_ID ELEMENT_NAME VALUE
01-JAN-2021 10-JAN-2021 19 Bonus 9.1
11-JAN-2021 28-MAR-2021 19 Bonus 11
29-MAR-2021 10-APR-2021 19 Bonus 11.1
11-APR-2021 31-DEC-2021 19 Bonus 10
11-APR-2022 30-APR-2022 19 Bonus 12
19-APR-2022 30-MAY-2022 19 LTP 09
01-JAN-2019 30-APR-2019 8 Bonus 5
11-MAR-2021 10-JAN-2021 10 Bonus 11
11-JAN-2021 28-MAR-2021 10 Bonus 11.1
20-JAN-2019 08-MAR-2019 1 LTP 9
11-APR-2019 19-AUG-2019 1 LTP 9.8

I want to display the following output by using MAXand not use ROW_NUM.

LATEST_EFF_DT_BONUS LATEST_EFF_DT_LTP BONUS_VAL LTP_VAL PERSON_ID
11-APR-2022 12 19
19-APR-2022 09 19
01-JAN-2019 5 8
11-JAN-2021 11.1 10
11-APR-2019 9.8 1

When i am using the below statement, it is not giving correct value.

max(effective_start_Date)  over ( PARTITION BY ELEMENT_NAME,EFFECTIVE_START_DATE order by PERSON_ID )

Upvotes: 1

Views: 98

Answers (1)

David Lukas
David Lukas

Reputation: 1229

Edit: Based on the downvote, I revised SQL. Now SQL returns the desired result.

SQL:

with b as (
  select
    person_id,
    element_name,
    max(effective_end_date) as msd
  from peevf 
  group by person_id, element_name
) select
 case when b.element_name = 'Bonus' then to_char(a.EFFECTIVE_START_DATE,'DD-MON-YYYY') else ' ' end  as LATEST_EFF_DT_BONUS,
 case when b.element_name = 'LTP' then to_char(a.EFFECTIVE_START_DATE,'DD-MON-YYYY') else ' ' end as LATEST_EFF_DT_LTP,
 case when b.element_name = 'Bonus' then trim(trailing '.' from to_char(a.value,'fm99D9')) else ' ' end as BONUS_VAL,
 case when b.element_name = 'LTP' then trim(trailing '.' from to_char(a.value,'fm99D9')) else ' ' end as LTP_VAL,
 b.person_id as PERSON_ID
from b left join peevf a
on b.person_id = a.person_id
  and b.element_name = a.element_name
  and b.msd = a.EFFECTIVE_END_DATE
;

SQL2:

Select using a max over by. You can get rid of the rowid if the order of the result does not matter or if there is another order.

select 
  LATEST_EFF_DT_BONUS,
  LATEST_EFF_DT_LTP,
  BONUS_VAL,
  LTP_VAL,
  person_id
from (
 select
  case when element_name = 'Bonus' then to_char(EFFECTIVE_START_DATE,'DD-MON-YYYY') else ' ' end  as LATEST_EFF_DT_BONUS,
  case when element_name = 'LTP' then to_char(EFFECTIVE_START_DATE,'DD-MON-YYYY') else ' ' end as LATEST_EFF_DT_LTP,
  case when element_name = 'Bonus' then trim(trailing '.' from to_char(value,'fm99D9')) else ' ' end as BONUS_VAL,
  case when element_name = 'LTP' then trim(trailing '.' from to_char(value,'fm99D9')) else ' ' end as LTP_VAL,
  max(effective_end_date)    over ( PARTITION BY ELEMENT_NAME,PERSON_ID order by PERSON_ID) as mo,
  EFFECTIVE_END_DATE,
  rowid rn,
  person_id
 from peevf
) a
where
a.EFFECTIVE_END_DATE = a.mo
order by rn
;

Output:

LATEST_EFF_DT_BONUS LATEST_EFF_DT_LTP BONUS_VAL LTP_VAL PERSON_ID
11-APR-2022 12 19
19-APR-2022 9 19
01-JAN-2019 5 8
11-JAN-2021 11.1 10
11-APR-2019 9.8 1

DDL:

CREATE TABLE PEEVF
    ("EFFECTIVE_START_DATE" timestamp, "EFFECTIVE_END_DATE" timestamp, "PERSON_ID" int, "ELEMENT_NAME" varchar2(5), "VALUE" number)
;

INSERT ALL 
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('01-Jan-2021 12:00:00 AM', '10-Jan-2021 12:00:00 AM', 19, 'Bonus', 9.1)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('11-Jan-2021 12:00:00 AM', '28-Mar-2021 12:00:00 AM', 19, 'Bonus', 11)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('29-Mar-2021 12:00:00 AM', '10-Apr-2021 12:00:00 AM', 19, 'Bonus', 11.1)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('11-Apr-2021 12:00:00 AM', '31-Dec-2021 12:00:00 AM', 19, 'Bonus', 10)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('11-Apr-2022 12:00:00 AM', '30-Apr-2022 12:00:00 AM', 19, 'Bonus', 12)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('19-Apr-2022 12:00:00 AM', '30-May-2022 12:00:00 AM', 19, 'LTP', 09)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('01-Jan-2019 12:00:00 AM', '30-Apr-2019 12:00:00 AM', 8, 'Bonus', 5)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('11-Mar-2021 12:00:00 AM', '10-Jan-2021 12:00:00 AM', 10, 'Bonus', 11)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('11-Jan-2021 12:00:00 AM', '28-Mar-2021 12:00:00 AM', 10, 'Bonus', 11.1)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('20-Jan-2019 12:00:00 AM', '08-Mar-2019 12:00:00 AM', 1, 'LTP', 9)
    INTO PEEVF ("EFFECTIVE_START_DATE", "EFFECTIVE_END_DATE", "PERSON_ID", "ELEMENT_NAME", "VALUE")
         VALUES ('11-Apr-2019 12:00:00 AM', '19-Aug-2019 12:00:00 AM', 1, 'LTP', 9.8)
SELECT * FROM dual
;

Upvotes: 1

Related Questions