calculate duration between two dates in Financial Year

Question

3.00/5 (2 votes)

See more:

SQL-Server-2012

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 27-Aug-15 0:27am

kirthiga S

Updated 27-Aug-15 1:44am

v2

Add a Solution

Comments

Maciej Los 27-Aug-15 6:45am

What have you tried?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2015-08-27T00:45:00

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

To be able to calculate it for each year in date range, you have to use CTE[^]:

SQL

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[^]

Herman<T>.Instance · Answer 2 · 2015-08-27T00:53:00

Well.....Original found here[^]
and with a bit playing:

SQL

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:

SQL

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]

calculate duration between two dates in Financial Year

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0