Help on query for fiscal year
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help on query for fiscal year

  1. #1
    Join Date
    May 2003
    Location
    NY
    Posts
    24

    Help on query for fiscal year

    I have a table like below and data like below

    SQL> DESC A
    Name Null? Type
    ---------- -------- ----
    ID NOT NULL NUMBER(10)
    PAY_DATE DATE
    AMOUNT NUMBER(11,2)
    SFY VARCHAR2(20)

    SQL> SELECT * FROM A;

    ID PAYDATE AMOUNT SFY
    --- -------- ------- -------
    1 10-DEC-99 2000 1999/00
    1 10-MAR-99 3000 1999/00
    2 10-SEP-99 2000 1999/00
    2 10-MAR-99 3000 1999/00
    3 10-DEC-00 2000 2000/01
    3 10-MAR-99 3000 1999/00
    4 10-JAN-00 2000 2000/01
    4 10-MAR-99 3000 1999/00
    5 10-DEC-00 2000 2000/01
    5 10-MAR-99 3000 1999/00
    6 10-JAN-00 2000 2000/01
    6 10-MAR-99 3000 1999/00

    WHAT I NEED TO GET IS BASED ON FISCAL YEAR(1999-2000) I NEED TO GET THE AMOUNT PAID
    FOR JUN,SEP,DEC,MARCH

    SO I NEED A QUERY LIKE BELOW

    1)SUPPOSE IF USER NEEDS REPORT BASED ON 1999-2000
    THEN I NEED IS

    ID JUN1999 SEP1999 DEC1999 MARCH1999 TOTAL
    -- ------- ------ ------ ---------- -------
    1 0 0 2000 3000 5000
    2 0 2000 0 3000 5000
    3 0 0 0 3000 3000
    4 0 0 0 3000 3000
    5 0 0 0 3000 3000
    6 0 0 0 3000 3000

    CAN ANYBODY HELP FOR CODE

    like for 2000-2001 and so on

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    Is this what you need?

    Code:
    DECLARE
       v_fiscal_year_end       DATE := '30-JUN-2000';
    BEGIN
       SELECT * FROM A
          WHERE pay_date <= v_fiscal_year_end   AND
                pay_date >= ADD_MONTHS( v_fiscal_year_end, -12);
    END;
    /

  3. #3
    Join Date
    May 2003
    Location
    NY
    Posts
    24

    Fiscal year query

    what i need is like below

    I need the quarterly like this

    If it is for Fical year 1999-2000
    Then my
    1st quarter is March 1999-April 1999- May 1999
    2nd qarter is Jun 1999-July 1999- August 1999
    3rd Quarter is Sep 1999-OCT 1999 - Nov 1999
    4th QUarter is Dec 1999-Jan 2000 - Feb 2000

    Can you help how to do this

    So my query should come up like below

    If I use the below code
    It is coming as

    1st quarter is JAN 1999-FEB 1999- MARCH 1999
    2nd qarter is APRIL 1999-MAY 1999- JUN 1999
    3rd Quarter is JULY 1999-AUG 1999 - SEP 1999
    4th QUarter is OCT 1999-NOV 1999 - DEC 1999

    SELECT
    id,
    SUM(DECODE(TO_CHAR(paydate, 'QYYYY'), '1'||:SFY', amount, 0)) Jun1999,
    SUM(DECODE(TO_CHAR(paydate, 'QYYYY'), '2'||:SFY', amount, 0)) Sep1999,
    SUM(DECODE(TO_CHAR(paydate, 'QYYYY'), '3'||:SFY', amount, 0)) Dec1999,
    SUM(DECODE(TO_CHAR(paydate, 'QYYYY'), '4'||:SFY', amount, 0)) Mar1999,
    SUM(amount) total
    FROM
    a
    GROUP BY
    id

    ID MARC1999 JUN1999 SEP1999 DEC1999 TOTAL
    ---- ------- ------- ------- ------- ----------
    1 3000 0 0 2000 5000
    2 3000 0 2000 0 5000
    3 3000 0 0 0 3000
    4 3000 0 0 0 3000
    5 3000 0 0 0 3000
    6 3000 0 0 0 3000


    I NEED LIKE BELOW
    1st quarter is March 1999-April 1999- May 1999
    2nd qarter is Jun 1999-July 1999- August 1999
    3rd Quarter is Sep 1999-OCT 1999 - Nov 1999
    4th QUarter is Dec 1999-Jan 2000 - Feb 2000


    ID JUN1999 SEP1999 DEC1999 MAR2000 TOTAL
    ---- ------- ------- ------- ------- ----------
    1 3000 0 0 2000 5000
    2 3000 0 2000 0 5000
    3 3000 0 0 0 3000
    4 3000 0 0 0 3000
    5 3000 0 0 0 3000
    6 3000 0 0 0 3000


    ANY HELP IN CODE

Posting Permissions

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


Click Here to Expand Forum to Full Width