Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 689
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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