13 Replies Latest reply: Jan 31, 2013 4:01 PM by Frank Kulash RSS

    selecting unused dates for list

    985940
      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
          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
            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
              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
                13 would be the year, or 12, 11 etc
                • 5. Re: selecting unused dates for list
                  Solomon Yakobson
                  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
                    >
                    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
                      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
                        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
                          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
                            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-Oracle
                              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
                                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
                                  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.