Finding Financial year from set of two dates

[kbsudhir]

Hi All,

I have set of dates in a column & I want to find the number of financial year/s between the first & last date in that column :banghead:. The financial year starts from April & end in march next year.

Please guide me on how to get this done.

Regards
Sudhir

 

[recyan]

just check out if below gives some guidelines ( rather crude way ):

SELECT 
	a.MinOfTheDates, 
	a.MaxOfTheDates, 
	Month([MinOfTheDates]) AS TheMonthOfMinOfTheDatesDate, IIf([TheMonthOfMinOfTheDatesDate]<4,Year([MinOfTheDates])-1,Year([MinOfTheDates])) AS TheStartFinancialYear,
	DateSerial([TheStartFinancialYear],4,1) AS TheStartFinancialYearDate, 
	Month([MaxOfTheDates]) AS TheMonthOfMaxOfTheDatesDate,
	IIf([TheMonthOfMaxOfTheDatesDate]>=4,Year([MaxOfTheDates])+1,Year([MaxOfTheDates])) AS TheEndFinancialYear,
	DateSerial([TheEndFinancialYear],3,31) AS TheEndFinancialYearDate,
	DateDiff("yyyy",[TheStartFinancialYearDate],[TheEndFinancialYearDate]) AS NoOffYears
FROM 
	(
		SELECT 
			Min(myTable.TheDates) AS MinOfTheDates, 
			Max(myTable.TheDates) AS MaxOfTheDates
		FROM 
			myTable
	)
	AS a;

Alternatively, may be a function.

Thanks

 

[plog]

I vote function. Basically, you will use the DateDiff function(http://www.techonthenet.com/access/functions/date/datediff.php) to get total calendar years between the two dates and then add or subtract a year based on which months your starting and ending dates are to account for your offset financial year.

 

[kbsudhir]

Thanks guys.