计算财政年度两个日期之间的持续时间 [英] calculate duration between two dates in Financial Year

问题描述

嗨朋友



我有两个约会

例如：2007-10-01和2012-08-24



我的要求是我需要在这两个日期之间的每个财政年度的月数



（即）

财政年度

2007-2008 6

2008-2009 12

2009-2010 12

2010-2011 12

2011-2012 12

2012-2013 5



如何到达这个使用sql查询



亲爱的朋友，财政年度应该是4月到3月

（ie）2007-2008意味着2007-04-01至2008-03-31
2008-2009
指2008-04-01至2009-03-31

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

推荐答案

DateDiff [ ^ ]正是您要找的。


为了能够计算日期范围内的每一年，你必须使用 CTE [ ^ ]：

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

进一步信息，请参阅：

使用公用表格式 [ ^ ]

使用公用表表达式的递归查询 [ ^ ]

嗯.....原创找到在其中 [ ^ ]

并且有点玩：

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]

改进查询：

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]

[/EDIT]

这篇关于计算财政年度两个日期之间的持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！