This discussion is archived
3 Replies Latest reply: Mar 1, 2012 8:44 AM by Frank Kulash RSS

Tricky Analytics

user3308033 Newbie
Currently Being Moderated
Hi Gurus

The OUT_DTL Table contains details of outstanding balance for each transaction date of every application id : AID.
CREATE TABLE OUT_DTL
(
AID          NUMBER(5),
BAL_AMT          NUMBER(12,2),
BAL_INT          NUMBER(12,2),
TXN_DATE     DATE
);

 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 93000, 115044,  TO_Date( '03/31/2009 11:59:00 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 90000, 116242,  TO_Date( '05/18/2009 03:36:38 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 84000, 117352,  TO_Date( '07/02/2009 10:13:08 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 81000, 118205,  TO_Date( '08/10/2009 11:10:20 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 78000, 118871,  TO_Date( '09/09/2009 01:06:26 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 75000, 120175,  TO_Date( '11/09/2009 11:13:53 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 72000, 120689,  TO_Date( '12/04/2009 11:23:34 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 69000, 121439,  TO_Date( '01/11/2010 02:43:15 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 66000, 122063,  TO_Date( '02/13/2010 11:14:31 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 63000, 122388,  TO_Date( '03/03/2010 11:14:45 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 60000, 122854,  TO_Date( '03/30/2010 09:45:58 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 60000, 122932,  TO_Date( '03/31/2010 11:59:00 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 57000, 123709,  TO_Date( '05/21/2010 11:47:34 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 54000, 124021,  TO_Date( '06/10/2010 11:40:36 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 51000, 124406,  TO_Date( '07/06/2010 11:25:35 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 48000, 124420,  TO_Date( '07/07/2010 10:28:39 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 45000, 124946,  TO_Date( '08/16/2010 03:50:58 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 42000, 125661,  TO_Date( '10/13/2010 02:18:36 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 39000, 125995,  TO_Date( '11/11/2010 03:26:49 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 36000, 126572,  TO_Date( '01/04/2011 10:08:56 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 33000, 126651,  TO_Date( '01/12/2011 10:41:17 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 32000, 126651,  TO_Date( '01/12/2011 10:42:53 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 29000, 126896,  TO_Date( '02/09/2011 12:56:23 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 28000, 126896,  TO_Date( '02/09/2011 12:57:15 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 25000, 127134,  TO_Date( '03/12/2011 10:15:27 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 24000, 127134,  TO_Date( '03/12/2011 10:31:52 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 21000, 127252,  TO_Date( '03/30/2011 02:38:49 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 20000, 127252,  TO_Date( '03/30/2011 02:39:11 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 16000, 127252,  TO_Date( '03/30/2011 03:46:25 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 16000, 127371,  TO_Date( '03/31/2011 11:59:00 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 13000, 127625,  TO_Date( '06/23/2011 03:59:05 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 9000, 127629,  TO_Date( '06/24/2011 04:04:33 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 6000, 127671,  TO_Date( '07/11/2011 04:32:27 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 3000, 127714,  TO_Date( '08/06/2011 12:18:07 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 0, 127745,  TO_Date( '09/13/2011 10:44:46 AM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 0, 124745,  TO_Date( '10/05/2011 02:24:49 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 0, 121745,  TO_Date( '11/03/2011 12:22:21 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 0, 118745,  TO_Date( '12/23/2011 12:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 0, 114745,  TO_Date( '01/13/2012 04:05:42 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
INSERT INTO OUT_DTL ( AID, BAL_AMT, BAL_INT,
TXN_DATE ) VALUES ( 
2836, 0, 110745,  TO_Date( '02/14/2012 02:35:18 PM', 'MM/DD/YYYY HH:MI:SS AM')); 
commit;
Based on the information therein and using the following SQL, I have generated
the following report wherein I need further assistance of you gurus.
SELECT AID, BAL_AMT, BAL_INT, TXN_DATE, LEAD(TRUNC(TXN_DATE),1) OVER (ORDER BY TXN_DATE) - TRUNC(TXN_DATE) 
AS DAYS, ROUND((BAL_AMT * (LEAD(TRUNC(TXN_DATE),1) OVER (ORDER BY TXN_DATE) - TRUNC(TXN_DATE))),0) AS PRODUCTS,
 ROUND((BAL_AMT * (LEAD(TRUNC(TXN_DATE),1) OVER (ORDER BY TXN_DATE) - TRUNC(TXN_DATE)) * 
(SELECT B.INT_RATE FROM LOAN_APP_DTL A, LOAN_DTL B WHERE 
A.SEQ_ID = B.SEQ_ID AND A.AID = 2836) / 36600),2) INTT FROM OUT_DTL
WHERE AID = 2836

APP_ID     BALANCE_AMT     BALANCE_INT     DIFF_INT     TXN_DATE     DAYS     PRODUCTS     INTT
2,836     93,000.00     115,044.00     1,198.00     3/31/2009     48.00     4,464,000.00     1,220
2,836     90,000.00     116,242.00     1,110.00     5/18/2009     45.00     4,050,000.00     1,107
2,836     84,000.00     117,352.00     853.00          7/2/2009     39.00     3,276,000.00     895
2,836     81,000.00     118,205.00     666.00          8/10/2009     30.00     2,430,000.00     664
2,836     78,000.00     118,871.00     1,304.00     9/9/2009     61.00     4,758,000.00     1,300
2,836     75,000.00     120,175.00     514.00          11/9/2009     25.00     1,875,000.00     512
2,836     72,000.00     120,689.00     750.00          12/4/2009     38.00     2,736,000.00     748
2,836     69,000.00     121,439.00     624.00          1/11/2010     33.00     2,277,000.00     622
2,836     66,000.00     122,063.00     325.00          2/13/2010     18.00     1,188,000.00     325
2,836     63,000.00     122,388.00     466.00          3/3/2010     27.00     1,701,000.00     465
2,836     60,000.00     122,854.00     78.00          3/30/2010     1.00     60,000.00     16
2,836     60,000.00     122,932.00     777.00          3/31/2010     51.00     3,060,000.00     836
2,836     57,000.00     123,709.00     312.00          5/21/2010     20.00     1,140,000.00     311
2,836     54,000.00     124,021.00     385.00          6/10/2010     26.00     1,404,000.00     384
2,836     51,000.00     124,406.00     14.00          7/6/2010     1.00     51,000.00     14
2,836     48,000.00     124,420.00     526.00          7/7/2010     40.00     1,920,000.00     525
2,836     45,000.00     124,946.00     715.00          8/16/2010     58.00     2,610,000.00     713
2,836     42,000.00     125,661.00     334.00          10/13/2010     29.00     1,218,000.00     333
2,836     39,000.00     125,995.00     577.00          11/11/2010     54.00     2,106,000.00     575
2,836     36,000.00     126,572.00     79.00          1/4/2011     8.00     288,000.00     79
2,836     33,000.00     126,651.00     0.00          1/12/2011     0.00     0.00          0
2,836     32,000.00     126,651.00     245.00          1/12/2011     28.00     896,000.00     245
2,836     29,000.00     126,896.00     0.00          2/9/2011     0.00     0.00          0
2,836     28,000.00     126,896.00     238.00          2/9/2011     31.00     868,000.00     237
2,836     25,000.00     127,134.00     0.00          3/12/2011     0.00     0.00          0
2,836     24,000.00     127,134.00     118.00          3/12/2011     18.00     432,000.00     118
2,836     21,000.00     127,252.00     0.00          3/30/2011     0.00     0.00          0
2,836     20,000.00     127,252.00     0.00          3/30/2011     0.00     0.00          0
2,836     16,000.00     127,252.00     119.00          3/30/2011     1.00     16,000.00     4
2,836     16,000.00     127,371.00     254.00          3/31/2011     84.00     1,344,000.00     367
2,836     13,000.00     127,625.00     4.00          6/23/2011     1.00     13,000.00     4
2,836     9,000.00     127,629.00     42.00          6/24/2011     17.00     153,000.00     42
2,836     6,000.00     127,671.00     43.00          7/11/2011     26.00     156,000.00     43
2,836     3,000.00     127,714.00     31.00          8/6/2011     38.00     114,000.00     31
2,836     0.00          127,745.00     0          9/13/2011     22.00     0.00          0
2,836     0.00          124,745.00     0          10/5/2011     29.00     0.00          0
2,836     0.00          121,745.00     0          11/3/2011     50.00     0.00          0
2,836     0.00          118,745.00     0          12/23/2011     21.00     0.00          0
2,836     0.00          114,745.00     0          1/13/2012     32.00     0.00          0
2,836     0.00          110,745.00     0          2/14/2012          
Using the above details :
a. I need to insert the code to sum up DIFF_INT & INTT for each financial year i.e. at the end of '31/03'
b. I need to decrease the days by 1 when ever the transaction date : TXN_DATE = '31/03'
c. Starting from the first record of DIFF_INT, I need to add the INTT and put the same in the 2nd row in a new column 'CUMM_INT'
d. Update the 'DIFF_INT' with 'CUMM_INT' for all the rows where balance_amt is > 0

Can anyone of you Gurus help me out.
  • 1. Re: Tricky Analytics
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    user3308033 wrote:
    Hi Gurus

    The OUT_DTL Table contains details of outstanding balance for each transaction date of every application id : AID. ...
    Thanks for posting the CREATE TABLE and INSERT statements for out_dtl.
    The query you posted references another table, loan_app_dtl, as well. Please post CREATE TABLE and INSERT statements for load_app_dtl.
    Always say which version of Oracle you're using.
    Based on the information therein and using the following SQL, I have generated
    the following report wherein I need further assistance of you gurus.
    SELECT AID, BAL_AMT, BAL_INT, TXN_DATE, LEAD(TRUNC(TXN_DATE),1) OVER (ORDER BY TXN_DATE) - TRUNC(TXN_DATE) 
    AS DAYS, ROUND((BAL_AMT * (LEAD(TRUNC(TXN_DATE),1) OVER (ORDER BY TXN_DATE) - TRUNC(TXN_DATE))),0) AS PRODUCTS,
    ROUND((BAL_AMT * (LEAD(TRUNC(TXN_DATE),1) OVER (ORDER BY TXN_DATE) - TRUNC(TXN_DATE)) * 
    (SELECT B.INT_RATE FROM LOAN_APP_DTL A, LOAN_DTL B WHERE 
    A.SEQ_ID = B.SEQ_ID AND A.AID = 2836) / 36600),2) INTT FROM OUT_DTL
    WHERE AID = 2836
    You should never write unformatted code like that.
    Even if you really like code that looks like that, don't post it. Format it so people can read and understand it.
    >
    APP_ID     BALANCE_AMT     BALANCE_INT     DIFF_INT     TXN_DATE     DAYS     PRODUCTS     INTT
    2,836     93,000.00     115,044.00     1,198.00     3/31/2009     48.00     4,464,000.00     1,220
    2,836     90,000.00     116,242.00     1,110.00     5/18/2009     45.00     4,050,000.00     1,107
    2,836     84,000.00     117,352.00     853.00          7/2/2009     39.00     3,276,000.00     895
    ... (35 rows cut)
    2,836     0.00          114,745.00     0          1/13/2012     32.00     0.00          0
    2,836     0.00          110,745.00     0          2/14/2012          
    Are you sure the query you posted produces that output? There's no column called DIFF_INT in your query. Also, the 4th column of your SELECT clause is a DATE, but the 4th column of the output is a NUMBER.

    Is it really necessary to post so much sample data? It would be a lot easier to understand the problem, and be sure we got the solution right, if there was much less to look at. Can't you show what you need to do with, say, 4 rows in one financial year, and another 4 rows in a different financial year?
    Using the above details :
    a. I need to insert the code to sum up DIFF_INT & INTT for each financial year i.e. at the end of '31/03'
    Use the analytic SUM function. You can't nest analytic fucntions, so you'll have to compute intt (and maybe diff_int) in a sub-query, before calling SUM.
    b. I need to decrease the days by 1 when ever the transaction date : TXN_DATE = '31/03'
    When do you need to decrease days? It's hard to tell if you don't explain, and only give one example.
    c. Starting from the first record of DIFF_INT, I need to add the INTT and put the same in the 2nd row in a new column 'CUMM_INT'
    To what do you need to add intt? Do you want a cumulative total? If so, use the analytic SUM function.
    d. Update the 'DIFF_INT' with 'CUMM_INT' for all the rows where balance_amt is > 0
    What are the exact results you want from the sample data? It's great to describe the results, but describe them in addition to, not instead of, actually showing them.
  • 2. Re: Tricky Analytics
    user3308033 Newbie
    Currently Being Moderated
    Hi there,

    I'll answer the queries one by one
    a. I need to subtract 1 day from computed days difference when the transaction date is 31st March, because the difference is inclusive of 31st which falls in the previous financial year. I need to count the days from 1st April to 31st March of the year. For example : When you subtract first 31/03/2009 from 18/05/2009, it shows days difference as 48, whereas actually they are 47, counting from 1st April : 30 days of April & 17 days of May.
    b. Similarly, I need to sum up the INT Column for every financial year i.e. 1st April to 31st March and display the sum in a separate row subsequent to 31st March, every year.
    c. I need to have a cummulative total for BAL_INT thereby creating a new column, say CUMM_INT i.e. starting from first record of BAL_INT i.e. 115044, add first record of INT i.e. 1220 and display in the second row in the CUMM_INT column and so on.
    d. When you get the above at 'c', update BAL_INT with CUMM_INT for all the row where BAL_AMT > 0. This is because BAL_INT is giving wrong results and needs to be updated.
    e. Oracle version is 9i.

    Thanks
  • 3. Re: Tricky Analytics
    Frank Kulash Guru
    Currently Being Moderated
    user3308033 wrote:
    Hi there,

    I'll answer the queries one by one ...
    Okay, but remember, I can't do anything until I have
    (1) a sample version of your other table, load_app_dtl (CREATE TABLE and INSERT statements), and
    (2) the exact results you want from that sample data.

    I could get a solution much faster if you also posted
    (3) a smaller set of sample data
    (4) a fiormatted version of your current query
    (5) your Oracle version

    Didn't I mention all this before?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points