Home » SQL & PL/SQL » SQL & PL/SQL » transpose rows into columns (oracle)
| transpose rows into columns [message #684936] |
Thu, 30 September 2021 10:26  |
 |
suji6281
Messages: 130
Registered: September 2014
|
Senior Member |
|
|
Hi Team,
I am trying to get the rows into columns but couldn't get the proper results.
Below is the sample data created expalining the issue.
CREATE TABLE PYMNT_HEADER_TBL
(PAYMENT_ID INT NOT NULL, DESCR VARCHAR2(50), START_DATE DATE NOT NULL, CLOSE_DATE DATE NOT NULL)
INSERT INTO PYMNT_HEADER_TBL
VALUES (56134, 'payment details', to_date('01/01/2019', 'MM/DD/YYYY'), to_date('12/31/2025', 'MM/DD/YYYY'))
CREATE TABLE PAYMENT_TBL
(PAYMENT_ID INT NOT NULL, START_DATE DATE NOT NULL, END_DATE DATE NOT NULL, AMOUNT INT NOT NULL)
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2019', 'MM/DD/YYYY'),
TO_DATE('12/31/2019', 'MM/DD/YYYY'), 100);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2020', 'MM/DD/YYYY'),
TO_DATE('12/31/2020', 'MM/DD/YYYY'), 200);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2021', 'MM/DD/YYYY'),
TO_DATE('12/31/2021', 'MM/DD/YYYY'), 300);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2022', 'MM/DD/YYYY'),
TO_DATE('12/31/2022', 'MM/DD/YYYY'), 400);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (56134, to_date('01/01/2023', 'MM/DD/YYYY'),
TO_DATE('12/31/2023', 'MM/DD/YYYY'), 500);
SELECT A.PAYMENT_ID, A.START_DATE, A.CLOSE_DATE,
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT
WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR1",
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT
WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR2",
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT
WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR3",
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT
WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR4",
CASE WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) = EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
EXTRACT (MONTH FROM TO_DATE(A.CLOSE_DATE)) - EXTRACT (MONTH FROM TO_DATE(B.START_DATE)) * AMOUNT
WHEN EXTRACT (YEAR FROM TO_DATE(B.START_DATE)) < EXTRACT (YEAR FROM TO_DATE(A.CLOSE_DATE)) THEN
(13- EXTRACT (MONTH FROM TO_DATE(B.START_DATE))) * AMOUNT END AS "YEAR5"
FROM PYMNT_HEADER_TBL A, PAYMENT_TBL B
WHERE A.PAYMENT_ID = 56134
AND A.PAYMENT_ID = B.PAYMENT_ID
PAYMENT_ID START_DATE CLOSE_DATE YEAR1 YEAR2 YEAR3 YEAR4 YEAR5
56134 01-JAN-19 31-DEC-25 1200 1200 1200 1200 1200
56134 01-JAN-19 31-DEC-25 2400 2400 2400 2400 2400
56134 01-JAN-19 31-DEC-25 3600 3600 3600 3600 3600
56134 01-JAN-19 31-DEC-25 4800 4800 4800 4800 4800
56134 01-JAN-19 31-DEC-25 6000 6000 6000 6000 6000
PAYMENT_ID START_DATE CLOSE_DATE YERA1 YEAR2 YEAR3 YEAR4 YEAR5
56134 01/01/2019 12/31/2025 1200 2400 3600 4800 6000
Appreciate you response on this. Thank you.
Regards
Suji
|
|
|
|
|
|
|
|
|
|
|
|
| Re: transpose rows into columns [message #684943 is a reply to message #684942] |
Fri, 01 October 2021 01:52  |
 |
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:Can a row in PAYMENT_TBL cover several years? -- there won't be server years.. max 5 or 6 years
This does not answer my question.
in addition, "there won't be [server]several years.. max 5 or 6 years" is meaningless, aren't 5 or 6 years several years?
Quote:there will be only one row for year.
Is this at least and at most one?
Can there be some months or years without payment?
In short, can there be some cases in PAYMENT_TBL like these, for a PYMNT_HEADER_TBL (start,end) as (01/01/2000,01/31/2003):
01/01/2000->03/31/2000
<no payment in months 4 and 5)
06/01/2000->06/15/2000
06/16/2000->06/30/2000
07/01/2000->08/20/2000
<no payment between 08/21 and 08/31
09/01/2000->09/30/2000
<no payment in months 10 to 12)
<no payment in 2001>
<no payment in 2002 until 12/01>
12/01/2002->01/31/2003
Post one answer per line.
Post the added INSERT statements I asked.
[Updated on: Fri, 01 October 2021 01:55] Report message to a moderator |
|
|
|
| Re: transpose rows into columns [message #684946 is a reply to message #684943] |
Fri, 01 October 2021 04:08 |
|
suji6281
Messages: 130
Registered: September 2014
|
Senior Member |
|
|
Hi Michel,
There could be the scenario like start_date and end_date in PAYMENT_TBL as '06/01/2000' and '10/31/2022'.
In this case Year1 would be (7 months * amount), year2 would be (12 months * amount), year3 would be (10 months * amount) and year4, year5 would be blank.
There won't be such scenario like missing months in between.
INSERT INTO PYMNT_HEADER_TBL
VALUES (75200, 'payment details_2', to_date('01/01/2020', 'MM/DD/YYYY'), to_date('10/31/2022', 'MM/DD/YYYY'));
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT) VALUES (75200, to_date('06/01/2020', 'MM/DD/YYYY'),
TO_DATE('10/31/2022', 'MM/DD/YYYY'), 700);
Regards
Suji
|
|
|
|
|
|
|
|
| Re: transpose rows into columns [message #684950 is a reply to message #684949] |
Fri, 01 October 2021 07:52 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Something like this, assuming there will be no more than 10 year period:
WITH T1 AS (
SELECT YEAR_NUMBER,
PAYMENT_ID,
GREATEST(START_DATE,ADD_MONTHS(TRUNC(START_DATE,'YYYY'),12 * (YEAR_NUMBER - 1))) START_DATE,
LEAST(CLOSE_DATE,ADD_MONTHS(TRUNC(START_DATE,'YYYY'),12 * YEAR_NUMBER) - 1) CLOSE_DATE
FROM PYMNT_HEADER_TBL,
LATERAL(
SELECT LEVEL YEAR_NUMBER
FROM DUAL
CONNECT BY ADD_MONTHS(TRUNC(START_DATE,'YYYY'),12 * (LEVEL - 1)) <= CLOSE_DATE
)
),
T2 AS (
SELECT T1.YEAR_NUMBER,
T1.PAYMENT_ID,
GREATEST(P.START_DATE,T1.START_DATE) START_DATE,
LEAST(P.END_DATE,T1.CLOSE_DATE) END_DATE,
AMOUNT
FROM T1,
PAYMENT_TBL P
WHERE P.START_DATE(+) <= T1.CLOSE_DATE
AND P.END_DATE(+) >= T1.START_DATE
)
SELECT *
FROM T2
PIVOT(
SUM(AMOUNT * MONTHS_BETWEEN(END_DATE + 1,START_DATE))
FOR YEAR_NUMBER IN (1 YEAR1,2 YEAR2,3 YEAR3,4 YEAR4,5 YEAR5,6 YEAR6,7 YEAR7,8 YEAR8,9 YEAR9,10 YEAR10)
)
/
PAYMENT_ID YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 YEAR6 YEAR7 YEAR8 YEAR9 YEAR10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
56134 1200 2400 3600 4800 6000
SQL>
[Updated on: Fri, 01 October 2021 07:53] Report message to a moderator |
|
|
|
| Re: transpose rows into columns [message #684951 is a reply to message #684949] |
Fri, 01 October 2021 07:54 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
1)
This is not exactly and a complete answer to what I asked.
Is it, and this is the only case, exactly one line in PAYMENT_TBL per year between start and end dates in PYMNT_HEADER_TBL for each PAYMENT_ID?
The answer is a simple "yes" or "no".
2)
If, in your latest example, "start_date and end_date in PAYMENT_TBL as '06/01/2000' and '10/31/20202'" then is there, between these 2 dates, only and exactly in PAYMENT_TBL, one row for 2000, one row for 2001 and one row for 2002?
Once again, the answer I expect it "yes" or "no".
If the answer is "yes" for both questions (which are in fact the same one), I have 2 more questions (still with your latest example):
3) Can start_date in PAYMENT_TBL for year 2000 be after start_date in PYMNT_HEADER_TBL?
4) Can end_date in PAYMENT_TBL for year 2002 be before close_date in PYMNT_HEADER_TBL?
Expected answers:
1) yes/no
2) yes/no
3) yes/no/na (not applicable if answer in previous points is "no")
4) yes/no/na (not applicable if answer in previous points is "no")
|
|
|
|
|
|
|
|
| Re: transpose rows into columns [message #684955 is a reply to message #684953] |
Fri, 01 October 2021 11:04 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
suji6281 wrote on Fri, 01 October 2021 16:52Hi Michel,
Below is the my response on your queries:
1) Yes
2) No
3) Yes
hope you are clear on this.
Thank you.
Regards
Suji
What about 4)??? 
Note that Solomon's query may not work with your Oracle version, this is why we ask:
Michel Cadot wrote on Tue, 16 October 2018 10:35
...
- give your Oracle version
...
cookiemonster wrote on Fri, 01 March 2019 11:43...
3) What oracle version are you using?
cookiemonster wrote on Fri, 01 March 2019 12:29...
What is the exact oracle version? run select * from v$version if you're not sure.
[Updated on: Sat, 02 October 2021 03:04] Report message to a moderator |
|
|
|
| Re: transpose rows into columns [message #684956 is a reply to message #684955] |
Fri, 01 October 2021 11:24 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Another query is (assuming there is only 1 row per payment_id in PYMNT_HEADER_TBL, and assuming that dates in tables start at day 1 and end at last day of the month):
SQL> with
2 data as (
3 select p.payment_id, p.amount,
4 greatest(p.start_date,h.start_date) start_date,
5 least(p.end_date, h.close_date) end_date,
6 trunc(months_between(p.start_date,h.start_date)/12)+1 year
7 from PAYMENT_TBL p, PYMNT_HEADER_TBL h
8 where p.payment_id = h.payment_id
9 -- conditions to be sure used data in PAYMENT_TBL are correct:
10 and p.start_date >= h.start_date
11 and p.end_date <= h.close_date
12 )
13 select *
14 from data
15 pivot (
16 sum(amount*months_between(end_date+1,start_date))
17 for year in (1 "YEAR1", 2 "YEAR2", 3 "YEAR3", 4 "YEAR4", 5 "YEAR5")
18 )
19 order by 1
20 /
PAYMENT_ID YEAR1 YEAR2 YEAR3 YEAR4 YEAR5
---------- ---------- ---------- ---------- ---------- ----------
56134 1200 2400 3600 4800 6000[Updated on: Fri, 01 October 2021 11:30] Report message to a moderator |
|
|
|
| Re: transpose rows into columns [message #684957 is a reply to message #684956] |
Fri, 01 October 2021 11:34 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Removing the second assumption:
SQL> with
2 data as (
3 select p.payment_id, p.amount,
4 trunc(greatest(p.start_date,h.start_date),'MONTH') start_date,
5 last_day(least(p.end_date, h.close_date)) end_date,
6 trunc(months_between(trunc(p.start_date,'MONTH'),
7 trunc(h.start_date,'MONTH'))
8 /12) + 1 year
9 from PAYMENT_TBL p, PYMNT_HEADER_TBL h
10 where p.payment_id = h.payment_id
11 -- conditions to be sure used data in PAYMENT_TBL are correct:
12 and p.start_date >= h.start_date
13 and p.end_date <= h.close_date
14 )
15 select *
16 from data
17 pivot (
18 sum(amount*months_between(end_date+1,start_date))
19 for year in (1 "YEAR1", 2 "YEAR2", 3 "YEAR3", 4 "YEAR4", 5 "YEAR5")
20 )
21 order by 1
22 /
PAYMENT_ID YEAR1 YEAR2 YEAR3 YEAR4 YEAR5
---------- ---------- ---------- ---------- ---------- ----------
56134 1200 2400 3600 4800 6000 |
|
|
|
| Re: transpose rows into columns [message #684958 is a reply to message #684954] |
Fri, 01 October 2021 11:40 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
suji6281 wrote on Fri, 01 October 2021 10:53I am trying to understand the sql query that you provided here.
T1 - split each PYMNT_HEADER_TBL PAYMENT_ID by year and assign consecutive numbers to each year within PAYMENT_ID (starting with 1).
T2 - outer join T1 to PAYMENT_TBL by PAYMENT_ID and year and pivot by year number (up to 10 consecutive years). IMPORTANT: my solution assumes PAYMENT_TBL START_DATE - END_DATE intervals don't span across multiple years (otherwise we would have to split PAYMENT_TBL by year also same way T1 does).
SY.
|
|
|
|
|
|
|
|
| Re: transpose rows into columns [message #685047 is a reply to message #685000] |
Wed, 13 October 2021 14:11 |
|
suji6281
Messages: 130
Registered: September 2014
|
Senior Member |
|
|
Hi Michel/Solomon,
The requirement was changed little bit.
If there was 6 years payment that was started in Aug 2021 then we would expect to see 5 months in 2021, 12 months in 2022,
12 months in 2023, 12 months in 2024, 12 months in 2025 and remaining 7 months in hereafter column and sum of all year amounts.
Consider least date of PAYMENT_TBL would be START_DATE and greatest date of PAYMENT_TBL would be END_DATE.
CREATE TABLE PAYMENT_TBL
(PAYMENT_ID INT NOT NULL, START_DATE DATE NOT NULL, END_DATE DATE NOT NULL, AMOUNT INT NOT NULL, NUMBER_OF_PAYMENTS INT NOT NULL)
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT, NUMBER_OF_PAYMENTS) VALUES (56100, to_date('08/01/2021', 'MM/DD/YYYY'),
TO_DATE('08/31/2021', 'MM/DD/YYYY'), 100, 1);
INSERT INTO PAYMENT_TBL (PAYMENT_ID, START_DATE, END_DATE, AMOUNT, NUMBER_OF_PAYMENTS) VALUES (56100, to_date('09/01/2021', 'MM/DD/YYYY'),
TO_DATE('07/31/2026', 'MM/DD/YYYY'), 100, 59);
CREATE TABLE PAYMENT_SCHEDULE_TBL
(PAYMENT_ID INT NOT NULL, PERIOD_NAME VARCHAR2(50), AMOUNT INT NOT NULL)
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2021', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2021', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2021', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2021', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2021', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2022', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2023', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2024', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'AUG-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'SEP-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'OCT-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'NOV-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'DEC-2025', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JAN-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'FEB-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAR-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'APR-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'MAY-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUN-2026', 100);
INSERT INTO PAYMENT_SCHEDULE_TBL VALUES (56100, 'JUL-2026', 100);
PAYMENT_ID START_DATE END_DATE YERA1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL
56100 08/01/2021 07/31/2026 500 1200 1200 1200 1200 700 6000
|
|
|
|
|
|
|
|
|
|
|
|
| Re: transpose rows into columns [message #685060 is a reply to message #685059] |
Fri, 15 October 2021 02:04 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
OK, in this case here's one possibility using only payment_schedule_tbl:
SQL> with
2 data as (
3 select payment_id,
4 extract(year
5 from to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')) yr,
6 min(extract(year
7 from to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')))
8 over (partition by payment_id) minyr,
9 amount
10 from payment_schedule_tbl
11 )
12 select payment_id,
13 sum(case when yr = minyr+0 then amount end) year1,
14 sum(case when yr = minyr+1 then amount end) year2,
15 sum(case when yr = minyr+2 then amount end) year3,
16 sum(case when yr = minyr+3 then amount end) year4,
17 sum(case when yr = minyr+4 then amount end) year5,
18 sum(case when yr > minyr+4 then amount end) thereafter,
19 sum(amount) total
20 from data
21 group by payment_id, minyr
22 order by payment_id
23 /
PAYMENT_ID YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
56100 500 1200 1200 1200 1200 700 6000 |
|
|
|
| Re: transpose rows into columns [message #685061 is a reply to message #685060] |
Fri, 15 October 2021 02:11 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Or, if you are sure about your date values (so without verifying it with to_date):
SQL> with
2 data as (
3 select payment_id,
4 to_number(substr(period_name,5)) yr,
5 to_number(min(substr(period_name,5)) over (partition by payment_id)) minyr,
6 amount
7 from payment_schedule_tbl
8 )
9 select payment_id,
10 sum(case when yr = minyr+0 then amount end) year1,
11 sum(case when yr = minyr+1 then amount end) year2,
12 sum(case when yr = minyr+2 then amount end) year3,
13 sum(case when yr = minyr+3 then amount end) year4,
14 sum(case when yr = minyr+4 then amount end) year5,
15 sum(case when yr > minyr+4 then amount end) thereafter,
16 sum(amount) total
17 from data
18 group by payment_id, minyr
19 order by payment_id
20 /
PAYMENT_ID YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
56100 500 1200 1200 1200 1200 700 6000 |
|
|
|
| Re: transpose rows into columns [message #685062 is a reply to message #685061] |
Fri, 15 October 2021 02:20 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
And using only payment_tbl:
SQL> with
2 data as (
3 select payment_id, amount,
4 extract(year from add_months(start_date,column_value-1)) yr,
5 min(extract(year from add_months(start_date,column_value-1)))
6 over (partition by payment_id) minyr
7 from payment_tbl,
8 table(cast(multiset(select level from dual
9 connect by level <= number_of_payments)
10 as sys.odciNumberList))
11 )
12 select payment_id,
13 sum(case when yr = minyr+0 then amount end) year1,
14 sum(case when yr = minyr+1 then amount end) year2,
15 sum(case when yr = minyr+2 then amount end) year3,
16 sum(case when yr = minyr+3 then amount end) year4,
17 sum(case when yr = minyr+4 then amount end) year5,
18 sum(case when yr > minyr+4 then amount end) thereafter,
19 sum(amount) total
20 from data
21 group by payment_id, minyr
22 order by payment_id
23 /
PAYMENT_ID YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
56100 500 1200 1200 1200 1200 700 6000 |
|
|
|
| Re: transpose rows into columns [message #685063 is a reply to message #685062] |
Fri, 15 October 2021 04:12 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
There are many redundancies in your tables that should be always correct like "number_of_payments" and months between "start_date" and "end_date"+1) in "payment_tbl" or "number_of_payments" in "payment_tbl" and the actual number of rows in "payment_schedule_tbl"... They may be the origin of false results if some discrepancies exist.
You will say that they can't happen but Murphy' laws say that if this can happen then it will.
So, just for fun, I modified the query to verify the data at the same time it generates the report. In case of discrepancy it generates an error.
SQL> col "Discrepancy in payment_tbl" noprint
SQL> col "Inconsistency in tables" noprint
SQL> with
2 data as (
3 select payment_id, amount, number_of_payments,
4 months_between(end_date+1,start_date) number_of_months,
5 extract(year from add_months(start_date,column_value-1)) yr,
6 min(extract(year from add_months(start_date,column_value-1)))
7 over (partition by payment_id) minyr,
8 count(*) over
9 (partition by payment_id, extract(year from add_months(start_date,column_value-1)))
10 nb_payment
11 from payment_tbl,
12 table(cast(multiset(select level from dual
13 connect by level <= number_of_payments)
14 as sys.odciNumberList))
15 )
16 select d.payment_id,
17 sum(case when d.yr = d.minyr+0 then d.amount end) year1,
18 sum(case when d.yr = d.minyr+1 then d.amount end) year2,
19 sum(case when d.yr = d.minyr+2 then d.amount end) year3,
20 sum(case when d.yr = d.minyr+3 then d.amount end) year4,
21 sum(case when d.yr = d.minyr+4 then d.amount end) year5,
22 sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
23 sum(d.amount) total,
24 max(case
25 when d.payment_id is null or p.payment_id is null
26 or p.nb_payment != d.nb_payment
27 or p.amount != d.amount
28 then 1/0 end) "Inconsistency in tables",
29 max(case
30 when d.number_of_payments != d.number_of_months
31 then 1/0 end) "Discrepancy in payment_tbl"
32 from data d
33 full outer join
34 ( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount
35 from payment_schedule_tbl
36 group by payment_id, substr(period_name,5), amount ) p
37 on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
38 group by d.payment_id, d.minyr
39 order by d.payment_id
40 /
PAYMENT_ID YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
56100 500 1200 1200 1200 1200 700 6000
SQL> update payment_tbl set number_of_payments = 2 where number_of_payments = 1;
1 row updated.
SQL> with
2 data as (
3 select payment_id, amount, number_of_payments,
4 months_between(end_date+1,start_date) number_of_months,
5 extract(year from add_months(start_date,column_value-1)) yr,
6 min(extract(year from add_months(start_date,column_value-1)))
7 over (partition by payment_id) minyr,
8 count(*) over
9 (partition by payment_id, extract(year from add_months(start_date,column_value-1)))
10 nb_payment
11 from payment_tbl,
12 table(cast(multiset(select level from dual
13 connect by level <= number_of_payments)
14 as sys.odciNumberList))
15 )
16 select d.payment_id,
17 sum(case when d.yr = d.minyr+0 then d.amount end) year1,
18 sum(case when d.yr = d.minyr+1 then d.amount end) year2,
19 sum(case when d.yr = d.minyr+2 then d.amount end) year3,
20 sum(case when d.yr = d.minyr+3 then d.amount end) year4,
21 sum(case when d.yr = d.minyr+4 then d.amount end) year5,
22 sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
23 sum(d.amount) total,
24 max(case
25 when d.payment_id is null or p.payment_id is null
26 or p.nb_payment != d.nb_payment
27 or p.amount != d.amount
28 then 1/0 end) "Inconsistency in tables",
29 max(case
30 when d.number_of_payments != d.number_of_months
31 then 1/0 end) "Discrepancy in payment_tbl"
32 from data d
33 full outer join
34 ( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount
35 from payment_schedule_tbl
36 group by payment_id, substr(period_name,5), amount ) p
37 on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
38 group by d.payment_id, d.minyr
39 order by d.payment_id
then 1/0 end) "Discrepancy in payment_tbl"
*
ERROR at line 31:
ORA-01476: divisor is equal to zero
Now deleting a row in "payment_schedule_tbl":
SQL> rollback;
Rollback complete.
SQL> delete payment_schedule_tbl where period_name='JAN-2025';
1 row deleted.
SQL> with
2 data as (
3 select payment_id, amount, number_of_payments,
4 months_between(end_date+1,start_date) number_of_months,
5 extract(year from add_months(start_date,column_value-1)) yr,
6 min(extract(year from add_months(start_date,column_value-1)))
7 over (partition by payment_id) minyr,
8 count(*) over
9 (partition by payment_id, extract(year from add_months(start_date,column_value-1)))
10 nb_payment
11 from payment_tbl,
12 table(cast(multiset(select level from dual
13 connect by level <= number_of_payments)
14 as sys.odciNumberList))
15 )
16 select d.payment_id,
17 sum(case when d.yr = d.minyr+0 then d.amount end) year1,
18 sum(case when d.yr = d.minyr+1 then d.amount end) year2,
19 sum(case when d.yr = d.minyr+2 then d.amount end) year3,
20 sum(case when d.yr = d.minyr+3 then d.amount end) year4,
21 sum(case when d.yr = d.minyr+4 then d.amount end) year5,
22 sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
23 sum(d.amount) total,
24 max(case
25 when d.payment_id is null or p.payment_id is null
26 or p.nb_payment != d.nb_payment
27 or p.amount != d.amount
28 then 1/0 end) "Inconsistency in tables",
29 max(case
30 when d.number_of_payments != d.number_of_months
31 then 1/0 end) "Discrepancy in payment_tbl"
32 from data d
33 full outer join
34 ( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount
35 from payment_schedule_tbl
36 group by payment_id, substr(period_name,5), amount ) p
37 on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
38 group by d.payment_id, d.minyr
39 order by d.payment_id
40 /
then 1/0 end) "Inconsistency in tables",
*
ERROR at line 28:
ORA-01476: divisor is equal to zero
SQL> rollback;
Rollback complete.
SQL> update payment_schedule_tbl set amount = 200 where period_name='JAN-2025';
1 row updated.
SQL> with
2 data as (
3 select payment_id, amount, number_of_payments,
4 months_between(end_date+1,start_date) number_of_months,
5 extract(year from add_months(start_date,column_value-1)) yr,
6 min(extract(year from add_months(start_date,column_value-1)))
7 over (partition by payment_id) minyr,
8 count(*) over
9 (partition by payment_id, extract(year from add_months(start_date,column_value-1)))
10 nb_payment
11 from payment_tbl,
12 table(cast(multiset(select level from dual
13 connect by level <= number_of_payments)
14 as sys.odciNumberList))
15 )
16 select d.payment_id,
17 sum(case when d.yr = d.minyr+0 then d.amount end) year1,
18 sum(case when d.yr = d.minyr+1 then d.amount end) year2,
19 sum(case when d.yr = d.minyr+2 then d.amount end) year3,
20 sum(case when d.yr = d.minyr+3 then d.amount end) year4,
21 sum(case when d.yr = d.minyr+4 then d.amount end) year5,
22 sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
23 sum(d.amount) total,
24 max(case
25 when d.payment_id is null or p.payment_id is null
26 or p.nb_payment != d.nb_payment
27 or p.amount != d.amount
28 then 1/0 end) "Inconsistency in tables",
29 max(case
30 when d.number_of_payments != d.number_of_months
31 then 1/0 end) "Discrepancy in payment_tbl"
32 from data d
33 full outer join
34 ( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount
35 from payment_schedule_tbl
36 group by payment_id, substr(period_name,5), amount ) p
37 on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
38 group by d.payment_id, d.minyr
39 order by d.payment_id
40 /
then 1/0 end) "Inconsistency in tables",
*
ERROR at line 28:
ORA-01476: divisor is equal to zero
|
|
|
|
| Re: transpose rows into columns [message #685064 is a reply to message #685063] |
Fri, 15 October 2021 14:57 |
|
suji6281
Messages: 130
Registered: September 2014
|
Senior Member |
|
|
Thanks Michel for the solution and possible scenarios that will cause an error.
I will test the 1st Sql with different scenarios and let you know the test results.
And is there any possibility to get the start_date and end_date in the output along with the payment_id, Year1, Year2, Year3, Year4, Year5, Thereafter, Total columns.
Regards
Suji
[Updated on: Fri, 15 October 2021 14:59] Report message to a moderator |
|
|
|
| Re: transpose rows into columns [message #685065 is a reply to message #685064] |
Sat, 16 October 2021 11:23 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
With query on payment_schedule_tbl:
SQL> with
2 data as (
3 select payment_id, amount,
4 to_number(substr(period_name,5)) yr,
5 to_number(min(substr(period_name,5)) over (partition by payment_id)) minyr,
6 min(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
7 over (partition by payment_id) start_date,
8 add_months(max(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
9 over (partition by payment_id),1)-1 end_date
10 from payment_schedule_tbl
11 )
12 select payment_id, start_date, end_date,
13 sum(case when yr = minyr+0 then amount end) year1,
14 sum(case when yr = minyr+1 then amount end) year2,
15 sum(case when yr = minyr+2 then amount end) year3,
16 sum(case when yr = minyr+3 then amount end) year4,
17 sum(case when yr = minyr+4 then amount end) year5,
18 sum(case when yr > minyr+4 then amount end) thereafter,
19 sum(amount) total
20 from data
21 group by payment_id, minyr, start_date, end_date
22 order by payment_id
23 /
PAYMENT_ID START_DATE END_DATE YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL
---------- ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
56100 01-AUG-2021 31-JUL-2026 500 1200 1200 1200 1200 700 6000
With query on payment_tbl:
SQL> with
2 data as (
3 select payment_id, amount,
4 extract(year from add_months(start_date,column_value-1)) yr,
5 min(extract(year from add_months(start_date,column_value-1)))
6 over (partition by payment_id) minyr,
7 min(start_date) over (partition by payment_id) start_date,
8 max(end_date) over (partition by payment_id) end_date
9 from payment_tbl,
10 table(cast(multiset(select level from dual
11 connect by level <= number_of_payments)
12 as sys.odciNumberList))
13 )
14 select payment_id, start_date, end_date,
15 sum(case when yr = minyr+0 then amount end) year1,
16 sum(case when yr = minyr+1 then amount end) year2,
17 sum(case when yr = minyr+2 then amount end) year3,
18 sum(case when yr = minyr+3 then amount end) year4,
19 sum(case when yr = minyr+4 then amount end) year5,
20 sum(case when yr > minyr+4 then amount end) thereafter,
21 sum(amount) total
22 from data
23 group by payment_id, minyr, start_date, end_date
24 order by payment_id
25 /
PAYMENT_ID START_DATE END_DATE YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL
---------- ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
56100 01-AUG-2021 31-JUL-2026 500 1200 1200 1200 1200 700 6000
I updated my full query to cover the following discrepancies (I let you verify):
- in "payment_tbl", "number_of_payments" does match with months between start_date and end_date
- in "payment_schedule_tbl", some months contain more than one row
- numbers of months in the 2 tables don't match
- amounts are not the same in both tables
- start dates or end dates in the 2 table don't match
col "Discrepancy in payment_tbl" noprint
col "Inconsistency in tables" noprint
Col "Duplicated month" noprint
with
data as (
select payment_id, amount, number_of_payments,
months_between(end_date+1,start_date) number_of_months,
extract(year from add_months(start_date,column_value-1)) yr,
min(extract(year from add_months(start_date,column_value-1)))
over (partition by payment_id) minyr,
min(start_date) over (partition by payment_id) start_date,
max(end_date) over (partition by payment_id) end_date,
count(*) over
(partition by payment_id, extract(year from add_months(start_date,column_value-1)))
nb_payment
from payment_tbl,
table(cast(multiset(select level from dual
connect by level <= number_of_payments)
as sys.odciNumberList))
)
select d.payment_id, d.start_date, d.end_date,
sum(case when d.yr = d.minyr+0 then d.amount end) year1,
sum(case when d.yr = d.minyr+1 then d.amount end) year2,
sum(case when d.yr = d.minyr+2 then d.amount end) year3,
sum(case when d.yr = d.minyr+3 then d.amount end) year4,
sum(case when d.yr = d.minyr+4 then d.amount end) year5,
sum(case when d.yr > d.minyr+4 then d.amount end) thereafter,
sum(d.amount) total,
max(case
when d.payment_id is null or p.payment_id is null
or p.nb_payment != d.nb_payment
or p.amount != d.amount
or p.start_date != d.start_date
or p.end_date != d.end_date
then 1/0 end) "Inconsistency in tables",
max(case
when p.maxcnt > 1
then 1/0 end) "Duplicated month",
max(case
when d.number_of_payments != d.number_of_months
then 1/0 end) "Discrepancy in payment_tbl"
from data d
full outer join
( select payment_id, substr(period_name,5) yr, count(*) nb_payment, amount,
start_date, end_date, maxcnt
from ( select p.*, max(cnt) over (partition by payment_id) maxcnt
from ( select p.*,
min(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
over (partition by payment_id) start_date,
add_months(max(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
over (partition by payment_id),1)-1 end_date,
count(*) over (partition by payment_id, period_name) cnt
from payment_schedule_tbl p ) p ) p
group by payment_id, substr(period_name,5), amount, start_date, end_date, maxcnt ) p
on p.payment_id = d.payment_id and p.yr = d.yr and p.amount = d.amount
group by d.payment_id, d.minyr, d.start_date, d.end_date
order by d.payment_id
/[Updated on: Sat, 16 October 2021 15:10] Report message to a moderator |
|
|
|
| Re: transpose rows into columns [message #685068 is a reply to message #685065] |
Sun, 17 October 2021 11:14 |
|
suji6281
Messages: 130
Registered: September 2014
|
Senior Member |
|
|
Hi Michel,
Thanks for sharing the modified SQL.
Please help me with the new SQL that displays new output columns or fields in addition to the existing output columns.
Those new columns are from the other table(s).
CREATE TABLE PAYMENT_HDR_TBL
(PAYMENT_ID INT NOT NULL, PAYMENT_NUMBER VARCHAR(20), DESCR VARCHAR(50), ASSET_NUMBER VARCHAR(15), PAYMENT_TYPE VARCHAR(10))
INSERT INTO PAYMENT_HDR_TBL VALUES (56100, 'VH2678190', 'VH-DESCR-2678190', '56719201', 'MONTHLY');
PAYMENT_NUMBER DESCR ASSET_NUMBER PAYMENT_TYPE PAYMENT_ID START_DATE END_DATE YEAR1 YEAR2
--------------- ----------------- ----------- ----------- ---------- ---------- ---------- ---------- ----------
VH2678190 VH-DESCR-2678190 56719201 MONTHLY 56100 01-AUG-2021 31-JUL-2026 500 1200
YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL
---------- ---------- ------ ----------- -----------
1200 1200 1200 700 6000
Thank you.
Regards
Suji
|
|
|
|
|
|
|
|
|
|
|
|
| Re: transpose rows into columns [message #685129 is a reply to message #685128] |
Wed, 27 October 2021 12:26 |
|
Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> def start_year=2021
SQL> with
2 data as (
3 select payment_id, amount,
4 to_number(substr(period_name,5)) yr,
5 min(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
6 over (partition by payment_id) start_date,
7 add_months(max(to_date(period_name, 'MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'))
8 over (partition by payment_id),1)-1 end_date
9 from payment_schedule_tbl
10 )
11 select payment_id, start_date, end_date,
12 nvl(sum(case when yr = &start_year+0 then amount end),0) year1,
13 nvl(sum(case when yr = &start_year+1 then amount end),0) year2,
14 nvl(sum(case when yr = &start_year+2 then amount end),0) year3,
15 nvl(sum(case when yr = &start_year+3 then amount end),0) year4,
16 nvl(sum(case when yr = &start_year+4 then amount end),0) year5,
17 nvl(sum(case when yr > &start_year+4 then amount end),0) thereafter,
18 nvl(sum(case when yr < &start_year then amount end),0) total_before_&start_year,
19 nvl(sum(case when yr >= &start_year then amount end),0) total_from_&start_year,
20 sum(amount) overall_total
21 from data
22 group by payment_id, start_date, end_date
23 order by payment_id
24 /
PAYMENT_ID START_DATE END_DATE YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL_BEFORE_2021 TOTAL_FROM_2021 OVERALL_TOTAL
---------- ----------- ----------- ----- ----- ----- ----- ----- ---------- ----------------- --------------- -------------
56100 01-AUG-2021 31-JUL-2026 500 1200 1200 1200 1200 700 0 6000 6000
1 row selected.
SQL> def start_year=2023
SQL> /
PAYMENT_ID START_DATE END_DATE YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL_BEFORE_2023 TOTAL_FROM_2023 OVERALL_TOTAL
---------- ----------- ----------- ----- ----- ----- ----- ----- ---------- ----------------- --------------- -------------
56100 01-AUG-2021 31-JUL-2026 1200 1200 1200 700 0 0 1700 4300 6000
1 row selected.
SQL> def start_year=2019
SQL> /
PAYMENT_ID START_DATE END_DATE YEAR1 YEAR2 YEAR3 YEAR4 YEAR5 THEREAFTER TOTAL_BEFORE_2019 TOTAL_FROM_2019 OVERALL_TOTAL
---------- ----------- ----------- ----- ----- ----- ----- ----- ---------- ----------------- --------------- -------------
56100 01-AUG-2021 31-JUL-2026 0 0 500 1200 1200 3100 0 6000 6000
1 row selected. |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 23 05:36:44 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
