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 Go to next message
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 #637864 is a reply to message #637858] Thu, 28 May 2015 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What should be the result for the data your gave?

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 Go to previous messageGo to next message
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 #637869 is a reply to message #637866] Thu, 28 May 2015 01:30 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Sorry if my repeated updates confused you, the sample output is final now.
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Identify bottlenecks in queries
Next Topic: Recursive Hierarchy Help
Goto Forum:
  


Current Time: Mon Mar 18 23:38:00 CDT 2024