How to get day differences and fiscal date ranges by providing start date and end dates

Question

0.00/5 (No votes)

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 2-May-19 0:57am

iAnkitBhatt

Updated 7-May-19 20:49pm

v5

Add a Solution

Comments

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.

iAnkitBhatt 3-May-19 5:42am

One more try given but somehow not able to generate date range properly: Day difference is correct.

SQL QUERY:
DECLARE
@MinDate DATE = '2015-12-12',
@MaxDate DATE = '2018-12-12';

SELECT PT.* from (
SELECT FT.*,DATEADD(DAY, DayDifference-1, FT.StartDate) as EndDate from
(SELECT FiscalYear,Count(UT.dt) as DayDifference ,@MinDate as StartDate
from (select st.dt,year(dateadd(month, -3, st.dt)) as fiscalyear
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 fiscalyear) as FT ) PT

OUTPUT:
FiscalYear DayDifference StartDate EndDate
2015 111 2015-12-12 2016-03-31
2016 365 2015-12-12 2016-12-10
2017 365 2015-12-12 2016-12-10
2018 256 2015-12-12 2016-08-23

[no name] 3-May-19 9:34am

2. the date range will be classified as per Indian financial years (April to march)

The simple fact is, that virtually every financial system uses a "table" to manage their "reporting periods / months" for every fiscal year ... Because another function of this "period table" is to control whether or not a period is "open" or "closed"; i.e. can be "posted" to.

So, you're attempts to "back calculate" the reporting periods would be considered an "unacceptable" accounting practice.

You should be able to take a date, match it to the period table, and retrieve the "period" that it applies to (open/closed; fiscal year; quarter; etc.)

iAnkitBhatt 5-May-19 23:42pm

Gerry thanks for responding.

yes i am calculating however the period is open or closed for every fiscal year because.

in my application, there is a product table with different Invoice date and its age define audit report to government about depreciation.

and my requirement is not only between fiscal year period. sometime we need to calculate depreciation based on date of invoice or date of use which may be middle of the year. and as it seems like to calculate the straight line depreciation calculation i need to change product values next time/Fiscal year. the same can be possible at end date(Age) which may be middle of the year. so simply i need date ranges and day differences to calculate it.

and there is restriction to define any table for fiscal date range and the above solution query will be join of other query once i achieve this

but whatever you provide is correct logic flow ,if in case we want to manage a series table inside database server.

2 solutions

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

Posted 2-May-19 23:42pm

#realJSOP

Updated 2-May-19 23:45pm

v2

Add a Solution

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

iAnkitBhatt · Accepted Answer · 2019-05-07T20:42:00

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

How to get day differences and fiscal date ranges by providing start date and end dates

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to get day differences and fiscal date ranges by providing start date and end dates

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us