Last 4 fiscal years for a given date

Greetings,

   I have to come up with the last 4 fiscal years for a given date.
Can someone kindly help?
pvsbandiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave FordSoftware Developer / Database AdministratorCommented:
You say "come up with the last 4 fiscal years for a given date", but what exactly do you mean?

In detail, what are you trying to accomplish?
0
pvsbandiAuthor Commented:
I need it for the Where clause in my SQL query, to get the date range for the last 4 fiscal years (October 1st to September 30th each year), based on the given date.
0
Dave FordSoftware Developer / Database AdministratorCommented:
It would be tremendously helpful if you told us what you're trying to accomplish and included some details of your table layout and content.

You say you want to "get the date range for the last 4 fiscal years", but then you immediately detailed the date-range for those fiscal years.

I suppose if you're just trying to see if a certain date is within a given fiscal year, you'd just use:

WHERE someDate between '2014-10-01' and '2015-09-30'

Is that what you're asking?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

pvsbandiAuthor Commented:
OK. Let's assume, my input date is today.
Based on this date, the last 4 fiscal years are:
FY11: Oct 1, 2010 through Sep 30, 2011
FY12: Oct 1, 2011 through Sep 30, 2012
FY13: Oct 1, 2012 through Sep 30, 2013
FY14: Oct 1 2013 through Sep 30, 2014.

I want the dates like that bsed on the Input Date.
0
tliottaCommented:
So, the result of the query should be the set of those eight dates?

Tom
0
pvsbandiAuthor Commented:
Yes
0
tliottaCommented:
Then basically you'd select from a table of FiscalYearDates:
WHERE FiscalStartDate > (checkDate - 5 YEARS)
  AND FiscalEndDate < checkDate

Open in new window


Assume a FiscalYearDates table with these rows:
FiscalStartDate  FiscalEndDate
Oct 1, 2009 	Sep 30, 2010
Oct 1, 2010 	Sep 30, 2011
Oct 1, 2011 	Sep 30, 2012
Oct 1, 2012 	Sep 30, 2013
Oct 1, 2013 	Sep 30, 2014
Oct 1, 2014 	Sep 30, 2015

Open in new window

Assuming checkDate is 2014-10-03 as in your comment, then ( checkDate - 5 YEARS ) will be = 2009-10-03. The set of all FiscalStartDates greater than that is (Oct 1 2010, Oct 1 2011, Oct 1 2012, Oct 1 2013, Oct 1 2014). The set of all FiscalEndDates less than checkDate is (Sep 30 2010, Sep 30 2011, Sep 30 2012, Sep 30 2013, Sep 30 2014).

Now the AND connector puts those two together and gives this result set:
FiscalStartDate  FiscalEndDate
Oct 1, 2010 	Sep 30, 2011
Oct 1, 2011 	Sep 30, 2012
Oct 1, 2012 	Sep 30, 2013
Oct 1, 2013 	Sep 30, 2014

Open in new window

You might create a physical FiscalYearDates table or create a temporary one, perhaps as a CTE. It shouldn't really matter, though I'd think that a real table could be referenced in a few different queries.

Tom
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pvsbandiAuthor Commented:
Thank you! that's a good idea
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.