Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Posts
    13

    Unanswered: How to find active and inactive based on cut off date?

    Let say i have a table as follow;

    KEY STATUS STARTDATE ENDDATE
    100 Active JUL 2015 SEP 2015
    100 Active SEP 2015 NOV 2015
    100 Inavtive Nov 2015 Dec 2015
    101 Active Aug 2015 Nov 2015

    How do I find all active status with cut off date say Nov 2015? also, the list has to be unique

    My desire results as follow;
    100 Active SEP 2015 NOV 2015
    101 Active Aug 2015 Nov 2015

  2. #2
    Join Date
    Mar 2007
    Posts
    621
    I see no table in your post, there are only five rows with some value(s).

    Let say that the "cut off date" is somehow related to the values under ENDDATE (let pretend they are formatted).
    Let say that "active status" means the value 'Active' under STATUS.
    No idea about "unique list" as the uniquess is based on the set of values. However it is impossible to deduce which values should be taken into account.

    Anyway, if you want to filter the result set to the required rows only, use appropriate WHERE clause.
    As you did not post any SQL (CREATE TABLE + INSERT statements), I will not post any as well. I cannot query unknown table(s) having unknown column(s) with unknown data type(s) (plus testing its correctness).

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,504
    Code:
    select KEY,STATUS,STARTDATE,ENDDATE
    from my_table
    where STATUS = 'Active'
    and to_date(ENDDATE,'mon yyyy') >  to_date('Nov 2015','mon yyyy')
    order by key;
    
    If the two date columns are actually date columns and not varchar2 then
    
    select KEY,STATUS,STARTDATE,ENDDATE
    from my_table
    where STATUS = 'Active'
    and ENDDATE >  to_date('Nov 2015','mon yyyy')
    order by key;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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