Find rows for an item where all option dates are inactive

DMADARAS · #1 (**permalink**) 01-06-11, 13:32

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.

Stealth_DBA · #2 (**permalink**) 01-06-11, 14:46

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

DMADARAS · #3 (**permalink**) 01-06-11, 15:20

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.

Stealth_DBA · #4 (**permalink**) 01-06-11, 15:32

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.

DMADARAS · #5 (**permalink**) 01-06-11, 15:44

Sorry... I was having a senior moment...

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

David M.

Internet Services

Web Development

Digital Marketing

Consumer Tech