15,991,287 members
See more:
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>```

What I have tried:

i tried multiple codes but none of that completely relevant to achieve expected output.
Posted
Updated 7-May-19 20:49pm
v5
CHill60 2-May-19 8:09am
Your question is not really clear. Perhaps if you gave us some sample data to go on, and some real expected results? Also don't post code that isn't relevent
iAnkitBhatt 2-May-19 23:44pm
there is no data. i need the expected output to perform further operation based on "Expected output" from provided two dates. simplifying my question again.

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 willbe till End date only.
4. the day difference for each ranges generated.

future use:
5. further i can use that every day difference for financial application for Assets depreciation for each year
[no name] 2-May-19 16:10pm
Just calculate the "date differences" first. Then you can consider the other stuff since you seem to be over-extended.
iAnkitBhatt 2-May-19 23:43pm
i am aware about date difference function between dates .
but unable to generate series as per requirement(eg. shown in "Expected output") so that further can use that range for calculation.
[no name] 3-May-19 0:03am
Yes, you probably need an "asset number". And depreciation is typically calculated every month; and accumulated until the asset is depreciated; and depending on the asset type, there are different rates of depreciation. One usually stores depreciation in an Asset sub ledger which is subsequently posted to the General Ledger in order to generate financial statements. I usually use C# for that part.

Solution 1

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).

v2

Solution 2

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)
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```