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
rexyphilipsAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Naveen KumarConnect With a Mentor Production 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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
awking00Commented:
Could you perhaps provide some sample data for the payable table that I could test with?
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
All Courses

From novice to tech pro — start learning today.