Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2011
    Posts
    3

    Unanswered: Find rows for an item where all option dates are inactive

    I may be over-thinking this problem... but I can't seem to get there from here...

    1. I have a list of entries in a table where each item can have multiple entries.
    2. Each item entry contains a process option - along with a start-date and end-date to indicate whether the option is currently active, currently inactive, or active/inactive at some time in the future.
    3. I need to find a list of items where all of the option rows for an item are inactive (all rows have an end-date < the current date).
    4. I then need to find the max date value in the overall result list in order to display the last date any of the options went inactive.
    5. It doesn't matter if there are multiple rows with the same max date or which option it was. I just need the item # and a single max end-date value.

    Example :
    Item # Option Start Date End Date
    --------- --------- ------------- -----------
    110 E 07-23-2004 12-31-9999 < active

    201 A 07-23-2004 10-10-2004 < inactive
    201 B 10-10-2004 10-11-2004 < inactive
    201 B 10-14-2004 10-15-2004 < inactive
    201 B 11-13-2004 01-05-2011 < inactive
    201 C 01-01-2011 01-05-2011 < inactive
    201 E 04-08-2010 04-08-2010 < inactive

    401 A 01-10-2004 03-14-2007 < inactive
    401 B 10-10-2004 10-11-2004 < inactive
    401 B 10-14-2004 10-15-2004 < inactive
    401 B 11-13-2004 02-01-2011 < stops on 2/1/11

    402 A 01-10-2004 03-14-2007 < inactive
    402 B 10-10-2004 10-11-2004 < inactive
    402 B 10-14-2004 10-15-2004 < inactive
    402 B 11-13-2004 12-31-9999 < active


    I do not want to pick up any of the rows for items 110, 401, 402 because at least one of the options are active.

    I would only want to pick the rows associated with item 201 (all options are inactive). Two rows have the same max last date (01-05-2011) where the options went inactive. I only care about the item # and the max last date for that item - not which option.

    I've tried using various combinations of max(end-date) and group by, group by having, and sub-selects without luck.

    Any help would be greatly appreciated!
    Thanks, Dave M.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    DMADARAS, here is the first thing that comes to mind:
    Code:
    SELECT ITEM_NO, MAX(END_DT)
    FROM table-name
    WHERE ITEM_NO NOT IN (SELECT ITEM_NO
                          FROM TABLE-NAME
                          WHERE END_DT > CURRENT DATE
                        )
    GROUP BY ITEM_NO
    
    
    WITH TEST_TAB (ITEM_NO, STATUS, ST_DT, END_DT)
      AS (
    SELECT 110, 'E', DATE('07/23/2004'), DATE('12/31/9999') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 201, 'A', DATE('07/23/2004'), DATE('10/10/2004') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 201, 'B', DATE('10/10/2004'), DATE('10/11/2004') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 201, 'B', DATE('10/14/2004'), DATE('10/15/2004') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 201, 'B', DATE('11/13/2004'), DATE('01/05/2011') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 201, 'C', DATE('01/01/2011'), DATE('01/05/2011') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 201, 'E', DATE('04/08/2010'), DATE('04/08/2010') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 401, 'A', DATE('01/10/2004'), DATE('03/14/2007') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 401, 'B', DATE('10/10/2004'), DATE('10/11/2004') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 401, 'B', DATE('10/14/2004'), DATE('10/15/2004') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 401, 'B', DATE('11/13/2004'), DATE('02/01/2011') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 402, 'A', DATE('01/10/2004'), DATE('03/14/2007') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 402, 'B', DATE('10/10/2004'), DATE('10/11/2004') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 402, 'B', DATE('10/14/2004'), DATE('10/15/2004') FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 402, 'B', DATE('11/13/2004'), DATE('12/31/9999') FROM SYSIBM.SYSDUMMY1
    )
    SELECT ITEM_NO, MAX(END_DT) AS MAX_DT
    FROM TEST_TAB
    WHERE ITEM_NO NOT IN (SELECT ITEM_NO
                          FROM TEST_TAB
                          WHERE END_DT > CURRENT DATE
                        )
    GROUP BY ITEM_NO
    
    ITEM_NO     MAX_DT    
    ----------- ----------
            201 01/05/2011
    Last edited by Stealth_DBA; 01-06-11 at 16:00.

  3. #3
    Join Date
    Jan 2011
    Posts
    3

    Re:

    Thanks for the info, but how could I use this for a table with thousands of entries?

    The example I gave was just a small snippet of what was in the overeall table.

    I'm trying to find all item entries in this table that have all of their option rows inactive and ignore the rest.

    I need to find the max end date for each item entry found where all of the option rows are inactive.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    That is what it will do. For each set of unique Item numbers, you will get them and the max date associated with them. Since you only had one item number that would be returned in the result, that is all that you get.

    You can test this by changing one of the other item numbers so it would also be returned.

  5. #5
    Join Date
    Jan 2011
    Posts
    3
    Sorry... I was having a senior moment...

    Works fine... it giave me exactly what I was looking for!
    Many thanks!

    David M.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •