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
Solved

Last 4 fiscal years for a given date

Posted on 2014-10-02
8
218 Views
Last Modified: 2014-10-10
Greetings,

   I have to come up with the last 4 fiscal years for a given date.
Can someone kindly help?
0
Comment
Question by:pvsbandi
  • 4
  • 2
  • 2
8 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 40357380
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
 

Author Comment

by:pvsbandi
ID: 40359947
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
 
LVL 18

Expert Comment

by:daveslash
ID: 40359998
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:pvsbandi
ID: 40360044
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
 
LVL 27

Expert Comment

by:tliotta
ID: 40365016
So, the result of the query should be the set of those eight dates?

Tom
0
 

Author Comment

by:pvsbandi
ID: 40367478
Yes
0
 
LVL 27

Accepted Solution

by:
tliotta earned 500 total points
ID: 40372378
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
 

Author Closing Comment

by:pvsbandi
ID: 40373319
Thank you! that's a good idea
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DB2 join tables across schemas 5 574
Leveraging AD Groups in DB2 1 84
MS/SQL OpenQuery to linked DB2 Server error 3 128
Importing Android SQlite database data to db2 4 86
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 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