Solved

Pull records based on current fiscal year

Posted on 2013-06-05
16
1,689 Views
Last Modified: 2015-09-22
Hello experts!

I have spent the past two solid days trying to do something that should be quite simple.
The real problem is me and my lack of knowledge of Oracles SQL Developer language. I just started using it a few days ago.

I did more than a few searches in the knowledge base and did not find an entire solution. So if this has been answered already somewhere, please by all means, let me know.
 I am quite sure this is a very common task.

I am working with Oracle SQL Developer 3.2
(which will be read by a Jasper iReport, being called upon thru a web server)

OK, so on to the task...
Fiscal Year starts October 1
So for example, if I run the report today the report would contain records with a Start_Date between Oct 1, 2012-Oct 1,2013 (fiscal year 2013)
If I run the report in November, it should pull records with a Start_Date between Oct 1, 2013 and Oct 1, 2014 (fiscal year 2014)

The date range always begins and ends on 10-1. Only the year is conditional.

I actually found what I want to do on here. The problem is, the santax doesnt work in Oracle SQL.
My date field is {p.PMP_START_DATE}

There is no dateserial function.
Does anyone know what would be comparable?

 Here is what I came up with and would work if the functions actually existed in Oracle SQL.

SELECT * FROM TABLES
WHERE
case when extract(month from sysdate) > 9
THEN {p.PMP_START_DATE} BETWEEN date(year(sysdate)- 1,10,01)  AND date(year(sysdate),10,01) else
P.PMP_START_DATE between date(year(sysdate),10,01)  and dateserial(year(sysdate)+ 1,10,01)
END;

I was thinking about creating a field that would contain the current fiscal year and calling upon that field but that just seems silly.

Thanks in advance.
0
Comment
Question by:ColB7533
  • 6
  • 4
  • 3
  • +2
16 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39222347
You're also able to use ROUND on date types, maybe that will help, look here:

http://www.techonthenet.com/oracle/functions/round_date.php
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39222361
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39222579
try something like this (untested)
select
*
from yourtable
where ( datefield >=     (case
                          when to_number(to_char(sysdate,'MM')) >= 10 
                                then add_months(trunc(sysdate,'YYYY'),9)
                                else add_months(trunc(sysdate,'YYYY'),-3)
                         end)
       and datefield <  (case
                          when to_number(to_char(sysdate,'MM')) >= 10 
                                then add_months(trunc(sysdate,'YYYY'),21)
                                else add_months(trunc(sysdate,'YYYY'),9)
                         end)
       )

Open in new window

{+ edit for got some = in the above, and again a wrong number}
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39222873
Oracle can do arithmetic with date values, so one possibility is to do something like PortletPaul suggested (but did not explain).  This date arithmetic can be simply adding a number of days to a date (for example: p.PMP_START_DATE + 90) or you can use an Oracle function like "add_months" to add a specific number of months to a date value.

But, in both of those cases you still end up with a date, which in Oracle's way of thinking includes: year, month and day (these can be displayed in any order you like) plus optionally the time of day: hours, minutes and seconds.

It may seem easier to you to work with a "fiscal year" than with Oracle date values and Oracle does offer operators to extract any portion of a "date" (century, year, quarter, month, day, day of week, hour, minute, second, etc.) but keep in mind that when you use operators like this to extract a portion of a date, if you do this in the "where" clause of you query, you prevent Oracle from using any indexes on that date column and you force Oracle to read every record in the table to determine if that record meets your query criteria, or not.  This can make queries painfully slow!

When working with dates in Oracle, I usually find it best to use a "between" comparison in the query, something like this:
where p.PMP_START_DATE between [min_value] and [max_value]
This kind of query allows Oracle to use existing indexes on the p.PMP_START_DATE column.  Depending on the data volumes, and on whether this column is indexed or not, this can make a huge difference in performance compared to something like this syntax:
where extract(PMP_START_DATE,'year') = [year_value]

I realize that I haven't directly answered your question, but I tried to help a bit and indicate some other things you should be aware of when working with dates in Oracle databases.
0
 

Author Comment

by:ColB7533
ID: 39223079
@markgeer Thank you for all your input! Like I stated, I am brand new to Oracle SQL and I welcome all the input I can get!

Your point of allowing Oracle to utilize the index in the query is a very good point.
I will defiantly use the BETWEEN function for my Date ranges. After all...making the query more efficient is the whole point in putting the WHERE clause in the SQL in the first place!

It helps to know those little nuances between languages. Like Java and their lack of useable date functions.
0
 

Author Comment

by:ColB7533
ID: 39223511
ok @PortletPaul I finally figured out what you were trying to do.
It took me a minute to catch up but once I figured it out, I just had to make some adjustments and now works perfectly!

Thank you!!

where ( p.pmp_start_date between     (case
                          when to_number(to_char(sysdate,'MM')) > 9
                                then trunc(sysdate,'Q')   --if the current month is October-December then it begins pulling records on October 1st of the same year.
                                else add_months(trunc(sysdate,'YYYY'),-3)  --if the current month is Jan-Sept then it begins pulling records on October 1st of the prior year.
                         end)
  and                                (case
                          when to_number(to_char(sysdate,'MM')) > 9  
                                then add_months(trunc(sysdate,'YYYY'),12) --if the current month is October-December then it stops pulling records on October 1st of the next year.
                                else trunc(sysdate,'Q') --if the current month is Jan-Sept then it stops pulling records on October 1st of the same year.
                         end)
       );

Open in new window

0
 

Author Comment

by:ColB7533
ID: 39223525
Can anyone suggest the best way to create this as a FUNCTION?

I can create a FUNCTION that returns only the START DATE or the END DATE but I do not know how I would create a function that would return a date range?
Is that even possible??

Thanks!
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39223589
Functions by defintion return a single value.  A "value" though could be a text string, and this text string could (possibly) be interpreted as (or converted to) a portion of a SQL statement.  Is this the best way to solve the problem though?
0
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.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39224268
>>When working with dates in Oracle, I usually find it best to use a "between" comparison in the query
I absolutely disagree about the use of between I'm afraid, and urge you to not adopt use of that.

Please see "Beware of Between" for reasons why.

Using between leads to complexities that are completely avoidable. In many cases I'm afraid to say it also leads to error (either by permitting overlaps, or more commonly forcing users to adjust the criteria but in so doing creating gaps).

The most reliable method for filtering dates is through a combination of >= with <
======

Your question asked for an automated way to select date based information for a fiscal year (starting in the 10th month). So for sysdates in months 10,11,12 the year of the FY starting date is unaffected, for all other months the year much be reduced by one.

The same logic occurs for the ending date, if the sysdate is in months 10,11,12 the ending date of the FY is in today's year + 1, otherwise the ending date year is the current year.

Having established year, then the add_months() function is used to either add or deduct months to the relevant year.

I have to admit I was too tired when I answered so I put in what I could and omitted any attempt at explanation. Markgeer did a great job on that - thanks (except we disagree on use of between).

Like markgeer, the direction of the proposed calculations is to ensure you maximize indexes in the query by avoiding calculations on the data. Make the filtering criteria suit that data - don't adjust the data to suit filtering criteria.

& Please don't use between :)

{apologies, an edit was needed for clarity}
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39224284
by the way, nice job on fixing my code :)
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39224347
ooops I see you did adopt between.... drat

'between' is translated by the optimiser into higher order operators for the actual calculations, when it translates it it uses >= and <=

let's examine this with a simple example

select *
from table1
where datef between to_date('2012-10-01') and to_date('2013-10-01')

the optimizer converts this into:

select *
from table1
where datef >= 2012-10-01 and datef <= 2013-10-01

so now 2013-01-01 00:00:00 + 00000 is INCLUDED, which isn't correct

====
avoid these sorts of problems by avoiding use of between for date range selection

select *
from table1
where datef >= to_date('2012-10-01') and  datef < to_date('2013-10-01')

is precise, is concise, is easy to read, and needs no complex explanation
it will not cause a gap or an overlap
and this syntax is adaptable to any situation where you need to filter for a date range.

please try this (untested again):
where ( p.pmp_start_date >=     (case
                          when to_number(to_char(sysdate,'MM')) > 9
                                then trunc(sysdate,'Q')   --if the current month is October-December then it begins pulling records on October 1st of the same year.
                                else add_months(trunc(sysdate,'YYYY'),-3)  --if the current month is Jan-Sept then it begins pulling records on October 1st of the prior year.
                         end)
  and p.pmp_start_date <       (case
                          when to_number(to_char(sysdate,'MM')) > 9  
                                then add_months(trunc(sysdate,'YYYY'),12) --if the current month is October-December then it stops pulling records on October 1st of the next year.
                                else trunc(sysdate,'Q') --if the current month is Jan-Sept then it stops pulling records on October 1st of the same year.
                         end)
       );

Open in new window

0
 

Author Closing Comment

by:ColB7533
ID: 39226304
Correct Code and explained throughly.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 39226526
To PortletPaul:
OK, I read that article:  "Beware of Between".  There was nothing in there that I wasn't aware of.  Yes, the "between" operator in Oracle is inclusive of both the min and max boundary values.  And, yes with "date" columns in particular (because of the optional time component of Oracle "date" values) this can add some complexity.  To me, this is just a matter of preference: either use ">=" plus "<=" or "between".  They will both return the same rows if they are carefully written.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39227766
@markgeer
I don't expect to win over everyone, for those who understand the full details of between and the data precision they are analyzing, then of course between can be made useful - but:
many people simply do not understand the inclusive nature of between, or, if they do, they compensate in one of 2 ways
a. by attempting to reduce the high point by some arbitrary time unit (a possible gap), or
b. amend the data precision to suit the criteria (affecting performance)

the alternative route is to stop using between, and these issues fall away.
to me, the choice is very clear

btw: I have witnessed many questions here already where something like this has been used [ between 2012-01-01 and 2012-12-31 ] where it is clear the asker believes somehow that between magically adds "to the end of the day" on the high point.
There is a lot of misunderstanding on this sql feature out there.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39227770
@ColB7533
thanks! and thank you for tolerating my rants on between

and @markgeer
thank you for also for tolerating the same rants
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40989050
ColB7533 - I just kicked out an article that is the SQL Server answer to this question, deals specifically with Fiscal calendar planning --> SQL Server Calendar Table:  Fiscal Years.   Let me know if this helps you, and if yes please click on the 'Good Article' button and provide some feedback.  Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now