1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to count when multiple conditions must be met & possible no table match

Discussion in 'SQL PL/SQL' started by OldSchoolCoder, Mar 13, 2014.

  1. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I have literally 19 tables, but will use 5 in my example. Each are defined with an ID and a timestamp. However, not all tables contain an entry for every ID. I'm to count distinct ID's where the date is older than 6 months. So sample data may look like this:

    Table ID Date
    A 1 12/01/12
    A 2 10/03/12
    A 3 11/01/12
    A 4 01/10/14
    B 1 12/03/12
    B 3 11/30/12
    C 1 12/03/12
    C 2 02/04/13
    C 3 03/01/13
    D 3 01/10/13
    E 4 04/20/14

    My result would be a count of 3. All the items are over 6 months except for item ID 4. The code I used is the following, but it doesn't yeild the correct result. I'm suspecting it has something to do with the tables with missing entries. Any insight? Thanks for your help in advance!
    Code (SQL):
    SELECT COUNT(DISTINCT A.ID) AS TotalItems
    FROM A
    LEFT JOIN B
      ON A.ID = B.ID
    LEFT JOIN C
      ON A.ID = C.ID
    LEFT JOIN D
      ON A.ID = D.ID
    LEFT JOIN E
      ON A.ID = E.ID
    WHERE TO_DATE(TRUNC(A.DATE), 'DD-MON-YYYY')
             <= ADD_MONTHS(SYSDATE,-6)
      AND TO_DATE(TRUNC(B.DATE), 'DD-MON-YYYY')
             <= ADD_MONTHS(SYSDATE,-6)
      AND TO_DATE(TRUNC(C.DATE), 'DD-MON-YYYY')
             <= ADD_MONTHS(SYSDATE,-6)
      AND TO_DATE(TRUNC(D.DATE), 'DD-MON-YYYY')
             <= ADD_MONTHS(SYSDATE,-6)
      AND TO_DATE(TRUNC(E.DATE), 'DD-MON-YYYY')
             <= ADD_MONTHS(SYSDATE,-6);
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    All of the AND Conditions are causing you to return no rows; this works:


    Code (SQL):

    SQL> SELECT COUNT(DISTINCT A.ID) AS TotalItems
      2  FROM A
      3  LEFT JOIN B
      4    ON A.ID = B.ID
      5  LEFT JOIN C
      6    ON A.ID = C.ID
      7  LEFT JOIN D
      8    ON A.ID = D.ID
      9  LEFT JOIN E
     10    ON A.ID = E.ID
     11  WHERE TO_DATE(TRUNC(A.DT), 'DD-MON-YY')
     12           <= ADD_MONTHS(SYSDATE,-6);


    TOTALITEMS
    ----------
             3


    SQL>
     
     
  3. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    David,

    My apologies, I should've clarified that all of the dates in all of the tables for each ID, must meet the 6 month criteria, not just the one in table A. Hence the many AND's.

    Thank you for your consistent offer of help :)
     
  4. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Miracles never cease! I think I've figured it out! I just added each of the AND's to the Left Joins and it appears to be giving me the right results. :)
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I do understand that but it appears these multiple AND conditions cause Oracle to return no rows when they are all met:


    Code (SQL):

    SQL> SELECT DISTINCT A.ID
      2  FROM A
      3  LEFT JOIN B
      4    ON A.ID = B.ID
      5  LEFT JOIN C
      6    ON A.ID = C.ID
      7  LEFT JOIN D
      8    ON A.ID = D.ID
      9  LEFT JOIN E
     10    ON A.ID = E.ID
     11  WHERE TO_DATE(TRUNC(A.DT), 'DD-MON-YYYY')
     12           <= ADD_MONTHS(SYSDATE,-6)
     13    AND TO_DATE(TRUNC(B.DT), 'DD-MON-YYYY')
     14           <= ADD_MONTHS(SYSDATE,-6)
     15    AND TO_DATE(TRUNC(C.DT), 'DD-MON-YYYY')
     16           <= ADD_MONTHS(SYSDATE,-6)
     17    AND TO_DATE(TRUNC(D.DT), 'DD-MON-YYYY')
     18           <= ADD_MONTHS(SYSDATE,-6)
     19    AND TO_DATE(TRUNC(E.DT), 'DD-MON-YYYY')
     20           <= ADD_MONTHS(SYSDATE,-6);


    no ROWS selected


    SQL>
     

    You need to isolate those conditions on a per-table basis as shown in the example below:


    Code (SQL):

    SQL> WITH av AS(
      2          SELECT id a_id
      3          FROM a
      4          WHERE trunc(dt) <= add_months(sysdate, -6)
      5  ),
      6  bv AS (
      7          SELECT id b_id
      8          FROM b
      9          WHERE trunc(dt) <= add_months(sysdate, -6)
     10  ),
     11  cv AS (
     12          SELECT id c_id
     13          FROM c
     14          WHERE trunc(dt) <= add_months(sysdate, -6)
     15  ),
     16  dv AS (
     17          SELECT id d_id
     18          FROM d
     19          WHERE trunc(dt) <= add_months(sysdate, -6)
     20  ),
     21  ev AS (
     22          SELECT id e_id
     23          FROM e
     24          WHERE trunc(dt) <= add_months(sysdate, -6)
     25  )
     26  SELECT COUNT(DISTINCT A_ID) AS TotalItems
     27  FROM AV
     28  LEFT JOIN BV
     29    ON A_ID = B_ID
     30  LEFT JOIN CV
     31    ON A_ID = C_ID
     32  LEFT JOIN DV
     33    ON A_ID = D_ID
     34  LEFT JOIN EV
     35    ON A_ID = E_ID;


    TOTALITEMS
    ----------
             3


    SQL>
     

    The conditions are met across all tables and the total is what you expect it to be.
     
    OldSchoolCoder likes this.
  6. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Oh! That is ugly! ;) I'll give that a try because what I tried gave me bad results! Thank you again!
     
  7. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I finally got around to this again - it did work! Thank you very much!
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are welcome, I'm happy I could assist.