This discussion is archived
13 Replies Latest reply: Apr 5, 2013 10:47 AM by Frank Kulash RSS

calculating liquidation - percentages with subqueries

1001148 Newbie
Currently Being Moderated
Hi,

I have a query a sql statement as follows, which I am using to query an oracle database:

SELECT
EXTRACT(YEAR FROM "CLIENT_TH"."CREATED_DATE") AS LOAD_YEAR,
EXTRACT(MONTH FROM "CLIENT_TH"."CREATED_DATE") AS LOAD_MONTH,
CONCAT(CONCAT(EXTRACT(MONTH FROM "CLIENT_TH"."CREATED_DATE") , '-'), EXTRACT(YEAR FROM "CLIENT_TH"."CREATED_DATE") ) AS LOAD_MONTHNAME,
EXTRACT(YEAR FROM "DOC_HDR_TH"."CREATED_DATE") AS PAYMENT_YEAR,
EXTRACT(MONTH FROM "DOC_HDR_TH"."CREATED_DATE") AS PAYMENT_MONTH,
CONCAT(CONCAT(EXTRACT(MONTH FROM "DOC_HDR_TH"."CREATED_DATE") , '-'), EXTRACT(YEAR FROM "DOC_HDR_TH"."CREATED_DATE") ) AS PAYMENT_MONTHNAME,
SUM("DOC_HDR_TH"."TOTAL_GROSS"*-1) AS PAYMENT_AMOUNT,
"CLIENT_TH"."SPARE_CHAR_05" AS CLIENT_NAME,
"CLIENT_TH"."SPARE_CHAR_21" AS CLIENT_CODE
FROM
"SL"."CLIENT_TH" "CLIENT_TH" INNER JOIN
"SL"."TRAN_TH" "TRAN_TH" ON ("CLIENT_TH"."CLIENT_ID"="TRAN_TH"."CLIENT_ID") INNER JOIN
"SL"."DOC_LINK_TH" "DOC_LINK_TH" ON ("TRAN_TH"."TRAN_ID"="DOC_LINK_TH"."TRAN_ID") INNER JOIN
"SL"."DOC_HDR_TH" "DOC_HDR_TH" ON ("DOC_LINK_TH"."DOC_ID"="DOC_HDR_TH"."DOC_ID")
WHERE
"CLIENT_TH"."SPARE_CHAR_01" LIKE 'SBH/%') AND
("DOC_HDR_TH"."DOC_TYPE" NOT IN ('ADJ', 'Adjustment', 'ADMIN FEE', 'AOE FEE', 'ARREARS', 'CHARGE APP FEE', 'CLI', 'CO FEES', 'COSTS', 'CWO', 'DM', 'FEES', 'HCEO Fees', 'IJADJ', 'INTEREST', 'INV', 'JUDGEMENT COSTS', 'LATE PAYMENT COMP', 'OSB ADJ', 'SET WRITE OFF', 'SET WRITE OFF', 'WARRANT COSTS', 'WARRANT FEE')) and
TRUNC("DOC_HDR_TH"."CREATED_DATE") BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') AND SYSDATE-1AND
TRUNC("CLIENT_TH"."CREATED_DATE") BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-24),'MM') AND SYSDATE-1
GROUP BY
"CLIENT_TH"."SPARE_CHAR_05",
"CLIENT_TH"."SPARE_CHAR_21",
EXTRACT(MONTH FROM "CLIENT_TH"."CREATED_DATE") ,
EXTRACT(YEAR FROM "CLIENT_TH"."CREATED_DATE"),
EXTRACT(MONTH FROM "DOC_HDR_TH"."CREATED_DATE"),
EXTRACT(YEAR FROM "DOC_HDR_TH"."CREATED_DATE")


This transposes into the following outcome:

Payment Month
Jun-12 Jul-12 Aug-12

Load Date
Jun-12 100.12 200.00 300.00
Jul-12 300.00 400.00
Aug-12 500.00

This is working perfectly.

However, where I am having the problem is creating a liquidation batchtracker, where I want the outcome to look something like the following

Payment Months since Load
M1 M2 M3 M4 M5

Load Date
Jun-12 0.01% 0.02% 0.2% 0.3% 0.4%
July-12 0.02% 0.04% 0.4% 0.3%
Aug-12 0.01% 0.02% 0.2%
Sept-12 0.01% 0.02%
Oct-12 0.01%


The code I have so far is:

SELECT
CONCAT(CONCAT(EXTRACT(MONTH FROM Payments.LOAD_DATE) , '-'), EXTRACT(YEAR FROM Payments.LOAD_DATE) ) AS LOAD_MONTHNAME,
Original.ORIGINAL_BALANCE as ORIGINAL_BALANCE
FROM
     (SELECT
     "CLIENT_TH"."CLIENT_ID",
     SUM("DOC_HDR_TH"."TOTAL_GROSS") AS ORIGINAL_BALANCE
     FROM
     "SL"."CLIENT_TH" "CLIENT_TH" INNER JOIN
     "SL"."TRAN_TH" "TRAN_TH" ON ("CLIENT_TH"."CLIENT_ID"="TRAN_TH"."CLIENT_ID") INNER JOIN
     "SL"."DOC_LINK_TH" "DOC_LINK_TH" ON ("TRAN_TH"."TRAN_ID"="DOC_LINK_TH"."TRAN_ID") INNER JOIN
     "SL"."DOC_HDR_TH" "DOC_HDR_TH" ON ("DOC_LINK_TH"."DOC_ID"="DOC_HDR_TH"."DOC_ID")
     WHERE
     "CLIENT_TH"."SPARE_CHAR_01" = 'SBH/73020000003657' and
     "CLIENT_TH"."SPARE_CHAR_01" LIKE 'SBH/%' AND
     "DOC_HDR_TH"."DOC_TYPE" IN ('INV', 'ARREARS')
     GROUP BY
     "CLIENT_TH"."CLIENT_ID") Original LEFT OUTER JOIN
          (SELECT
          "CLIENT_TH"."CLIENT_ID",
          "CLIENT_TH"."SPARE_CHAR_01" AS REFERENCE,
          "CLIENT_TH"."CREATED_DATE"AS LOAD_DATE,
          "DOC_HDR_TH"."CREATED_DATE" AS PAYMENT_DATE,
          "DOC_HDR_TH"."TOTAL_GROSS"*-1 AS PAYMENT_AMOUNT,
          "CLIENT_TH"."SPARE_CHAR_05" AS CLIENT_NAME,
          ROUND(MONTHS_BETWEEN("DOC_HDR_TH"."DOC_DATE","CLIENT_TH"."CREATED_DATE") ) AS LIQUIDATION_MONTH
          FROM
          "SL"."CLIENT_TH" "CLIENT_TH" INNER JOIN
          "SL"."TRAN_TH" "TRAN_TH" ON ("CLIENT_TH"."CLIENT_ID"="TRAN_TH"."CLIENT_ID") INNER JOIN
          "SL"."DOC_LINK_TH" "DOC_LINK_TH" ON ("TRAN_TH"."TRAN_ID"="DOC_LINK_TH"."TRAN_ID") INNER JOIN
          "SL"."DOC_HDR_TH" "DOC_HDR_TH" ON ("DOC_LINK_TH"."DOC_ID"="DOC_HDR_TH"."DOC_ID")
          WHERE
          "CLIENT_TH"."SPARE_CHAR_01" like 'S%' and
          ("DOC_HDR_TH"."DOC_TYPE" NOT IN ('ADJ', 'Adjustment', 'ADMIN FEE', 'AOE FEE', 'ARREARS', 'CHARGE APP FEE', 'CLI', 'CO FEES', 'COSTS', 'CWO', 'DM', 'FEES', 'HCEO Fees', 'IJADJ', 'INTEREST', 'INV', 'JUDGEMENT COSTS', 'LATE PAYMENT COMP', 'OSB ADJ', 'SET WRITE OFF', 'SET WRITE OFF', 'WARRANT COSTS', 'WARRANT FEE')) and
          TRUNC("DOC_HDR_TH"."DOC_DATE") BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') AND SYSDATE-1) Payments on (Payments."CLIENT_ID" = Original."CLIENT_ID")
WHERE
Payments.REFERENCE like 'S%'



I would really appreciate any help that anyone can give me.

Many thanks,

Sammy

Edited by: 998145 on 04-Apr-2013 12:42

Edited by: 998145 on 05-Apr-2013 02:31
  • 1. Re: calculating liquidation - percentages with subqueries
    Zoltan Kecskemethy Expert
    Currently Being Moderated
    Welcome to the forum,

    Can you please check {thread:id=2174552} and re-phrase your question? Because I don't really understand it.

    HTH, Zoltan
  • 2. Re: calculating liquidation - percentages with subqueries - Urgent!
    pollywog Expert
    Currently Being Moderated
    welcome to the forum.
    unlikely you will get much response from your question.
    a better idea may be to show a small example of your input and your desired output.
    maybe something like

    here is my input
    with t as (select 'a' col1, 1 col2 from dual union all
               select 'b', 2 from dual union all
               select 'c', 3 from dual union all
               select 'd', 4 from dual 
               )
    select * from t;
    
    COL1     COL2
    a     1
    b     2
    c     3
    d     4
    I would like output like
    COL1     COL2    COL3
    a     1           value1
    b     2           value2
    c     3           value3
    d     4           value4
    then a statement of what you are trying to accomplish from the input to the output.
  • 3. Re: calculating liquidation - percentages with subqueries
    1001148 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the quick response. What I basically want to do with liquidation batchtracker is calculate the sum of the cash coming in, grouped by when its come in (month/year) and when the account was originally loaded on the system (month/year), as a percentage of the total balances loaded in month year.

    So for example, if 5 accounts were loaded with a balance of £100.00 each in Jun-12, then the Jun-12 balance would be £500.00. If the total amount paid by those accounts in Jun-12 was £100.00 then the liquidation in Jun-12(Ie Month1) would be 20% (ie (100/500)*100). If the same accounts paid £50 in Month 2 (being the months difference between the payment date and the load date) then the resulting liquidation for month 2 would be 10%. So the resulting tracker would look like:

    M1 M2

    LOAD
    Jun-12 20% 10%


    I hope this makes sense, but if not let me know and I will try and send you an example in an excel document.

    Thanks,

    Sammy
  • 4. Re: calculating liquidation - percentages with subqueries - Urgent!
    1001148 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the advice. I am very new to sql and using forums, so apologies if my explanations are a little confusing. I noticed that you have sample tables in your response wtih your tables all perfectly aligned. How do you do that, as when I tried to give sample output in my response, when I posted it it didn't maintain the spacing, and the columns are all misaligned.

    Thanks,

    Sammy

    Edited by: 998145 on 04-Apr-2013 13:49
  • 5. Re: calculating liquidation - percentages with subqueries - Urgent!
    AlbertoFaenza Expert
    Currently Being Moderated
    998145 wrote:
    Hi,

    How do you do that, as when I tried to give sample output in my response, when I posted it it didn't maintain the spacing, and the columns are all misaligned.

    Thanks,

    Sammy

    Edited by: 998145 on 04-Apr-2013 13:49
    You should lose some time and read SQL and PL/SQL FAQ.

    Then you will find: when you put some code or output please enclose it between two lines starting with {noformat}
    {noformat}
    
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    You can also have a preview of your message while writing by clicking the preview tab on top.
    Regarding your question about liquidation calculation it is not clear what the input data is.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 6. Re: calculating liquidation - percentages with subqueries - Urgent!
    EdStevens Guru
    Currently Being Moderated
    998145 wrote:
    Hi,

    Thanks for the advice. I am very new to sql and using forums, so apologies if my explanations are a little confusing. I noticed that you have sample tables in your response wtih your tables all perfectly aligned. How do you do that, as when I tried to give sample output in my response, when I posted it it didn't maintain the spacing, and the columns are all misaligned.

    Thanks,

    Sammy

    Edited by: 998145 on 04-Apr-2013 13:49
    Surround your code with the \
     tag.  So as you enter it in your post, it looks like this:
    
    \
    select sysdate from dual;
    \
    And when posted it will look like this:
    select sysdate from dual;
    And lose the "urgent".  There is no "urgent" here.  This is a volunteer forum.  Your problem is no more "urgent" than anyone else's.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 7. Re: calculating liquidation - percentages with subqueries - Urgent!
    1001148 Newbie
    Currently Being Moderated
    Hi,

    Let me see if I can somehow start again. So, I have a piece of code (Payments Query), which selects total payments grouped by load month/year and payment month/year. The resulting table looks as follows:
    LOAD_MONTHNAME         PAYMENT_MONTHNAME           PAYMENT_AMOUNT            CLIENT_NAME                 CLIENT_CODE
    5-2012                               6-2012                                        30,000                        V                                      
    3-2012                               4-2012                                        10,000                        V     
    10-2012                             11-2012                                       40,000                        V     
    5-2012                               6-2012                                        5,000                          C     
    The code that gives me the above dataset is as follows:
    SELECT 
    CONCAT(CONCAT(EXTRACT(MONTH FROM "CLIENT_TH"."CREATED_DATE") , '-'), EXTRACT(YEAR FROM "CLIENT_TH"."CREATED_DATE") ) AS LOAD_MONTHNAME,
    CONCAT(CONCAT(EXTRACT(MONTH FROM "DOC_HDR_TH"."CREATED_DATE") , '-'), EXTRACT(YEAR FROM "DOC_HDR_TH"."CREATED_DATE") ) AS PAYMENT_MONTHNAME,
    SUM("DOC_HDR_TH"."TOTAL_GROSS"*-1) AS PAYMENT_AMOUNT, 
    "CLIENT_TH"."SPARE_CHAR_05" AS CLIENT_NAME,
    "CLIENT_TH"."SPARE_CHAR_21" AS CLIENT_CODE
     FROM  
     "SL"."CLIENT_TH" "CLIENT_TH" INNER JOIN 
    "SL"."TRAN_TH" "TRAN_TH" ON ("CLIENT_TH"."CLIENT_ID"="TRAN_TH"."CLIENT_ID") INNER JOIN
    "SL"."DOC_LINK_TH" "DOC_LINK_TH" ON ("TRAN_TH"."TRAN_ID"="DOC_LINK_TH"."TRAN_ID")  INNER JOIN
    "SL"."DOC_HDR_TH" "DOC_HDR_TH" ON ("DOC_LINK_TH"."DOC_ID"="DOC_HDR_TH"."DOC_ID")
     WHERE  
    ("CLIENT_TH"."SPARE_CHAR_01" LIKE 'BH/%' OR
    "CLIENT_TH"."SPARE_CHAR_01" LIKE 'SBH/%') AND
    ("DOC_HDR_TH"."DOC_TYPE" NOT IN ('ADJ', 'Adjustment', 'ADMIN FEE', 'AOE FEE', 'ARREARS', 'CHARGE APP FEE', 'CLI', 'CO FEES', 'COSTS', 'CWO', 'DM', 'FEES', 'HCEO Fees', 'IJADJ', 'INTEREST', 'INV', 'JUDGEMENT COSTS', 'LATE PAYMENT COMP', 'OSB ADJ', 'SET WRITE OFF', 'SET WRITE OFF', 'WARRANT COSTS', 'WARRANT FEE')) and
    TRUNC("DOC_HDR_TH"."CREATED_DATE") BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') AND SYSDATE-1AND
    TRUNC("CLIENT_TH"."CREATED_DATE") BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-24),'MM') AND SYSDATE-1
    GROUP BY
    "CLIENT_TH"."SPARE_CHAR_05", 
    "CLIENT_TH"."SPARE_CHAR_21",
    EXTRACT(MONTH FROM "CLIENT_TH"."CREATED_DATE") ,
    EXTRACT(YEAR FROM "CLIENT_TH"."CREATED_DATE"), 
    EXTRACT(MONTH FROM "DOC_HDR_TH"."CREATED_DATE"),
    EXTRACT(YEAR FROM "DOC_HDR_TH"."CREATED_DATE")
    I am then pivoting the above data returned by the sql in excel to display the data as below, with the LOAD_MONTHNAME as Row Labels and the PAYMENT_MONTHNAME as column labels.
                         PAYMENT_MONTHNAME
    LOAD_MN        4-2012               5-2012                  6-2012                     7-2012
    4-2011                 £226.00                £226.00          £191.00                      £235.00 
    5-2011                 £361.00                £303.50          £213.50                      £278.00 
    6-2011                 £231.83                £240.84          £150.00                      £35.00 
    7-2011                 £122.06                 £88.82          £190.63                      £158.80 
    The above all works perfectly. The next step I need to take (and is the part that I am having trouble with) is that I need to calculate the above cash values as a % of the total balance that was loaded in the month.

    The below code is what I have under a seperate query (Balance Query) to return the balances for each load_month:
    SELECT 
    "CLIENT_TH"."SPARE_CHAR_05",
    CONCAT(CONCAT(EXTRACT(MONTH FROM "CLIENT_TH"."CREATED_DATE") , '-'), EXTRACT(YEAR FROM "CLIENT_TH"."CREATED_DATE") ) AS LOAD_MONTHNAME,
    SUM("DOC_HDR_TH"."TOTAL_GROSS") as INVOICE_AMOUNT
     FROM   
    "SL"."CLIENT_TH" "CLIENT_TH" INNER JOIN
    "SL"."TRAN_TH" "TRAN_TH" ON ("CLIENT_TH"."CLIENT_ID"="TRAN_TH"."CLIENT_ID")  INNER JOIN
    "SL"."DOC_LINK_TH" "DOC_LINK_TH" ON ("TRAN_TH"."TRAN_ID"="DOC_LINK_TH"."TRAN_ID")  INNER JOIN
    "SL"."DOC_HDR_TH" "DOC_HDR_TH" ON ("DOC_LINK_TH"."DOC_ID"="DOC_HDR_TH"."DOC_ID")
     WHERE  
     (
    "DOC_HDR_TH"."DOC_TYPE"=N'INV') AND
     ("CLIENT_TH"."SPARE_CHAR_01" LIKE N'BH/%' OR 
    "CLIENT_TH"."SPARE_CHAR_01" LIKE N'SBH/%') 
    GROUP BY
    "CLIENT_TH"."SPARE_CHAR_05", EXTRACT(MONTH FROM "CLIENT_TH"."CREATED_DATE") ,EXTRACT(YEAR FROM "CLIENT_TH"."CREATED_DATE")
    This returns the below data:
    CLIENT_NAME     LOAD_YEAR     LOAD_MONTH     LOAD_MONTHNAME     INVOICE_AMOUNT
    A                       2011     7                    7-2011     £20,000.00
    A                       2011     8                    8-2011     £30,000.00
    A                       2011     9                    9-2011     £3,000.00
    What I basically need to do is calculate the first payments query with the above balance query to show the liquidation percentage, and I am not sure how to combine the two queries. So, for example, for Client A, the payments that came in for the accounts with a LOAD_MONTHNAME OF 7-2007 were:
                                  PAYMENT_MONTHNAME
    LOAD_MN                4-2012               5-2012               6-2012                     7-2012
    7-2011                 £122.06      £88.82             £190.63                  £158.80 
    (taken directly from the Payments query results)

    These then need to be calculated for each monthname against the balance loaded in 7-2011 which is £20,000 (from Balance Query).

    I hope this now explains it better and thanks so much all of you for your help in looking at this.

    Thanks again,

    S

    Edited by: 998145 on 04-Apr-2013 16:49
  • 8. Re: calculating liquidation - percentages with subqueries - Urgent!
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    As the others have said, you need to post a little sample data (CREATE TABLE and INSERT statements) and the results you want from that data. See the forum FAQ {message:id=9360002}

    One of the few things I can do without that is to show how you can simplify your code, which will make debugging easier, both for you and even more for people who aren't as familiar as you are with the problem.

    Let's just look at the expression:
    CONCAT(CONCAT(EXTRACT(MONTH FROM "CLIENT_TH"."CREATED_DATE") , '-'), EXTRACT(YEAR FROM "CLIENT_TH"."CREATED_DATE") )
    First of all, lose the double-quotes. If a column- or table name requires double-quotes, it's just begging for trouble. If it doesn't require them (as in this case), why bother to use them? So the expression above could be written:
    CONCAT(CONCAT(EXTRACT(MONTH FROM CLIENT_TH.CREATED_DATE) , '-'), EXTRACT(YEAR FROM CLIENT_TH.CREATED_DATE) )
    Use the || operator instead of the CONCAT function. They both do exactly the same thing, but || is much easier to read and understand, so the same statement can be re-written:
    EXTRACT(MONTH FROM CLIENT_TH.CREATED_DATE) || '-'
                                         || EXTRACT(YEAR FROM CLIENT_TH.CREATED_DATE) 
    The expression is now so clear that we can see it's just a long-winded way of saying:
    TO_CHAR ( CLIENT_TH.CREATED_DATE
         , 'fmMM-YYYY'
         )
    Again, all 4 of the expressions above produce the same results. Which one do you find easier to read and understand? If there is a mistake, in which one would it be easier to find the mistake? Which do you think will best encourage people to read your code and try to help?
  • 9. Re: calculating liquidation - percentages with subqueries - Urgent!
    pollywog Expert
    Currently Being Moderated
    is this a fair representation of your input

    for the results of your first query
    WITH t1
          -- which selects total payments grouped by load month/year and payment month/year. The resulting table looks as follows:
         AS (SELECT TO_DATE ('05-2012', 'mm-yyyy') load_monthname,
                    TO_DATE ('06-2012', 'mm-yyyy') payment_monthname,
                    300 payment_amount,
                    30000 client_name,
                    'V' client_code
               FROM DUAL
             UNION ALL
             SELECT TO_DATE ('03-2012', 'mm-yyyy'),
                    TO_DATE ('04-2012', 'mm-yyyy'),
                    700,
                    10000,
                    'V'
               FROM DUAL
             UNION ALL
             SELECT TO_DATE ('10-2012', 'mm-yyyy'),
                    TO_DATE ('11-2012', 'mm-yyyy'),
                    250,
                    40000,
                    'V'
               FROM DUAL
             UNION ALL
             SELECT TO_DATE ('5-2012', 'mm-yyyy'),
                    TO_DATE ('6-2012', 'mm-yyyy'),
                    800,
                    5000,
                    'C'
               FROM DUAL),
               -- I am then pivoting the above data returned by the sql 
     sampledata as 
     (
    SELECT *
      FROM (SELECT * FROM t1) PIVOT (SUM (payment_amount)
                              FOR payment_monthname
                              IN  (TO_DATE ('04-2012', 'mm-yyyy') AS "04-2012",
                                  TO_DATE ('05-2012', 'mm-yyyy') AS "05-2012",
                                  TO_DATE ('06-2012', 'mm-yyyy') AS "06-2012",
                                  TO_DATE ('07-2012', 'mm-yyyy') AS "07-2012",
                                  TO_DATE ('08-2012', 'mm-yyyy') AS "08-2012",
                                  TO_DATE ('09-2012', 'mm-yyyy') AS "09-2012",
                                  TO_DATE ('10-2012', 'mm-yyyy') AS "10-2012",
                                  TO_DATE ('12-2012', 'mm-yyyy') AS "11-2012"
                                  ) ) )
     select * from sampledata;
     
    
    LOAD_MONTHNAME     CLIENT_NAME     CLIENT_CODE     04-2012     05-2012     06-2012     07-2012     08-2012     09-2012     10-2012     11-2012
    10/1/2012     40000     V                                        
    5/1/2012     5000     C               800                         
    3/1/2012     10000     V     700                                   
    5/1/2012     30000     V               300
    for the results of your second query
    WITH balance_query
         AS (SELECT 'A' client_name,
                    TO_DATE ('2011', 'yyyy') load_year,
                    TO_DATE ('07-2011', 'mm-yyyy') load_month,
                    20000 invoice_amount
               FROM DUAL
             UNION ALL
             SELECT 'A',
                    TO_DATE ('2011', 'yyyy'),
                    TO_DATE ('08-2011', 'mm-yyyy'),
                    30000
               FROM DUAL
             UNION ALL
             SELECT 'A',
                    TO_DATE ('2011', 'yyyy'),
                    TO_DATE ('09-2011', 'mm-yyyy'),
                    3000
               FROM DUAL)
    SELECT *
      FROM balance_query;
    
    CLIENT_NAME     LOAD_YEAR     LOAD_MONTH     INVOICE_AMOUNT
    A     4/1/2011     7/1/2011     20000
    A     4/1/2011     8/1/2011     30000
    A     4/1/2011     9/1/2011     3000
    if it is what would be your expected output?
    if it is not could you modify it and repost.

    Edited by: pollywog on Apr 5, 2013 7:27 AM
  • 10. Re: calculating liquidation - percentages with subqueries - Urgent!
    1001148 Newbie
    Currently Being Moderated
    Hi,

    The input you have posted is correct. So for the next stage, what I need to do is take each of the payments received in each of the cash months for a particular load_date, and divide it by the total amount loaded within that load month (given by the second query)

    So using the figures from your example below, your first input line, taking £300 payment received in 06-2012 and divide this by the total balance of what was loaded in June (which you don't have a figure for in your second example, but say it's £10,000. Therefore the liquidation percentage for that month becomes (300/10000)*100 = 3%.
    If £150 was received in Jul-2012, then the liquidation for the next payment month would be (£150/10000)*100 = 1.5%

    Therefore the table will look something like:
                      PAYMENT
                      06/2012              07/2012
    LOAD
    06/2012           3%                     1.5%
    Thanks very much,

    S
  • 11. Re: calculating liquidation - percentages with subqueries - Urgent!
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    I am so confused.
    998145 wrote:
    Hi,

    The input you have posted is correct.
    ...
    divide this by the total balance of what was loaded in June (which you don't have a figure for in your second example, but say it's £10,000. ...
    If the sample data is missing something, how can you say it's correct?
    Do you mean soemthing like "The input you have posted is correct *for those columns, but there is another column ...* ".
    Please post your complete sample data, and the results you want from that data.
  • 12. Re: calculating liquidation - percentages with subqueries - Urgent!
    1001148 Newbie
    Currently Being Moderated
    Hi,

    It's basically that they 2 queries are correct in what they are pulling back, I now want to take it one step further and use the results of both queries to work out a percentage liquidation. Is there any way I can attach an excel document to this thread to show you what I mean?

    Thanks,

    S
  • 13. Re: calculating liquidation - percentages with subqueries - Urgent!
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    998145 wrote:
    Hi,

    It's basically that they 2 queries are correct in what they are pulling back, I now want to take it one step further and use the results of both queries to work out a percentage liquidation. Is there any way I can attach an excel document to this thread to show you what I mean?
    Sorry, I don't know of any way to attach files. I'm not sure that would help, anyway.

    Why can't you post CREATE TABLE and INSERT statements right on this site? If the 2 queries you have now are working fine, then post 2 CREATE TABLE statements for tables that look just like their result sets, and a few INSERT statements to populate those tables.
    Don't forget to post the results you want from the given data.

Legend

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