Results 1 to 4 of 4

Thread: qryhelp

  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: qryhelp

    SELECT b.reffundnbr,
    a.beginbalance,
    a.netbalance,
    a.endbalance,a.createddate
    FROM fo_mmfclientbalance_d a, fo_mmffund_m b
    WHERE trunc(a.createddate) BETWEEN to_date('Nov 10, 2002','Mon dd, yyyy') AND to_date('Dec 09, 2002','Mon dd, yyyy')
    AND a.clientid= '10189'
    AND a.fundnbr = b.fundnbr


    clsREFFUNDNBR BEGINBALANCE NETBALANCE ENDBALANCE CREATEDDA
    -------------------- ------------ ---------- ---------- ---------
    521 0 100000 100000 08-DEC-02
    521 100000 100000 200000 09-DEC-02


    in the above qry i need the output as follows


    clsREFFUNDNBR BEGINBALANCE NETBALANCE ENDBALANCE CREATEDDA
    -------------------- ------------ ---------- ---------- ---------
    521 0 200000 200000 08-DEC-02


    DONOT USE min or max functions. with out using those functions i need first record begin balance and
    last record end balance and sum of all all balances as net balance for the reffundnbr= 521

  2. #2
    Join Date
    Dec 2002
    Posts
    11
    and rownum = 1 --on the where clause???

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: qryhelp

    Originally posted by vadlamanibujji
    SELECT b.reffundnbr,
    a.beginbalance,
    a.netbalance,
    a.endbalance,a.createddate
    FROM fo_mmfclientbalance_d a, fo_mmffund_m b
    WHERE trunc(a.createddate) BETWEEN to_date('Nov 10, 2002','Mon dd, yyyy') AND to_date('Dec 09, 2002','Mon dd, yyyy')
    AND a.clientid= '10189'
    AND a.fundnbr = b.fundnbr


    clsREFFUNDNBR BEGINBALANCE NETBALANCE ENDBALANCE CREATEDDA
    -------------------- ------------ ---------- ---------- ---------
    521 0 100000 100000 08-DEC-02
    521 100000 100000 200000 09-DEC-02


    in the above qry i need the output as follows


    clsREFFUNDNBR BEGINBALANCE NETBALANCE ENDBALANCE CREATEDDA
    -------------------- ------------ ---------- ---------- ---------
    521 0 200000 200000 08-DEC-02


    DONOT USE min or max functions. with out using those functions i need first record begin balance and
    last record end balance and sum of all all balances as net balance for the reffundnbr= 521
    Why do you say "DONOT USE min or max functions"???
    Is this some sort of puzzle, or do you just not like MAX and MIN? They are pretty universally accepted to be the way to get the smallest and largest values from a set of records - in this case the MIN(createddate) and MAX(createddate).

    Anyway, I think I have managed to do it with analytic functions and some jiggery-pokery with the dates:

    SELECT b.reffundnbr,
    SUM(firstbeginbalance) beginbalance,
    SUM(netbalance) netbalance,
    SUM(lastendbalance) endbalance,
    TO_DATE(TO_CHAR(SUM(firstcreateddate)),'YYYYMMDD') createddate
    FROM
    (
    SELECT b.reffundnbr,
    DECODE(ROW_NUMBER() OVER(ORDER BY a.createddate),1,a.beginbalance,0) firstbeginbalance,
    a.netbalance,
    DECODE(ROW_NUMBER() OVER(ORDER BY a.createddate),COUNT(*) OVER (), a.endbalance,0) lastendbalance,
    DECODE(ROW_NUMBER() OVER(ORDER BY a.createddate),1,TO_NUMBER(TO_CHAR(a.createddate,' YYYYMMDD')),0) firstcreateddate,
    FROM fo_mmfclientbalance_d a, fo_mmffund_m b
    WHERE trunc(a.createddate) BETWEEN to_date('Nov 10, 2002','Mon dd, yyyy') AND to_date('Dec 09, 2002','Mon dd, yyyy')
    AND a.clientid= '10189'
    AND a.fundnbr = b.fundnbr
    )
    GROUP BY b.reffundnbr;

    Enjoy!

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you are using either 8i or 9i then use the new analytic functions to get the first record begin balance (using FIRST_VALUE) and last record end balance (using LAST_VALUE) and sum of all records (use the ordinary sum) in the group by.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •