Click here to Skip to main content
14,644,170 members
Rate this:
Please Sign up or sign in to vote.
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
Comments
Maciej Los 27-Aug-15 6:45am
   
What have you tried?
Rate this:
Please Sign up or sign in to vote.

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
Comments
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!
Rate this:
Please Sign up or sign in to vote.

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
            DATEFROMPARTS(YEAR(DATEADD(DD, 1,@enddate)), 12,31)=DATEFROMPARTS(YEAR(DATEADD(DD, 1,EndOfYear)), 12,31)
)
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
Comments
Wendelius 28-Aug-15 0:01am
   
Nice!
Maciej Los 28-Aug-15 3:05am
   
Thank you, Mika.

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




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