5 Replies Latest reply on Apr 24, 2013 2:30 PM by 1005051

    Finding number within specific range

    1005051
      Hi,

      I have a requirement where I need to check the lower and higher
      "range" for specific number. The range is in increment of 0.05

      Example:


      Range:
      0.05
      0.1
      0.15
      0.2
      0.25
      0.3
      0.35


      So if I am provided with number say 0.29, I have to return below
      outout


      Number Low_range High_range
      0.29 0.2 0.3
      0.13 0.1 0.15


      How can I do this via oracle sql? I tried couple of options but
      nothing seems to work.


      Any help would be appreciated

      Edited by: 1002048 on Apr 23, 2013 3:00 PM
        • 1. Re: Finding number within specific range
          sb92075
          1002048 wrote:
          Hi,

          I have a requirement where I need to check the lower and higher
          "range" for specific number. The range is in increment of 0.05

          Example:


          Range:
          0.05
          0.1
          0.15
          0.2
          0.25
          0.3
          0.35


          So if I am provided with number say 0.29, I have to return below
          outout

          Number Low_range High_range
          0.29 0.2 0.3
          why is LOW_RANGE=0.2? Should it be 0.25, instead?
          • 2. Re: Finding number within specific range
            rp0428
            >
            I have a requirement where I need to check the lower and higher
            "range" for specific number. The range is in increment of 0.05

            Example:

            Range:
            0.05
            0.1
            0.15
            0.2
            0.25
            0.3
            0.35


            So if I am provided with number say 0.29, I have to return below
            outout


            Number Low_range High_range
            0.29 0.2 0.3
            0.13 0.1 0.15
            >
            This is one way
            with q as (select .29 myNum from dual
              union all select .13 from dual)
            select myNum, (trunc((myNum * 2) * 10)/2)/10 minRange,
                          (round((myNum * 2) * 10)/2)/10 maxRange from q
            
            MYNUM     MINRANGE     MAXRANGE
            0.29     0.25     0.3
            0.13     0.1     0.15
            • 3. Re: Finding number within specific range
              Frank Kulash
              Hi,

              Welcome to the forum!

              Oracle has two very handy built-in functions, FLOOR and CEIL, that return the closest integer no greater than, or no less than, a given number. Is there some way we can leverage these functions? Yes! They deal with ranges of 1.00, and you have ranges of .05, or 1/20 that size. So magnify your numbers by a factor of 20, use FLOOR and CEIL, and then shrink the results back by a factor of 20, like this:
              SELECT  n
              ,     FLOOR (n * 20) / 20    AS low_range
              ,     CEIL  (n * 20) / 20    AS high_range
              FROM     table_x
              ;
              To test this, I created a test table:
              CREATE TABLE     table_x
              (
                   n     NUMBER
              );
              
              INSERT INTO table_x (n) VALUES (.29);
              INSERT INTO table_x (n) VALUES (.13);
              COMMIT;
              You should post sample data like this whenever you have a question.

              I got these results from that data:
              `        N  LOW_RANGE HIGH_RANGE
              ---------- ---------- ----------
                     .29        .25         .3
                     .13         .1        .15
               

              By the way, it's better to post questions like this that are about the SQL language in the SQL and PL/SQL
              See the FAQ for that forum {message:id=9360002}
              • 4. Re: Finding number within specific range
                1005051
                PLEASE IGNORE BELOW. You have already answered this.

                Thanks Frank. This seems perfect. Quick question, how did we determine that we need to use "20" for this problem.

                (n * 20) / 20

                Edited by: 1002048 on Apr 24, 2013 7:26 AM
                • 5. Re: Finding number within specific range
                  1005051
                  Thats correct. Sorry, will be careful while posting in future.