-
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
-
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;
/
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|