14,643,993 members
Rate this:
See more:
Hi friends

Iam having two dates
Example : 2007-10-01 and 2012-08-24

My requirement is I need number of months in each financial year between these two dates

(ie)
Financial Year Months
2007-2008 6
2008-2009 12
2009-2010 12
2010-2011 12
2011-2012 12
2012-2013 5

How to arrive this using sql query

Dear friends financial year should be from April to March
(ie) for 2007-2008 means 2007-04-01 to 2008-03-31
for 2008-2009 means 2008-04-01 to 2009-03-31
Posted
Updated 27-Aug-15 1:44am
v2
Maciej Los 27-Aug-15 6:45am

What have you tried?

Rate this:

## Solution 1

DateDiff[^] is what you're looking for.

To be able to calculate it for each year in date range, you have to use CTE[^]:
```DECLARE @startdate DATE = '2007-10-01'
DECLARE @enddate DATE = '2012-08-24'

;WITH MIFY AS
(
--initial values
SELECT  @startdate AS StartDate, DATEFROMPARTS(YEAR(@startDate), 12,31) AS EndOfYear, DATEDIFF(MM, @startdate, DATEFROMPARTS(YEAR(@startDate), 12,31)) +1 AS Months
WHERE @enddate> DATEFROMPARTS(YEAR(@startdate), 12, 31)
--recursive part
UNION ALL
SELECT DATEADD(DD, 1,EndOfYear) AS StartDate, DATEFROMPARTS(YEAR(DATEADD(DD, 1,EndOfYear)), 12,31) AS EndOfYear, DATEDIFF(MM, DATEADD(DD, 1,EndOfYear), DATEFROMPARTS(YEAR(DATEADD(DD, 1,EndOfYear)), 12,31)) +1 AS Months
FROM MIFY
WHERE @enddate >DATEFROMPARTS(YEAR(DATEADD(DD, 1,EndOfYear)), 12,31)
UNION ALL
SELECT DATEADD(DD, 1,EndOfYear) AS StartDate, @enddate AS EndOfYear, DATEDIFF(MM, DATEADD(DD, 1,EndOfYear), @enddate) +1 AS Months
FROM MIFY
WHERE DATEADD(DD, 1,EndOfYear) = DATEFROMPARTS(YEAR(DATEADD(DD, 1,@enddate)), 1,1) AND
)
SELECT *
FROM MIFY```

Result:
```StartDate	EndOfYear	Months
2007-10-01	2007-12-31	3
2008-01-01	2008-12-31	12
2009-01-01	2009-12-31	12
2010-01-01	2010-12-31	12
2011-01-01	2011-12-31	12
2012-01-01	2012-08-24	8```

For further information, please see:
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]
v2
Wendelius 28-Aug-15 0:01am

Nice!
Maciej Los 28-Aug-15 3:05am

Thank you, Mika.
Rate this:

## Solution 2

Well.....Original found here[^]
and with a bit playing:
```declare @dtFrom date
declare @dtTo date

select @dtFrom = '2007-10-01'
,@dtTo   = '2012-08-24'

select year(dt) [Year],   count(*) as Months
from (select top(datediff(MONTH, @dtFrom, @dtTo)) dateadd(MONTH,  row_number() over (order by (select null)), @dtFrom) dt
from sys.columns) q
group by year(dt)
order by [Year]
```

[EDIT]
Improved query:
```select year(dt) [Year],   count(*) as Months
from (
select top(datediff(MONTH, @dtFrom, @dtTo)+1) dateadd(MONTH,  row_number() over (order by (select null)) -1, @dtFrom) dt
from sys.columns
) q
group by year(dt)
order by [Year]```

[/EDIT]
v3
Maciej Los 27-Aug-15 7:18am

If i'm not wrong, this returns wrong result for first period of time (2 months instead of 3).
[EDIT]
I made some corrections to return proper resultset.
BTW: Very clever solution. +4 for link!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 247 Richard Deeming 225 Richard MacCutchan 205 Gerry Schmitz 118 Pete O'Hanlon 115
 Pete O'Hanlon 30 Gerry Schmitz 10 Member 14952873 -16

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100