Solved

Oracle Fiscal Dates query

Posted on 2009-04-09
11
673 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PAYER_ID has both atributes 4 38
ORA-01008: not all variables bound. 6 57
grouping on time windows 6 51
ODBC in excel 2016 in Windows 10 via VBA 16 80
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup

773 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