Solved

Oracle Fiscal Dates query

Posted on 2009-04-09
11
679 Views
Last Modified: 2013-12-19
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
Comment
Question by:rexyphilips
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 6

Expert Comment

by:twocandles
ID: 24106937
Consider using oracle analytic functions. Could you post your table definition?
0
 

Author Comment

by:rexyphilips
ID: 24106986
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
 
LVL 32

Expert Comment

by:awking00
ID: 24108319
Could you perhaps provide some sample data for the payable table that I could test with?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 24115767
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
 

Author Closing Comment

by:rexyphilips
ID: 31568504
Great Solution
0
 
LVL 32

Expert Comment

by:awking00
ID: 24117082
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24121219
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24121243
i think one another way would be to use Q flag of to_date(..) and manipulate accordingly.
0
 
LVL 32

Expert Comment

by:awking00
ID: 24122911
nav_kum_v, see attached.
comments.txt
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24124412
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24124415
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question