Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 702
  • Last Modified:

Oracle Fiscal Dates query

I have a table which has a field called Total_amount, that table consists of other fields as well, like Vendor Id and Invoice Date. I would like to get totals for all Invoices, grouped by vendor, for each month. I would the following columns to be populated. (I would be asking the user to input the yearmonth for which he/she wants to query)
1. Totals for the Input Year/Month
2. Last years totals for the same Month
3. Fiscal Total (i.e October 2008 thru Input YYMM)
4. Last Year Fiscal Total (i,e October 2007 through 08MM)
October is the starting month of the Fical period.

Pl help
0
rexyphilips
Asked:
rexyphilips
  • 5
  • 3
  • 2
  • +1
1 Solution
 
twocandlesCommented:
Consider using oracle analytic functions. Could you post your table definition?
0
 
rexyphilipsAuthor Commented:
Hi Two candles,
I want to group by Vendor for those buckets defined above

Payable (Table)

Voucher_ID (Primary Key)
Vendor_ID
Total_amount
Invoice_date
0
 
awking00Commented:
Could you perhaps provide some sample data for the payable table that I could test with?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
1) Totals for the Input Year/Month

for this , try the below :

select vendor_id, sum(total_amount)
from payable_table
where to_char(invoice_date,'YYYYMM') = '&enter_in_YYYYMM'
group by vendor_id;

2) Last years totals for the same Month

for this , try the below :

select vendor_id, sum(total_amount)
from payable_table
where to_char(invoice_date,'YYYYMM') =
to_char(
add_months(
to_date('&enter_in_YYYYMM'||'01','YYYYMMDD')
,-12),'YYYYMM')
group by vendor_id;

3) & 4). Fiscal Totals

for this , try the below :

note: a) for example, the below query will ask input two times but we need to
         give the same input value for both the times.
      b) if you give the input as 200904, then the query will get
         data from 200810 to 200904. Basically, from last year october till
         the given input month in general.
      c) if you give the input as 200806, then the query will get
         data from 200710 to 200806.
      d) this query will get at vendor_id level totals.

select vendor_id, sum(total_amount)
from payable_table
where to_char(invoice_date,'YYYYMM') >=
to_char(
add_months(
trunc(to_date('&enter_in_YYYYMM'||'01','YYYYMMDD'),'YYYY')
,-3),'YYYYMM')
and to_char(invoice_date,'YYYYMM') <= '&enter_in_YYYYMM'
group by vendor_id;

note: a) for example, the below query will ask input two times but we need to
         give the same input value for both the times.
      b) if you give the input as 200904, then the query will get
         data from 200810 to 200904. Basically, from last year october till
         the given input month in general.
      c) if you give the input as 200806, then the query will get
         data from 200710 to 200806.
      d) this query will get at vendor_id, YYYYMM level totals.

select vendor_id, to_char(invoice_date,'YYYYMM'),
sum(total_amount)
from payable_table
where to_char(invoice_date,'YYYYMM') >=
to_char(
add_months(
trunc(to_date('&enter_in_YYYYMM'||'01','YYYYMMDD'),'YYYY')
,-3),'YYYYMM')
and to_char(invoice_date,'YYYYMM') <= '&enter_in_YYYYMM'
group by vendor_id,to_char(invoice_date,'YYYYMM');

Thanks
0
 
rexyphilipsAuthor Commented:
Great Solution
0
 
awking00Commented:
Out of curiosity, can you explain how this gets the sum from the prior October?
where to_char(invoice_date,'YYYYMM') >=
to_char(
add_months(
trunc(to_date('&enter_in_YYYYMM'||'01','YYYYMMDD'),'YYYY')
,-3),'YYYYMM')
and to_char(invoice_date,'YYYYMM') <= '&enter_in_YYYYMM'
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
converting the YYYYMM into to_date by padding DD with it and then truncating it to year will get us the 01-jan-YY of the YYYYMM which we entered and then add_months (..,-3) will get us to 01-oct-previous year and then again converting back to YYYYMM and ensuring that it is between last october and entered YYYYMM
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i think one another way would be to use Q flag of to_date(..) and manipulate accordingly.
0
 
awking00Commented:
nav_kum_v, see attached.
comments.txt
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
for only fiscal queries, i assumed that entered YYYYMM will be from Jan to Sep only because the query will automatically go back to previous october and as September is the last month in a fiscal year for any year .

In your explanation, insetad of 200812 you need to enter 200809 as September is last month in a fiscal year for any year ( only for the fiscal queries ).

or if you think user can enter 200812 but it should get again results only till September 08 from October 07 by excluding from October 08 ( of the entered year ).... then we can just amend the where clause to validate the same instead of saying

"and to_char(invoice_date,'YYYYMM') <= '&enter_in_YYYYMM'    "

by saying it to as shown below :

" and to_char(invoice_date,'YYYYMM') <= '&enter_in_YYYYMM'  
AND to_char(invoice_date,'YYYYMM') <=
to_char(
add_months(
trunc(to_date('&enter_in_YYYYMM'||'01','YYYYMMDD'),'YYYY')
,8),'YYYYMM') <= '&enter_in_YYYYMM'  
  "
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
typo in the changed where clause....

"
and to_char(invoice_date,'YYYYMM') <= '&enter_in_YYYYMM'  
AND to_char(invoice_date,'YYYYMM') <=
to_char(
add_months(
trunc(to_date('&enter_in_YYYYMM'||'01','YYYYMMDD'),'YYYY')
,8),'YYYYMM')
 "
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now