Home » SQL & PL/SQL » SQL & PL/SQL » Displaying calculated field with selected rows and Adding row according to date condition (11.2.0.1.0)
Displaying calculated field with selected rows and Adding row according to date condition [message #637858] |
Thu, 28 May 2015 00:10 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have two challenges in writing a query that needs your support.
- Transaction table holds several transaction type (withdrawal and payment)
- Interest table holds interest calculated based on withdrawals and payment of type loan repayment only (type_id 6)
- there is no link between the two tables except the date fields (transaction T_DATE = interest I_FROM_DATE)
- It can be safely assumed that the order of withdrawals and payment in transaction table match their interest peers in interest table
Requirement1: display all interest next to their source withdrawal or loan repayment
My Solution: use rownumber to match records and case to separate withdrawal amount from loan repayment amount.
Question1: It is working fine, but please tell me in case of any enhancement or suggestion.
Requirement2: in case there is a transaction of type_id = 7 (interest repayment) the amount should appear on a separate column INTEREST_REPAY_AMOUNT next to the last rows for this date
My Solution: use a separate select (table C) to add the new column. Use outer join to account for no-type-7 case
Question2: my solution shows INTEREST_REPAY_AMOUNT next to every row for a certain date, while I need to show it only next to the last row of that date. What do I need to do in order to achieve this?
Requirement3 (the difficult one):
- there is a table called SYSTEM_DATES that has two main dates first_date and second_date(in this example 1 Dec and 1 June)
- in case all I_From_dates in the output are after the first date, a row should be inserted at the beginning carrying first_date value as t_date and all other fields empty
- in case one or more I_From_date less than first date, this row should be inserted in its correct t_date order
- in case there is one or more I_FROM_DATE = firs_date, this row should NOT be inserted
- the second_date should be inserted at the end carrying second_date as t_date and all other fields empty
- in case there is one or more I_FROM_DATE = second_date, this row should NOT be inserted
- this should be repeated for every year (in case i_from_date span more than one year)
My Solution: I failed to do it using SQL and not sure if its possible. Trying PL/SQL with no luck yet, however I prefer guidance or ideas to have the requirements met using SQL if possible.
Question3: Appreciate any suggestions or guidance.
CREATE TABLE TRY_TRANSACTION
(
TRAN_SERIAL NUMBER PRIMARY KEY,
TYPE_ID NUMBER, --2 = WITHDRAWAL, 6 = LOAN REPAYMENT, 7 = INTEREST REPAYMENT
T_DATE DATE,
AMOUNT NUMBER
);
CREATE TABLE TRY_INTEREST
(
INT_SERIAL NUMBER PRIMARY KEY,
I_FROM_DATE DATE,
I_DUE_DATE DATE,
AMOUNT NUMBER
);
CREATE TABLE TRY_SYS_DATE
(
FIRST_DATE DATE, --WHAT IS NEEDED IS JUST THE MONTH AND THE DAY (YEAR IS IGNORED)
SECOND_DATE DATE --WHAT IS NEEDED IS JUST THE MONTH AND THE DAY (YEAR IS IGNORED)
);
INSERT ALL
INTO TRY_TRANSACTION VALUES(1,2,TO_DATE('20-01-2015','DD-MM-YYYY'),1000)
INTO TRY_TRANSACTION VALUES(2,2,TO_DATE('20-01-2015','DD-MM-YYYY'),2000)
INTO TRY_TRANSACTION VALUES(3,6,TO_DATE('20-01-2015','DD-MM-YYYY'),33000)
INTO TRY_TRANSACTION VALUES(4,7,TO_DATE('20-01-2015','DD-MM-YYYY'),500)
INTO TRY_INTEREST VALUES(91,TO_DATE('20-01-2015','DD-MM-YYYY'),TO_DATE('01-06-2015','DD-MM-YYYY'),50)
INTO TRY_INTEREST VALUES(92,TO_DATE('20-01-2015','DD-MM-YYYY'),TO_DATE('01-06-2015','DD-MM-YYYY'),100)
INTO TRY_INTEREST VALUES(93,TO_DATE('20-01-2015','DD-MM-YYYY'),TO_DATE('01-06-2015','DD-MM-YYYY'),30)
INTO TRY_TRANSACTION VALUES(5,2,TO_DATE('17-02-2015','DD-MM-YYYY'),5000)
INTO TRY_TRANSACTION VALUES(6,2,TO_DATE('17-02-2015','DD-MM-YYYY'),2000)
INTO TRY_TRANSACTION VALUES(7,2,TO_DATE('17-02-2015','DD-MM-YYYY'),8000)
INTO TRY_INTEREST VALUES(94,TO_DATE('17-02-2015','DD-MM-YYYY'),TO_DATE('01-06-2015','DD-MM-YYYY'),250)
INTO TRY_INTEREST VALUES(95,TO_DATE('17-02-2015','DD-MM-YYYY'),TO_DATE('01-06-2015','DD-MM-YYYY'),100)
INTO TRY_INTEREST VALUES(96,TO_DATE('17-02-2015','DD-MM-YYYY'),TO_DATE('01-06-2015','DD-MM-YYYY'),400)
INTO TRY_SYS_DATE VALUES(TO_DATE('01-12','DD-MM'),TO_DATE('01-06','DD-MM'))
SELECT * FROM DUAL;
SELECT WITHDRAWAL_AMOUNT,PAYMENT_AMOUNT, T_DATE, TYPE_ID, I_AMOUNT, I_FROM_DATE,I_DUE_DATE, INT_SERIAL
--/* requirement 2
, INTEREST_REPAY_AMOUNT
--*/
FROM
(
SELECT
(CASE TYPE_ID
WHEN 2 THEN AMOUNT
ELSE NULL
END)
WITHDRAWAL_AMOUNT,
(CASE TYPE_ID
WHEN 6 THEN AMOUNT
ELSE NULL
END)
PAYMENT_AMOUNT,
T_DATE, TYPE_ID, ROWNUM R1
FROM TRY_TRANSACTION
WHERE TYPE_ID IN (2,6)
) A,
(
SELECT AMOUNT I_AMOUNT,I_FROM_DATE, I_DUE_DATE, INT_SERIAL, ROWNUM R2
FROM TRY_INTEREST
) B
--/* requirement 2
,
(SELECT AMOUNT INTEREST_REPAY_AMOUNT, T_DATE INT_REP_DATE
FROM TRY_TRANSACTION
WHERE TYPE_ID = 7
)C
--*/
WHERE
I_DUE_DATE = TO_DATE('01-06-2015','DD-MM-YYYY')
--/* requirement 2
AND T_DATE = INT_REP_DATE (+)
--*/
AND A.R1 =B.R2
ORDER BY A.T_DATE, INT_SERIAL;
Many thanks,
Ferro
[Updated on: Thu, 28 May 2015 00:20] Report message to a moderator
|
|
|
|
Re: Displaying calculated field with selected rows and Adding row according to date condition [message #637866 is a reply to message #637864] |
Thu, 28 May 2015 01:25 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi Michel,
note: my sample data only covers the case when inserting the two dummy records (first_date at the beginning and second_date at the end) are needed. It also does not cover the case when i_from_date spans more than one year.
the desired output is:
note: I am currently learning about using model but appreciate your help.
WITHDRAWAL_AMOUNT PAYMENT_AMOUNT T_DATE TYPE_ID I_AMOUNT I_FROM_DATE I_DUE_DATE INT_SERIAL INTEREST_REPAY_AMOUNT
(null) (null) 01-12-2014 12:00:00 AM (null) (null) (null) (null) (null) (null)
1000 (null) 20-Jan-15 12:00:00 AM 2 50 20-Jan-15 12:00:00 AM 01-Jun-15 12:00:00 AM 91 (null)
2000 (null) 20-Jan-15 12:00:00 AM 2 100 20-Jan-15 12:00:00 AM 01-Jun-15 12:00:00 AM 92 (null)
(null) 33000 20-Jan-15 12:00:00 AM 6 30 20-Jan-15 12:00:00 AM 01-Jun-15 12:00:00 AM 93 500
5000 (null) 17-Feb-15 12:00:00 AM 2 250 17-Feb-15 12:00:00 AM 01-Jun-15 12:00:00 AM 94 (null)
2000 (null) 17-Feb-15 12:00:00 AM 2 100 17-Feb-15 12:00:00 AM 01-Jun-15 12:00:00 AM 95 (null)
8000 (null) 17-Feb-15 12:00:00 AM 2 400 17-Feb-15 12:00:00 AM 01-Jun-15 12:00:00 AM 96 (null)
(null) (null) 01-06-2015 12:00:00 AM (null) (null) (null) (null) (null) (null)
[Updated on: Thu, 28 May 2015 01:29] Report message to a moderator
|
|
|
|
Re: Displaying calculated field with selected rows and Adding row according to date condition [message #637895 is a reply to message #637869] |
Thu, 28 May 2015 06:37 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
For requirement3, I have the following query that gives me for each year, whether the first_Date or the second_date should be added or not:
insert all --adding more data to sample
INTO TRY_TRANSACTION VALUES(10,2,TO_DATE('01-06-2013','DD-MM-YYYY'),8800)
INTO TRY_TRANSACTION VALUES(11,2,TO_DATE('01-06-2013','DD-MM-YYYY'),800)
INTO TRY_INTEREST VALUES(99,TO_DATE('01-06-2013','DD-MM-YYYY'),TO_DATE('01-06-2015','DD-MM-YYYY'),290)
INTO TRY_INTEREST VALUES(100,TO_DATE('01-06-2013','DD-MM-YYYY'),TO_DATE('01-06-2015','DD-MM-YYYY'),100)
select * from dual;
select year_no, insert_first_date, insert_second_date
FROM
(
select year_no,
(
case
when to_char(i_from_date,'DD-MM') = to_char(first_date,'DD-MM')
then 0
else 1
end
)
insert_first_date,
(
case
when to_char(i_from_date,'DD-MM') = to_char(second_date,'DD-MM')
then 0
else 1
end
) insert_second_date
from (
select distinct i_from_date, FIRST_DATE, SECOND_DATE, to_char(i_from_date,'YYYY') Year_no
from TRY_INTEREST, TRY_SYS_DATE
)
) all_dates
group by year_no, insert_first_date, insert_second_date;
but stil:
1- could not use model to add the dummy rows to the first query
2- did not solve the problem for requirement2
Thanks Ferro
|
|
|
Re: Displaying calculated field with selected rows and Adding row according to date condition [message #637969 is a reply to message #637895] |
Sat, 30 May 2015 23:50 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I had to address requirement 3 in the hard way as I failed to implement it using Model. I really appreciate any help regarding the use of Model in this case.
Also could not remove the extra column values as per requirement 2, appreciate any help.
My working solution (although I am convinced it is not a good one):
-- generate the needed extra rows by creating an intermediate query that flags the needed rows and then generate the rows by referring to this
-- query. Of course the final step is to union the output of the below with the main query that solves requirements 1, and 2
with all_dates AS
(
select year_no, insert_first_date, insert_second_date
FROM
(
select year_no,
(
case
when to_char(i_from_date,'DD-MM') = to_char(first_date,'DD-MM')
then 0
else 1
end
)
insert_first_date,
(
case
when to_char(i_from_date,'DD-MM') = to_char(second_date,'DD-MM')
then 0
else 1
end
) insert_second_date
from (
select distinct i_from_date, FIRST_DATE, SECOND_DATE, to_char(i_from_date,'YYYY') Year_no
from TRY_INTEREST, TRY_SYS_DATE
)
)
group by year_no, insert_first_date, insert_second_date
)
SELECT NULL, NULL,'FIRST_DATE ' || year_no, NULL, NULL, NULL, NULL, NULL, NULL
FROM ALL_DATES
WHERE INSERT_FIRST_DATE = 1
UNION
SELECT NULL, NULL,'SECOND_DATE ' || year_no, NULL, NULL, NULL, NULL, NULL, NULL
FROM ALL_DATES
WHERE INSERT_SECOND_DATE = 1;
Thanks Ferro
|
|
|
Goto Forum:
Current Time: Mon Mar 18 23:38:00 CDT 2024
|