11 Replies Latest reply: Mar 18, 2013 12:29 PM by Costa RSS

    find latest value based on defined date

    Costa
      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
          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
            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
              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
                sorry SB, It happens by mistake. I edited the output
                • 5. Re: find latest value based on defined date
                  Rahul_India
                  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
                    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
                      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
                        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
                          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
                            Thanks venkatesh, Its working perfectly.
                            • 11. Re: find latest value based on defined date
                              Costa
                              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;