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

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

bostonmacosx Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

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