2 Replies Latest reply: Dec 19, 2013 2:05 PM by bostonmacosx RSS

    Consistent Date based Xaxis when data is missing a few months.

    bostonmacosx

      So let's say I have

       

      Date

      DATE Value
      10/1/20134
      12/1/20135
      2/1/20146

       

      When would plot this out normally I would be missing November and January on the plot. I want to have a consistent date on the Xaxis.

       

      I'm thinking I would have to use a connect by level to get these values but I'm not sure how to apply this after reading a few turoirals and looking on these boards.

       

      Any help appreciated.

        • 1. Re: Consistent Date based Xaxis when data is missing a few months.
          Mike Kutz

          DATABASE VERSION PLEASE !!!

          SQL statements that can be used will be very dependent on which database version you are on.

          Re: 2. How do I ask a question on the forums?

           

          Step One:  define, mathematically, how to fill in the missing Y values.

           

          MK

           

          with month_generator as (
            select add_months(
                     trunc(
                        to_date( '11-12-13', 'MM-DD-RR' ) -- replace this with the first date
                        ,'MONTH' -- 'truncating' by month
                     )
                     ,level - 1 -- adding a month (0-based)
                     ) AS a_month
            from dual
            connect by level <= 6 -- replace '5' with a calculation that determines how many months
          )
          select .... -- place your regular SQL here. use NVL( value, <formula from above> ) for value
          from month_generator X
            left outer join T on ( t.a_date = X.a_date );
          

           

           

           

          PS - This is really an SQL question.  you'd be better off asking it in the "SQL and PL/SQL" forum.

          You can ask a moderator to move this thread by "report abuse" and typing in your request.

          • 2. Re: Consistent Date based Xaxis when data is missing a few months.
            bostonmacosx

            I always put the database version in the TAGS of the post. It is available at the bottom line of the post.

            Thanks for the suggestion I will run this in to my code and see if I can get an acceptable answer.

             

            Rob