8 Replies Latest reply: May 21, 2013 9:09 AM by Another_user RSS

    SQL QUERY NEEDED

    175645
      Hi

      We have some requirement to calculate some value depending on days . I need the query to show the output as below

      Fixed 62     and 0.39

           
      Day 1 Calculation 62 - 0.39 = 61.61
      Day 2 Calculation 61.61 - 0.39 = 61.22 here 61.61 is the last calculated value of day 1
      Day 3 Calculation 61.22 - 0.39 = 60.82 here 61.22 is the last calculated value of day 2
      Day 4 Calculation 60.82 - 0.39 = 60.42 here 60.82 is the last calculated value of day 3


      ...... till day 155

      Regards
        • 1. Re: SQL QUERY NEEDED
          sb92075
          mohdmunawar wrote:
          Hi

          We have some requirement to calculate some value depending on days . I need the query to show the output as below

          Fixed 62     and 0.39

               
          Day 1 Calculation 62 - 0.39 = 61.61
          Day 2 Calculation 61.61 - 0.39 = 61.22 here 61.61 is the last calculated value of day 1
          Day 3 Calculation 61.22 - 0.39 = 60.82 here 61.22 is the last calculated value of day 2
          Day 4 Calculation 60.82 - 0.39 = 60.42 here 60.82 is the last calculated value of day 3


          ...... till day 155

          Regards
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ


          Handle:     mohdmunawar
          Status Level:     Newbie
          Registered:     Dec 24, 2001
          Total Posts:     82
          Total Questions:     12 (12 unresolved)


          why do you waste your time & our time when you NEVER get your questions answered here?
          • 2. Re: SQL QUERY NEEDED
            Another_user
            Since this looks like homework, what have you tried?
            • 3. Re: SQL QUERY NEEDED
              Hoek
              Look into LAG/LEAD analytical functions:
              http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php
              • 4. Re: SQL QUERY NEEDED
                skr
                Hi.
                SELECT
                     LEVEL day,
                     62 - (0.39 * LEVEL) value
                FROM dual CONNECT BY LEVEL <= 155;
                
                DAY                    VALUE                  
                ---------------------- ---------------------- 
                1                      61.61                  
                2                      61.22                  
                3                      60.83                  
                4                      60.44                  
                5                      60.05                  
                6                      59.66                  
                7                      59.27                  
                8                      58.88                  
                9                      58.49                  
                10                     58.1 
                ...
                150                    3.5                    
                151                    3.11                   
                152                    2.72                   
                153                    2.33                   
                154                    1.94                   
                155                    1.55 
                Hope this helps.

                Regards.
                • 5. Re: SQL QUERY NEEDED
                  175645
                  Hi sKr

                  Thanks for Query. It helped me a lot.

                  Regards
                  • 6. Re: SQL QUERY NEEDED
                    ranit B
                    Exactly what skr already posted:
                    with xx as
                    (
                        select 62 c1, 0.39 c2 from dual
                    )
                    select 
                        'day-'||level
                        , c1-(level-1)*c2 
                        , c1-level*c2 
                    from xx
                    connect by level<=155;
                    Output:
                    day-1     62     61.61
                    day-2     61.61     61.22
                    day-3     61.22     60.83
                    day-4     60.83     60.44
                    day-5     60.44     60.05
                    .
                    .
                    .
                    day-152     3.11     2.72
                    day-153     2.72     2.33
                    day-154     2.33     1.94
                    day-155     1.94     1.55
                    • 7. Re: SQL QUERY NEEDED
                      skr
                      Hi.

                      Please, if your question was resolved mark it as answered, as well as your previous questions.

                      Regards.
                      • 8. Re: SQL QUERY NEEDED
                        Another_user
                        Another option with rownum...
                        SELECT 'DAY ' || ROWNUM || ' calculation ' || TO_CHAR (62 - (0.39 * (ROWNUM - 1))) || ' - .39 =  ' || TO_CHAR (62 - (0.39 * ROWNUM))
                              FROM DUAL
                        CONNECT BY LEVEL <= 155;
                        
                        DAY 1 calculation 62 - .39 =  61.61
                        DAY 2 calculation 61.61 - .39 =  61.22
                        DAY 3 calculation 61.22 - .39 =  60.83
                        .
                        .
                        .
                        DAY 155 calculation 1.94 - .39 =  1.55