1 2 Previous Next 17 Replies Latest reply: May 17, 2012 10:36 AM by 937284 RSS

    Find rows from a table with no Activities

    937284
      Hi,

      I have a ‘Data’ table in relation with a ‘Note’ table with a date and I would like to find all the rows in the ‘Data’ table that do not have any ‘Note’ taken in the the last 3 months (including no ‘Note’ at all)?

      THanks,
        • 1. Re: Find rows from a table with no Activities
          sb92075
          934281 wrote:
          Hi,

          I have a ‘Data’ table in relation with a ‘Note’ table with a date and I would like to find all the rows in the ‘Data’ table that do not have any ‘Note’ taken in the the last 3 months (including no ‘Note’ at all)?

          THanks,
          post CREATE TABLE statements for both tables.
          • 2. Re: Find rows from a table with no Activities
            rp0428
            >
            I have a ‘Data’ table in relation with a ‘Note’ table with a date and I would like to find all the rows in the ‘Data’ table that do not have any ‘Note’ taken in the the last 3 months (including no ‘Note’ at all)?
            >
            Create a query to get the 'ID' values for records with 'Notes' and then query the 'Data' table for records with 'ID' values that are not in the first query.

            Here it is for the SCOTT DEPT and EMP tables where the 'ID' column is 'deptno' and the 'Note' column is hiredate
            select * from dept d where deptno not in (                
            select distinct deptno
            from emp where hiredate > sysdate - 90
            )
            Hiredate is acting as the 'date the note was taken' so the inner query gets the list of 'IDs' (deptno) that DO HAVE 'notes' taken in the last 90 days.

            You want the 'IDs' that DO NOT so you want all ID's from DEPT that ARE NOT in the list.
            • 3. Re: Find rows from a table with no Activities
              908002
              select * from data d
              where not exists( select * from note where dataid=d.id)
              or not exists( select * from note where dataid=d.id and  notedate >=sysdate+90);
              Edited by: Kiran on May 14, 2012 9:05 PM
              • 4. Re: Find rows from a table with no Activities
                937284
                Thanks for the great and quick answers!

                Could someone tell me what is the difference between the 2 methodes (Not Exist vs Not In)?

                Also, I would I add in the Select the Date of the most recent Note and sort ASC so that I could have the Data that had not activity in the last 3 months and sorted by the one that had no activity the longest time ago first?

                Thanks,
                • 5. Re: Find rows from a table with no Activities
                  937284
                  Marked as aswered by mistake
                  • 6. Re: Find rows from a table with no Activities
                    Frank Kulash
                    Hi,
                    934281 wrote:
                    Thanks for the great and quick answers!

                    Could someone tell me what is the difference between the 2 methodes (Not Exist vs Not In)?
                    They can usually be used to get the same results. Sometimes, it's easier to understand the logic of one rather than the other. Use whichever one you prefer. I can't guarantee that the optimizer won't do exactly the same thing, regardless of which one you use.
                    Also, I would I add in the Select the Date of the most recent Note and sort ASC so that I could have the Data that had not activity in the last 3 months and sorted by the one that had no activity the longest time ago first?
                    In that case, I suggest a join, rather than either EXISTS or IN:
                    WITH     got_r_num     AS
                    (
                         SELECT     d.*
                         ,     n.notedate
                         ,     ROW_NUMBER () OVER ( PARTITION BY  d.id
                                                   ORDER BY          n.notedate     DESC     NULLS LAST
                                           )         AS r_num
                         FROM           data        d
                         LEFT OUTER JOIN      note        n  ON  n.dataid     = d.id
                    )
                    SELECT       *     -- Or list all couumns except r_num
                    FROM       got_r_num
                    WHERE       r_num          = 1
                    AND       NVL ( notedate
                               , SYSDATE - 99     -- or any date more than 3 months ago
                               ) <  ADD_MONTHS ( SYSDATE
                                                  , -3
                                         )
                    ORDER BY  notedate
                    ;
                    If you'd care to post some sample data (CREATE TABLE and INSERT statements), and the results you want from that data, then I could test this.

                    When you say you don't want dates within the last 3 months, I assume you're including dates in the future. (Future dates may be impossible in your table, anyway.)

                    3 months can be as short as 89 days or as long as 92 days. I used ADD_MONTHS (sysdate, -3) to get exactly 3 months; you can use SYSDATE - 90 if that suits your needs.
                    • 7. Re: Find rows from a table with no Activities
                      937284
                      The last query return 1 row, I guess I was not clear enough.

                      I do not want the one Data row with the longest time without activity, I want all the Data with no Note added in the last 3 months, then I want those rows order by the ones with the oldest Note first (Files with no activity in the last 3 months ordered by the oldest activity first), see sample data and expected result below:

                      Data:
                      PK Name
                      1 aaa
                      2 bbb
                      3 ccc

                      Note (sorted by FK for clarity):
                      FK Comment Date
                      1 zzz 15-01-12
                      1 yyy 15-02-12
                      1 xxx 15-03-12
                      2 rrr 15-11-11
                      2 vvv 15-12-11
                      2 uuu 15-01-12
                      3 ttt 15-09-11
                      3 sss 15-10-11

                      Result
                      PK Name Comment Date
                      3 ccc sss 15-10-11
                      2 bbb uuu 15-01-12
                      • 8. Re: Find rows from a table with no Activities
                        937284
                        Hi again,

                        Please let me know if my request is not clear enough.

                        Thanks again,
                        • 9. Re: Find rows from a table with no Activities
                          Frank Kulash
                          Hi,
                          934281 wrote:
                          Hi again,

                          Please let me know if my request is not clear enough.
                          It's not clear at all.

                          Post CREATE TABLE and INSERT statements for some sample data, and the results you want from that data.
                          What's wrong with the solution I posted earlier, {message:id=10336245} ? Point out where it is getting the wrong results, and explain, step by step, how you might get the right results in those places.
                          • 10. Re: Find rows from a table with no Activities
                            Hoek
                            How about using analytics here?
                            Something like:
                            SQL> with data as (
                              2  select 1 pk, 'aaa' name from dual union
                              3  select 2, 'bbb' from dual union
                              4  select 3, 'ccc' from dual
                              5  )
                              6  ,    note as (
                              7  select 1 fk, 'zzz' note, to_date('15-01-12', 'dd-mm-yy') dt from dual union
                              8  select 1, 'yyy', to_date('15-02-12', 'dd-mm-yy') from dual union
                              9  select 1, 'xxx', to_date('15-03-12', 'dd-mm-yy') from dual union
                             10  select 2, 'rrr', to_date('15-11-11', 'dd-mm-yy') from dual union
                             11  select 2, 'vvv', to_date('15-12-11', 'dd-mm-yy') from dual union
                             12  select 2, 'uuu', to_date('15-01-12', 'dd-mm-yy') from dual union
                             13  select 3, 'ttt', to_date('15-09-11', 'dd-mm-yy') from dual union
                             14  select 3, 'sss', to_date('15-10-11', 'dd-mm-yy') from dual
                             15  )
                             16  --
                             17  -- actual query:
                             18  --
                             19  select pk
                             20  ,      name
                             21  ,      note
                             22  ,      dt
                             23  from ( select d.pk
                             24         ,      d.name
                             25         ,      n.note
                             26         ,      n.dt
                             27         ,      row_number() over (partition by d.pk order by n.dt desc) rn
                             28         from   data d
                             29         ,      note n
                             30         where  d.pk = n.fk 
                             31         and    dt < trunc(sysdate-90)
                             32       )
                             33  where rn=1
                             34  order by dt; 
                            
                                    PK NAM NOT DT
                            ---------- --- --- -------------------
                                     3 ccc sss 15-10-2011 00:00:00
                                     2 bbb uuu 15-01-2012 00:00:00
                                     1 aaa yyy 15-02-2012 00:00:00
                            From what I can make of your requirement, PK value 1 should also be in the expected resultset.
                            If that;s not correct then please explain why.
                            • 11. Re: Find rows from a table with no Activities
                              937284
                              Hoek :"From what I can make of your requirement, PK value 1 should also be in the expected resultset.
                              If that;s not correct then please explain why."

                              So first I want to exclude de rows in Data where there is at least one Note added in the last 3 months (those have recent activities and not a concern), So for Data 1 there is a Note added on 15-03-12 which is in the last 3 months.

                              Then for Data rows with Note(s) added more then 3 months ago, I need each Data rows with the coresponding most recent Note.Date (but older then 3 months), ordered by Note.Date ASC.

                              And I tried your query and it also ruturn only 1 row, here is what I entered, Please let me know if I did note copy it properly:

                              SELECT
                              NODOSSIER, REPRESENTANTNOCERTIFICAT
                              , DETAIL, DATESUIVI
                              FROM
                              (
                              SELECT
                              E.NODOSSIER, E.REPRESENTANTNOCERTIFICAT
                              , S.DETAIL, S.DATESUIVI
                              , ROW_NUMBER () OVER
                                   (
                                   PARTITION BY E.NODOSSIER
                                   ORDER BY S.DATESUIVI DESC
                                   ) AS RomNum
                              FROM
                              ENQUETE E
                              , SUIVI S
                              WHERE
                              E.NODOSSIER = S.NODOSSIER
                              AND DATESUIVI < ADD_MONTHS(SYSDATE,-3)
                              )
                              WHERE
                              RowNum = 1
                              ORDER BY
                              DATESUIVI
                              • 12. Re: Find rows from a table with no Activities
                                sb92075
                                whose is easier to read & understand; your above or mine below?
                                SELECT NODOSSIER, 
                                       REPRESENTANTNOCERTIFICAT, 
                                       DETAIL, 
                                       DATESUIVI 
                                FROM   (SELECT E.NODOSSIER, 
                                               E.REPRESENTANTNOCERTIFICAT, 
                                               S.DETAIL, 
                                               S.DATESUIVI, 
                                               Row_number () over ( PARTITION BY E.NODOSSIER ORDER BY 
                                               S.DATESUIVI DESC 
                                                      ) AS 
                                               RomNum 
                                        FROM   ENQUETE E, 
                                               SUIVI S 
                                        WHERE  E.NODOSSIER = S.NODOSSIER 
                                               AND DATESUIVI < Add_months(SYSDATE, -3)) 
                                WHERE  ROWNUM = 1 
                                ORDER  BY DATESUIVI 
                                • 13. Re: Find rows from a table with no Activities
                                  937284
                                  I agree that your is easier to read.

                                  And I found out why I was getting only the first row, I renamed by error the "AS rn" to AS RowNum to be more clear, but then when I used it later it was the Keyword :p

                                  Replaced with First_Row and now it works as expected :D

                                  Thanks you all for the help.
                                  • 14. Re: Find rows from a table with no Activities
                                    937284
                                    After testing some more, I realize that it is still not exactly what I need
                                    1 2 Previous Next