Home » SQL & PL/SQL » SQL & PL/SQL » Sql Loop (Sql navigator)
Sql Loop [message #607058] |
Sun, 02 February 2014 01:57 |
|
majlinda.kastrati
Messages: 2 Registered: February 2014 Location: Watford
|
Junior Member |
|
|
Dear all,
Could anyone help me with the issue below.
I have a table table_1, which has an ID_No,Amount,date and there is only one row for this ID in table_1.
I want to make a loop for a column amount, based on column No, to add such number of rows.
Example: there is a payment plan for 12 months. The amount is 1200, and based on No=12, in the second row, amount will be subtracted by 100 and continuously, until it will be zero. Also Column date will be month=(month-1).
ID_No No Date Amount
999 12 1/1/2014 1200
999 11 2/1/2014 1100
999 10 3/1/2014 1000
999 9 4/1/2014 900
999 8 5/1/2014 800
999 7 6/1/2014 700
999 6 7/1/2014 600
999 5 8/1/2014 500
999 4 9/1/2014 400
999 3 10/1/2014 300
999 2 11/1/2014 200
999 1 12/1/2014 100
Thank you!
|
|
|
|
Re: Sql Loop [message #607064 is a reply to message #607058] |
Sun, 02 February 2014 03:58 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Assuming you have only one row per ID in your table and you want to generate No rows decreasing amount by 100 (computed by amount/No, i.e. 1200/12) and increasing Date by 1 month then:
SQL> with
2 data as (
3 select 999 id, 12 no, to_date('1/1/2014','MM/DD/YYYY') dt, 1200 amount
4 from dual
5 )
6 select id,
7 no-row_number() over (partition by id order by column_value)+1 no,
8 add_months(dt, column_value-1) dt,
9 amount-(amount/no*(column_value-1)) amount
10 from data,
11 table(cast(multiset(select level from dual connect by level <= no+1)
12 as sys.odciNumberList))
13 /
ID NO DT AMOUNT
---------- ---------- ----------- ----------
999 12 01-JAN-2014 1200
999 11 01-FEB-2014 1100
999 10 01-MAR-2014 1000
999 9 01-APR-2014 900
999 8 01-MAY-2014 800
999 7 01-JUN-2014 700
999 6 01-JUL-2014 600
999 5 01-AUG-2014 500
999 4 01-SEP-2014 400
999 3 01-OCT-2014 300
999 2 01-NOV-2014 200
999 1 01-DEC-2014 100
999 0 01-JAN-2015 0
(I added the line 0 from your output to satisfy the condition "until it will be zero".)
[Updated on: Sun, 02 February 2014 04:00] Report message to a moderator
|
|
|
Re: Sql Loop [message #607338 is a reply to message #607064] |
Wed, 05 February 2014 00:56 |
|
bluefred
Messages: 16 Registered: June 2012 Location: Qatar
|
Junior Member |
|
|
DT should decrease by 1 month at a time, so in Michel's answer change add_months(dt, column_value-1) to add_months(dt, -column_value+1)
or you can use Oracle Model Clause like the following:
SELECT ID,NO,DT,AMOUNT
FROM (
SELECT 999 ID, 12 NO, TO_DATE('1/1/2014','MM-DD-YYYY') DT, 1200 AMOUNT
FROM DUAL
UNION
SELECT 888 ID, 12 NO, TO_DATE('1/1/2014','MM-DD-YYYY') DT, 1200 AMOUNT
FROM DUAL
)
MODEL
PARTITION BY (ID)
DIMENSION BY (NO)
MEASURES (DT,AMOUNT)
RULES (
DT[FOR NO FROM 11 TO 1 DECREMENT 1]=ADD_MONTHS(DT[CV()+1],-1),
AMOUNT[FOR NO FROM 11 TO 1 DECREMENT 1]=AMOUNT[CV()+1]-100
);
[Updated on: Wed, 05 February 2014 00:57] Report message to a moderator
|
|
|
|
Re: Sql Loop [message #607343 is a reply to message #607341] |
Wed, 05 February 2014 01:18 |
|
bluefred
Messages: 16 Registered: June 2012 Location: Qatar
|
Junior Member |
|
|
Quote:
It is not what OP's output shows and his explanations are not clear so my "assuming" prelude.
right that's what the output says, but in the requirement it says:
Quote:
Also Column date will be month=(month-1).
I assumed 12 to be a constant since we only have 12 months a year 'ceteris paribus'
Quote:
Also note that "12" is not a constant but a variable given by "No" column (quote: "based on No=12").
Otherwise, Model Clause with ITIRATE rule should be the right answer.
[Updated on: Wed, 05 February 2014 02:22] Report message to a moderator
|
|
|
Re: Sql Loop [message #607372 is a reply to message #607343] |
Wed, 05 February 2014 03:29 |
vesile_taskiran
Messages: 66 Registered: August 2008 Location: Turkey
|
Member |
|
|
is this a solution ?
select add_months(trunc(sysdate,'year'),level-1) month, 1200-100*(level-1) amount
from dual
connect by level<=12
MONTH AMOUNT
01.01.2014 1200
01.02.2014 1100
01.03.2014 1000
01.04.2014 900
01.05.2014 800
01.06.2014 700
01.07.2014 600
01.08.2014 500
01.09.2014 400
01.10.2014 300
01.11.2014 200
01.12.2014 100
|
|
|
Re: Sql Loop [message #607374 is a reply to message #607372] |
Wed, 05 February 2014 03:39 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The values are not fixed but taken from a table and there can be several rows (ID) in this table.
If you take all these into account and use the same way than you query you will end with mine (or the better one below) or the like:
SQL> break on id skip 1 dup
SQL> with
2 data as (
3 select 999 id, 12 no, to_date('1/1/2014','MM/DD/YYYY') dt, 1200 amount from dual
4 union all
5 select 111 id, 5 no, to_date('3/1/2014','MM/DD/YYYY') dt, 200 amount from dual
6 )
7 select id,
8 no-column_value+1 no,
9 add_months(dt, column_value-1) dt,
10 amount-(amount/no*(column_value-1)) amount
11 from data,
12 table(cast(multiset(select level from dual connect by level <= no+1)
13 as sys.odciNumberList))
14 /
ID NO DT AMOUNT
---------- ---------- ----------- ----------
999 12 01-JAN-2014 1200
999 11 01-FEB-2014 1100
999 10 01-MAR-2014 1000
999 9 01-APR-2014 900
999 8 01-MAY-2014 800
999 7 01-JUN-2014 700
999 6 01-JUL-2014 600
999 5 01-AUG-2014 500
999 4 01-SEP-2014 400
999 3 01-OCT-2014 300
999 2 01-NOV-2014 200
999 1 01-DEC-2014 100
999 0 01-JAN-2015 0
111 5 01-MAR-2014 200
111 4 01-APR-2014 160
111 3 01-MAY-2014 120
111 2 01-JUN-2014 80
111 1 01-JUL-2014 40
111 0 01-AUG-2014 0
19 rows selected.
|
|
|
Re: Sql Loop [message #607377 is a reply to message #607374] |
Wed, 05 February 2014 05:30 |
vesile_taskiran
Messages: 66 Registered: August 2008 Location: Turkey
|
Member |
|
|
You right . Ended with the same method. Only no is dynamic.
select a.id,
add_months(xmonth,column_value) month,
amount-(amount/(months_between(trunc(sysdate,'year')+365,xmonth))*column_value) amount
from
(
select
100*level id,
1200-level*100 amount,
add_months(trunc(sysdate,'year'),level) xmonth,
months_between(trunc(sysdate,'year')+365,add_months(trunc(sysdate,'year'),level) ) n_months
from dual
connect by level<=10
) a,table(cast(multiset(select level from dual connect by level <= n_months)
as sys.odciNumberList))
order by id, month
ID MONTH AMOUNT
100 01.03.2014 1000
100 01.04.2014 900
100 01.05.2014 800
100 01.06.2014 700
100 01.07.2014 600
100 01.08.2014 500
100 01.09.2014 400
100 01.10.2014 300
100 01.11.2014 200
100 01.12.2014 100
100 01.01.2015 0
200 01.04.2014 900
200 01.05.2014 800
200 01.06.2014 700
200 01.07.2014 600
200 01.08.2014 500
200 01.09.2014 400
200 01.10.2014 300
200 01.11.2014 200
200 01.12.2014 100
200 01.01.2015 0
300 01.05.2014 800
300 01.06.2014 700
300 01.07.2014 600
300 01.08.2014 500
300 01.09.2014 400
300 01.10.2014 300
300 01.11.2014 200
300 01.12.2014 100
300 01.01.2015 0
..........................
|
|
|
|
Re: Sql Loop [message #607410 is a reply to message #607409] |
Wed, 05 February 2014 14:00 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
There is just a tiny difference with the previous problem.
As you can see the solutions do not rely on constant just on the values that are in the table columns.
Read again what have been posted. Try to understand the queries. Clarify the requirements.
[Updated on: Wed, 05 February 2014 14:01] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Thu Mar 28 08:50:04 CDT 2024
|