This discussion is archived
13 Replies Latest reply: Jan 31, 2013 2:01 PM by Frank Kulash RSS

selecting unused dates for list

985940 Newbie
Currently Being Moderated
Ok, last one for today..

I'm trying to populate a select list with Month-Year values between the first entry in the table and the previous month(sysdate - 1 month).

At the moment I have this, which will select all months between the last entry in the table and sysdate-1.
select to_char( add_months( start_date, level-1 ), 'MON-YY' ) d, to_char( add_months( start_date, level-1 ), 'MON-YY' ) r
from (select add_months(max(date_received),1) start_date, add_months(sysdate, -1) end_date from localdata) where start_date < end_date
connect by level <= months_between(trunc(end_date,'MM'), trunc(start_date,'MM') ) + 1
But I want it to use the first entry and select all months between then and sysdate-1 where there are no entries in the table.

i tried something like this, but it returned no data:
select to_char( add_months( start_date, level-1 ), 'MON-YY' ) d, to_char( add_months( start_date, level-1 ), 'MON-YY' ) r
from (select min(date_received) start_date, add_months(sysdate, -1) end_date from localdata) where start_date < end_date and to_char(start_date, 'MON-YY') not in (select to_char(date_received, 'MON-YY') from localdata)
connect by level <= months_between(trunc(end_date,'MM'), trunc(start_date,'MM') ) + 1
  • 1. Re: selecting unused dates for list
    Solomon Yakobson Guru
    Currently Being Moderated
    Assuming localdata has multiple date_received for same month:
    with t as (
               select  trunc(min(date_received),'mm') min_month
                 from  localdata
              )
     select  date_received
       from  localdata
    union all
     (
       select  add_months(min_month,level) dt
         from  t
         connect by add_months(min_month,level) <= sysdate - 1
      minus
       select  trunc(date_received,'mm')
         from  localdata
     )
    /
    SY.
  • 2. Re: selecting unused dates for list
    985940 Newbie
    Currently Being Moderated
    Not sure that's coming out quite right.

    I need them to be in the format MON-YY (JAN-13, FEB-13 etc).
  • 3. Re: selecting unused dates for list
    Solomon Yakobson Guru
    Currently Being Moderated
    pearlyred wrote:

    I need them to be in the format MON-YY (JAN-13, FEB-13 etc).
    And where does 13 come from? Is it day of min(date_received)? If so:
    with t as (
               select  min(date_received) min_dt
                 from  localdata
              )
     select  date_received
       from  localdata
    union all
     (
       select  add_months(min_dt,level) dt
         from  t
         connect by add_months(min_month,level) <= trunc(sysdate,'mm') - 1
      minus
       select  trunc(date_received,'mm') + (min_dt - trunc(min_dt))
         from  localdata,
               t
     )
    / 
    SY.

    Edited by: Solomon Yakobson on Jan 30, 2013 7:37 PM
  • 4. Re: selecting unused dates for list
    985940 Newbie
    Currently Being Moderated
    13 would be the year, or 12, 11 etc
  • 5. Re: selecting unused dates for list
    Solomon Yakobson Guru
    Currently Being Moderated
    Just post a sample of your localdata table (create table + insert data statements) and expected results.

    SY.
  • 6. Re: selecting unused dates for list
    rp0428 Guru
    Currently Being Moderated
    >
    Not sure that's coming out quite right.

    I need them to be in the format MON-YY (JAN-13, FEB-13 etc).
    >
    First get the set of dates correct. Then just add your TO_CHAR to format them the way you want.

    Are the dates corrrect using that query?
  • 7. Re: selecting unused dates for list
    985940 Newbie
    Currently Being Moderated
    Ok, the query works, but it seems to be returning the dates that DO exist in the table, where I want the ones that don't.
  • 8. Re: selecting unused dates for list
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    pearlyred wrote:
    Ok, the query works, but it seems to be returning the dates that DO exist in the table, where I want the ones that don't.
    I think this is what you want:
    SELECT     TO_CHAR ( ADD_MONTHS (first_month, LEVEL - 1)
              , 'MON-YYYY'
              )     AS missing_month
    FROM     (
              SELECT TRUNC ( MIN (date_received)
                               , 'MONTH'
                        )          AS first_month
              FROM   localdata 
         )
    CONNECT BY     LEVEL     <= 1 + MONTHS_BETWEEN ( SYSDATE - 1
                                          , first_month
                                   )
    --
        MINUS
    --
    SELECT  TO_CHAR (date_received, 'MON-YYYY')
    FROM     localdata
    ;
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 9. Re: selecting unused dates for list
    985940 Newbie
    Currently Being Moderated
    Thanks, that get's the right dates.

    This works fine in my query window, but if I add it as the code for an Apex select list it breaks:
    select missing_month d, missing_month r from (
    select to_char(add_months(first_month, level -1), 'MON-YYYY') as missing_month
    from ( select trunc(min(date_received), 'MONTH') as first_month
    from wplas_paper_data)
    connect by level <= 1 + months_between(add_months(sysdate,-1), first_month)
    --
    MINUS
    --
    select to_char(date_received, 'MON-YYYY') from wplas_paper_data)
    I get this error, which I don't really understand when it works ok in the SQL commands section.

    ORA-06550: line 1, column 462: PL/SQL: ORA-00907: missing right parenthesis ORA-00907: missing right parenthesis ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored ORA-06550: line 1
  • 10. Re: selecting unused dates for list
    fac586 Guru
    Currently Being Moderated
    pearlyred wrote:
    Thanks, that get's the right dates.

    This works fine in my query window, but if I add it as the code for an Apex select list it breaks:
    select missing_month d, missing_month r from (
    select to_char(add_months(first_month, level -1), 'MON-YYYY') as missing_month
    from ( select trunc(min(date_received), 'MONTH') as first_month
    from wplas_paper_data)
    connect by level <= 1 + months_between(add_months(sysdate,-1), first_month)
    --
    MINUS
    --
    select to_char(date_received, 'MON-YYYY') from wplas_paper_data)
    I get this error, which I don't really understand when it works ok in the SQL commands section.

    ORA-06550: line 1, column 462: PL/SQL: ORA-00907: missing right parenthesis ORA-00907: missing right parenthesis ORA-06550: line 1, column 7: PL/SQL: SQL Statement ignored ORA-06550: line 1
    Remove the "--" comments. They cause problems in APEX LOV queries.

    It's safer to only use <tt>/* ... */</tt> style comments in APEX SQL.
  • 11. Re: selecting unused dates for list
    Nicosa Expert
    Currently Being Moderated
    pearlyred wrote:
    13 would be the year, or 12, 11 etc
    Please, tell us you're working on an application for historians and that "year 13" is 13AD and not 2013 !
  • 12. Re: selecting unused dates for list
    985940 Newbie
    Currently Being Moderated
    Thanks for the comments tip, it works great now. Just need to work out how to sort it date wise.
  • 13. Re: selecting unused dates for list
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    pearlyred wrote:
    Thanks for the comments tip, it works great now. Just need to work out how to sort it date wise.
    If missing_month is a string, such as 'JAN-2013', then it's no good for sorting, since the string 'JAN-2013' comes after 'FEB-2013' (and also after 'FEB-2014' and 'FEB-2015') because 'J' comes after 'F'. If you want to sort it, then keep the month as a DATE, like this:
    WITH     got_missing_month_s     AS
    (
         SELECT     ADD_MONTH first_month, LEVEL - 1)     AS missing_month_s
         FROM     (
                   SELECT TRUNC ( MIN (date_received)
                                           , 'MONTH'
                                  )          AS first_month
                   FROM   localdata 
              )
         CONNECT BY     LEVEL     <= MONTHS_BETWEEN ( SYSDATE
                                                 , first_month
                                          )
        MINUS
            SELECT  TRUNC (date_received, 'MONTH')
         FROM     localdata
    )
    SELECT       TO_CHAR (missing_month_s, 'MON-YYYY')     AS missing_month_d
    ,       missing_month_s
    FROM       got_missing_month_s
    ORDER BY  missing_month_s
    ;
    The main query has 2 columns:
    missing_month_d is for display: it is a string such as 'JAN-2013'
    missing_month_s is for sorting: it is a DATE.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points