8 Replies Latest reply on May 21, 2013 2:09 PM by Lenny_

# SQL QUERY NEEDED

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
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
Since this looks like homework, what have you tried?
• ###### 4. Re: SQL QUERY NEEDED
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
Hi sKr

Thanks for Query. It helped me a lot.

Regards
• ###### 6. Re: SQL QUERY NEEDED
``````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
Hi.

Regards.
• ###### 8. Re: SQL QUERY NEEDED
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``````