This discussion is archived
11 Replies Latest reply: Mar 18, 2013 10:29 AM by Costa RSS

find latest value based on defined date

Costa Newbie
Currently Being Moderated
Hi all,

In my below example, I want to find the latest symptom and name which has been added after 24-mar-2012 (not before that) .Not getting proper output




with xx as
(select 101 as ID, 'A01' as name, '03/24/2012' as create_date from dual
union all
select 101, 'A01', '03/24/2012' from dual
union all
select 102 , 'A02', '03/24/2012' from dual
union all
select 101 , 'A01', '03/30/2012' from dual
union all
select 102 , 'A02', '03/30/2012' from dual
union all
select 102 , 'A01', '04/21/2012' from dual
union all
select 101 , 'A01','04/22/2012' from dual

),

xy as
(select 101 as ID, 'asthma' as symptom from dual
union all
select 101 , 'cancer' from dual
union all
select 101 , 'bpressure' from dual
union all
select 102, 'sbp' from dual
union all
select 101 , 'dbp' from dual
union all
select 102, 'allergy' from dual
union all
select 103 , 'cardiac failure' from dual
union all
select 102 , 'sneezing' from dual

)

select xx.name, xy.symptom, xx.create_date from xx, xy
where xx.id=xy.id
group by xx.name, xy.symptom, xx.create_date
having xx.create_date >'03/24/2012'
  • 1. Re: find latest value based on defined date
    sb92075 Guru
    Currently Being Moderated
    Costa wrote:
    Hi all,

    In my below example, I want to find the latest symptom and name which has been added after 24-mar-2012 (not before that) .Not getting proper output
    what does the proper output look like according to you?
      1  with xx as
      2  (select 101 as ID, 'A01' as name, '03/24/2012' as create_date from dual
      3  union all
      4  select 101, 'A01', '03/24/2012' from dual
      5  union all
      6  select 102 , 'A02', '03/24/2012' from dual
      7  union all
      8  select 101 , 'A01', '03/30/2012' from dual
      9  union all
     10  select 102 , 'A02', '03/30/2012' from dual
     11  union all
     12  select 102 , 'A01', '04/21/2012' from dual
     13  union all
     14  select 101 , 'A01','04/22/2012' from dual
     15  ),
     16  xy as
     17  (select 101 as ID, 'asthma' as symptom from dual
     18  union all
     19  select 101 , 'cancer' from dual
     20  union all
     21  select 101 , 'bpressure' from dual
     22  union all
     23  select 102, 'sbp' from dual
     24  union all
     25  select 101 , 'dbp' from dual
     26  union all
     27  select 102, 'allergy' from dual
     28  union all
     29  select 103 , 'cardiac failure' from dual
     30  union all
     31  select 102 , 'sneezing' from dual
     32  )
     33  select xx.name, xy.symptom, xx.create_date from xx, xy
     34  where xx.id=xy.id
     35  group by xx.name, xy.symptom, xx.create_date
     36* having xx.create_date >'03/24/2012'
    SQL> /
    
    NAM SYMPTOM         CREATE_DAT
    --- --------------- ----------
    A01 cancer          04/22/2012
    A01 bpressure       04/22/2012
    A01 bpressure       03/30/2012
    A01 allergy         04/21/2012
    A01 asthma          03/30/2012
    A01 sbp             04/21/2012
    A01 sneezing        04/21/2012
    A02 sneezing        03/30/2012
    A01 asthma          04/22/2012
    A01 cancer          03/30/2012
    A02 sbp             03/30/2012
    
    NAM SYMPTOM         CREATE_DAT
    --- --------------- ----------
    A01 dbp             04/22/2012
    A01 dbp             03/30/2012
    A02 allergy         03/30/2012
    
    14 rows selected.
    
    SQL> 
  • 2. Re: find latest value based on defined date
    Costa Newbie
    Currently Being Moderated
    Thanks Sb for ur quick response.

    I want the below output

    A01 bpressure 03/30/2012
    A01 asthma 03/30/2012
    A02 sneezing 03/30/2012
    A01 cancer 03/30/2012
    A02 sbp 03/30/2012
    A01 dbp 03/30/2012
    A02 allergy 03/30/2012
    A01 dbp 04/22/2012
    Advance thanks

    Edited by: Costa on Mar 17, 2013 1:32 PM
  • 3. Re: find latest value based on defined date
    sb92075 Guru
    Currently Being Moderated
    Costa wrote:
    Thanks Sb for ur quick response.

    I want the below output

    A01 bpressure 03/30/2012
    A01 asthma 03/30/2012
    A02 sneezing 03/30/2012
    A01 cancer 03/30/2012
    A02 sbp 03/30/2012
    A01 dbp 03/30/2012
    A02 allergy 03/30/2012

    Advance thanks
    why no dates in April included in desired results?
  • 4. Re: find latest value based on defined date
    Costa Newbie
    Currently Being Moderated
    sorry SB, It happens by mistake. I edited the output
  • 5. Re: find latest value based on defined date
    Rahul_India Journeyer
    Currently Being Moderated
    Costa wrote:
    sorry SB, It happens by mistake. I edited the output
    use to_date
    having xx.create_date > to_date('03/24/2013','mm/dd/yyyy');
  • 6. Re: find latest value based on defined date
    EdStevens Guru
    Currently Being Moderated
    Rahul India wrote:
    Costa wrote:
    sorry SB, It happens by mistake. I edited the output
    use to_date
    having xx.create_date > to_date('03/24/2013','mm/dd/yyyy');
    Incomplete.
    His 'with' statements are producing 'date' columns that are really varchar2.
  • 7. Re: find latest value based on defined date
    EdStevens Guru
    Currently Being Moderated
    Costa wrote:
    Hi all,

    In my below example, I want to find the latest symptom and name which has been added after 24-mar-2012 (not before that) .Not getting proper output




    with xx as
    (select 101 as ID, 'A01' as name, '03/24/2012' as create_date from dual
    union all
    select 101, 'A01', '03/24/2012' from dual
    union all
    select 102 , 'A02', '03/24/2012' from dual
    union all
    select 101 , 'A01', '03/30/2012' from dual
    union all
    select 102 , 'A02', '03/30/2012' from dual
    union all
    select 102 , 'A01', '04/21/2012' from dual
    union all
    select 101 , 'A01','04/22/2012' from dual

    ),

    xy as
    (select 101 as ID, 'asthma' as symptom from dual
    union all
    select 101 , 'cancer' from dual
    union all
    select 101 , 'bpressure' from dual
    union all
    select 102, 'sbp' from dual
    union all
    select 101 , 'dbp' from dual
    union all
    select 102, 'allergy' from dual
    union all
    select 103 , 'cardiac failure' from dual
    union all
    select 102 , 'sneezing' from dual

    )

    select xx.name, xy.symptom, xx.create_date from xx, xy
    where xx.id=xy.id
    group by xx.name, xy.symptom, xx.create_date
    having xx.create_date >'03/24/2012'
    Your 'with' table definition is producing a 'date' column that is really not a DATE but a VARCHAR2. Then in your WHERE clause, you are providing a 'date' that is really a character string. Doing string comparisons, '28-Feb-2013' comes after '01-Apr-2013'. Deal with dates as DATE, not character strings. Make sure all date columns in your real tables are typed as DATE. Check out the to_char and to_date functions.
  • 8. Re: find latest value based on defined date
    Costa Newbie
    Currently Being Moderated
    Thank you very much for rectifying the mistake.

    But I am not getting the below output although I have modified the script. Group by clause is not working. Why the data is not filtered
    Please help

    A01 bpressure
    A01 asthma
    A02 sneezing
    A01 cancer
    A02 sbp
    A01 dbp
    A02 allergy


    select xx.name, xy.symptom from xx, xy
    where xx.id=xy.id
    group by xx.name, xy.symptom
    having xx.create_date > to_date('03/24/2013','mm/dd/yyyy');
  • 9. Re: find latest value based on defined date
    Venkadesh Raja Pro
    Currently Being Moderated
    with xx as
    (select 101 as ID, 'A01' as name, '03/24/2012' as create_date from dual
    union all
    select 101, 'A01', '03/24/2012' from dual
    union all
    select 102 , 'A02', '03/24/2012' from dual
    union all
    select 101 , 'A01', '03/30/2012' from dual
    union all
    select 102 , 'A02', '03/30/2012' from dual
    union all
    select 102 , 'A01', '04/21/2012' from dual
    union all
    select 101 , 'A01','04/22/2012' from dual
    
    ),
    
    xy as
    (select 101 as ID, 'asthma' as symptom from dual
    union all
    select 101 , 'cancer' from dual
    union all
    select 101 , 'bpressure' from dual
    union all
    select 102, 'sbp' from dual
    union all
    select 101 , 'dbp' from dual
    union all
    select 102, 'allergy' from dual
    union all
    select 103 , 'cardiac failure' from dual
    union all
    select 102 , 'sneezing' from dual
    
    )
    
    select xx.name, xy.symptom, xx.create_date from xx, xy
    where xx.id=xy.id
    and substr(xx.create_date,4,2)>24
    group by xx.name, xy.symptom,xx.create_date;
  • 10. Re: find latest value based on defined date
    Costa Newbie
    Currently Being Moderated
    Thanks venkatesh, Its working perfectly.
  • 11. Re: find latest value based on defined date
    Costa Newbie
    Currently Being Moderated
    Although the substring function is used to get particular output, it is not working properly for advanced date (I have added two more records in table xx in the last part)

    Requirement is to find the symptom > 24/03/2012

    But the record (select 102 , 'A01', '05/18/2012' from dual) should be captured which is not happening

    Also the record (select 101 , 'A01','01/28/2012' from dual) should not captured as it is less than my require date.


    Can anyone help me once again. Advance thanks


    with xx as
    (select 101 as ID, 'A01' as name, '03/24/2012' as create_date from dual
    union all
    select 101, 'A01', '03/24/2012' from dual
    union all
    select 102 , 'A02', '03/24/2012' from dual
    union all
    select 101 , 'A01', '03/30/2012' from dual
    union all
    select 102 , 'A02', '03/30/2012' from dual
    union all
    select 102 , 'A01', '04/21/2012' from dual
    union all
    select 101 , 'A01','04/22/2012' from dual
    union all
    select 102 , 'A01', '05/18/2012' from dual
    union all
    select 101 , 'A01','01/28/2012' from dual

    ),

    xy as
    (select 101 as ID, 'asthma' as symptom from dual
    union all
    select 101 , 'cancer' from dual
    union all
    select 101 , 'bpressure' from dual
    union all
    select 102, 'sbp' from dual
    union all
    select 101 , 'dbp' from dual
    union all
    select 102, 'allergy' from dual
    union all
    select 103 , 'cardiac failure' from dual
    union all
    select 102 , 'sneezing' from dual

    )

    select xx.name, xy.symptom, xx.create_date from xx, xy
    where xx.id=xy.id
    and substr(xx.create_date,4,2)>24
    group by xx.name, xy.symptom,xx.create_date;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points