如何通过提供开始日期和结束日期来获取日差异和财务日期范围 [英] How to get day differences and fiscal date ranges by providing start date and end dates

问题描述

我陷入困境。

我需要帮助进行金融应用的日差计算。



1.两个日期作为输入。

2.日期范围将按照印度财政年度（4月至3月）分类

3.第一个范围将从开始日期开始。最后生成的范围仅为结束日期。

4.生成的每个范围的日差。



未来使用：

5.此外，我可以使用财务应用程序中有关准确资产折旧的所有差异。





输入

i am stuck at the point.
i need help for day difference calculation for financial application.

1. two dates as input.
2. the date range will be classified as per Indian financial years (April to march)
3. first range will start from start date. and last generated range will be till End date only.
4. the day difference for each ranges generated.

future use:
5. further i can use that every differences for financial application about accurate Assets depreciation.


Input

@StartDate = 2015-12-12
@EndDate  = 2018-12-12

预期产量：

Expected output :

srNo  startdate(A)    EndDate(B)   CalculatedDayDiff  
1.    2015-12-12  2016-03-31  ==> <Diffrence>
2.    2016-04-01  2017-03-31  ==> <Diffrence>
3.    2017-04-01  2018-03-31  ==>  <Diffrence>
4.    2018-04-01  2018-12-12  ==>  <Diffrence>

我尝试过：



i尝试了多个代码，但没有一个与实现预期输出完全相关。

What I have tried:

i tried multiple codes but none of that completely relevant to achieve expected output.

推荐答案

我写了一个提示/技巧，其特点是存储过程可以让你建立一个包含财政日期的日历。



构建没有预先存在的表的日历 [ ^ ]



在该代码中，会计年度从10月1日开始，所以财政抵消是3.所以如果您的财政开始是JUN 01，那么抵消将是8（我认为）。

I wrote a tip/trick that features a stored proc which lets you build a calendar that includes fiscal dates.

Build a Calendar Without Pre-Existing Tables[^]

In that code, the fiscal year starts on October 01, so the fiscal offset is 3. So if your fiscal start is JUN 01, the offset would be 8 (I think).

亲爱的所有感谢您的回复...

现在实现了可行的解决方案...

如果有任何优化或有效，请恢复只能通过选择查询来支付...





查询：

Dear All Thanks for your Responses...
Achieved Workable solution for now...
please revert if any optimization or effective is available by Select query only...


Query:

DECLARE
@MinDate DATE = '2015-12-12',
@MaxDate DATE = '2018-12-12';
SELECT FT.Row_Number,FT.Endfiscalyear-1 as StartFiscalYear,
FT.Endfiscalyear, FT.DayDifference,
CASE WHEN FT.Row_Number = 1 THEN DATEADD(DD,-FT.DayDifference+1,FT.EndDate) 
ELSE DATEADD(day,1,DATEADD(YEAR,-1,FT.EndDate ))
END  as startDate , CASE WHEN FT.Row_Number > 1 AND FT.DayDifference < 365 THEN  @MaxDate 
ELSE FT.EndDate END  as EndDate 
from (SELECT ROW_NUMBER() OVER(ORDER BY Endfiscalyear) AS Row_Number,
Endfiscalyear,Count(UT.dt) as DayDifference,
DATEFROMPARTS (Endfiscalyear-1,04,01) as StartDate,
DATEFROMPARTS (Endfiscalyear,03,31) as EndDate
from (select st.dt,year(dateadd(month, -3 + 12, st.dt)) as Endfiscalyear
from(SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate) as dt
FROM sys.all_objects a CROSS JOIN sys.all_objects b) as st) as UT
Group BY Endfiscalyear) as FT 

输出：

Output:

RN  SFY     EFY   DayDiff  stDate	  EndDate
1	2015	2016	111	2015-12-12	2016-03-31
2	2016	2017	365	2016-04-01	2017-03-31
3	2017	2018	365	2017-04-01	2018-03-31
4	2018	2019	256	2018-04-01	2018-12-12

这篇关于如何通过提供开始日期和结束日期来获取日差异和财务日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！