Function to calculate fiscal days between two dates

Question

Post reply

Function to calculate fiscal days between two dates

murthyvs

SSCommitted

Points: 1799
More actions
May 10, 2011 at 8:46 am

#250039

Hello all,
I am a newbie and struck with this problem at hand.
The fiscal year starts on 10/1 and ends on 09/30 of every year.
I want to calculate the number of fiscal days in a given/supplied fiscal year between two dates that span multiple fiscal years.
For example: If 06/01/2007 is the start date and 10/04/2010 is the end date then there are 1222 days between these two dates.
If we give fiscal_days(2007, 06/01/2007,10/04/2010) then the function should return 122 days
fiscal_days(2008, 06/01/2007,10/04/2010) then the function should return 366 days
fiscal_days(2009, 06/01/2007,10/04/2010) then the function should return 365 days
fiscal_days(2010, 06/01/2007,10/04/2010) then the function should return 365 days
fiscal_days(2011, 06/01/2007,10/04/2010) then the function should return 4 days
122+366+365+365+4 = 1222
Calculation is as follows:
6/1/200710/1/200710/1/200810/1/200910/1/2010
9/30/20079/30/20089/30/20099/30/201010/4/2010
122 366 365 365 4
Thanks for your inputs!
Regards

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The easiest solution would be a calendar table with (at least) the date and the corresponding fiscal year.

The query then simply would be

SELECT COUNT(*)

FROM dbo.calendar

WHERE fiscal_yr=2010

AND date >= '20100901'

AND date < '20101101'

As a sid note: you might need to adjust the >= or < part depending on your expected result (e.g. what would you expect for fiscal_days(2011, 09/29/2011,09/30/2011): 1 or 2 days?)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

murthyvs SSCommitted Points: 1799 More actions · Answer 2

Thanks LutsM!

The start date, end date are two fields in the same table.

Lets say sku, start_date, end_date are in table t1.

I am looking for something like:

select a.*, dbo.fiscal_days(2006, a.start_date, a.end_date) as fiscal_days_2006

, dbo.fiscal_days(2007, a.start_date, a.end_date) as fiscal_days_2007

, dbo.fiscal_days(2008, a.start_date, a.end_date) as fiscal_days_2008

, dbo.fiscal_days(2009, a.start_date, a.end_date) as fiscal_days_2009

, dbo.fiscal_days(2010, a.start_date, a.end_date) as fiscal_days_2010

, dbo.fiscal_days(2011, a.start_date, a.end_date) as fiscal_days_2011

, dbo.fiscal_days(2012, a.start_date, a.end_date) as fiscal_days_2012

from t1 as a

LutzM (5/10/2011)
The easiest solution would be a calendar table with (at least) the date and the corresponding fiscal year.
The query then simply would be
SELECT COUNT(*)
FROM dbo.calendar
WHERE fiscal_yr=2010
AND date >= '20100901'
AND date < '20101101'
As a sid note: you might need to adjust the >= or < part depending on your expected result (e.g. what would you expect for fiscal_days(2011, 09/29/2011,09/30/2011): 1 or 2 days?)

Lowell SSC Guru Points: 323518 More actions · Answer 3

with MySampleData

AS (

SELECT

'06/01/2007' as StartDate,

'10/04/2010' as EndDate

)

SELECT *,

datediff(dd,StartDate,EndDate)

FROM MySampleData

ok that returns 1221 days...but lets talk about your function:

SELECT

dbo.fiscal_days(2007, '06/01/2007','10/04/2010') ,

--then the function should return 366 days

WHY would that return 366 days? why not the # days from 01/01/2007 to '06/01/2007', or from '06/01/2007' to 12/31/2007 (~180 days or so)?

I don't seem to grasp what the function is supposed to do?

if it's just counting days in a year?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

murthyvs SSCommitted Points: 1799 More actions · Answer 4

Thanks for your reply. Sorry for not being clear.

The function is supposed to calculate the fiscal days in a fiscal year (fiscal year is supplied). The fiscal year starts on 10/01 and ends on 09/30 of any given year.

Ex: 2007 fiscal year = 10/01/2006 - 09/30/2007

2008 fiscal year = 10/01/2007 - 09/30/2008

2009 fiscal year = 10/01/2008 - 09/30/2009

etc.

In the example: dbo.fiscal_days(2007, '06/01/2007','10/04/2010') it would return 122 days and not 366 days.

2007 is the supplied fiscal year (2007 fiscal year = 10/01/2006 - 09/30/2007)

'06/01/2007' - start date

'10/04/2010' - end date

A portion of '06/01/2007' - '10/04/2010' falls in between '10/01/2006' - '09/30/2007' which is '06/01/2007' - '09/30/2007'.

datediff(d, '06/01/2007' , '09/30/2007') + 1 = 122 days

Hope this helps.

Thanks

Lowell (5/10/2011)
with MySampleData AS ( SELECT '06/01/2007' as StartDate, '10/04/2010' as EndDate ) SELECT *, datediff(dd,StartDate,EndDate) FROM MySampleData
ok that returns 1221 days...but lets talk about your function:
SELECT
dbo.fiscal_days(2007, '06/01/2007','10/04/2010') ,
--then the function should return 366 days
WHY would that return 366 days? why not the # days from 01/01/2007 to '06/01/2007', or from '06/01/2007' to 12/31/2007 (~180 days or so)?
I don't seem to grasp what the function is supposed to do?
if it's just counting days in a year?

Lowell SSC Guru Points: 323518 More actions · Answer 5

ok in theory, this is pretty close. I didn't test too many examples, so its up to you to improve it and test the edge cases, but it seems to work:

CREATE Function fiscal_days(@iYear int,@StartDate datetime,@EndDate DateTime)

returns INT

AS

BEGIN

DECLARE @Days int,

@FiscalStart datetime,

@FiscalEnd datetime

SELECT @FiscalStart = CONVERT(datetime,CONVERT(varchar,@iYear -1) + '-10-01')

SELECT @FiscalEnd = CONVERT(datetime,CONVERT(varchar,@iYear) + '-09-30')

SELECT @Days =

CASE

WHEN @StartDate BETWEEN @FiscalStart AND @FiscalEnd

THEN DATEDIFF(dd,@StartDate,@FiscalEnd)

WHEN @EndDate BETWEEN @FiscalStart AND @FiscalEnd

THEN DATEDIFF(dd,@FiscalStart,@EndDate)

WHEN @StartDate < @FiscalStart AND @EndDate > @FiscalEnd

THEN DATEDIFF(dd,@FiscalStart,@FiscalEnd)

WHEN @StartDate > @FiscalEnd

THEN 0

ELSE DATEDIFF(dd,@FiscalStart,@FiscalEnd)

END

Return @Days + 1--why an extra day? i dunno?

END--PROC

GO

SELECT

dbo.fiscal_days(2007, '06/01/2007','10/04/2010') ,--then the function should return 366 days

dbo.fiscal_days(2008, '06/01/2007','10/04/2010') ,--then the function should return 366 days

dbo.fiscal_days(2009, '06/01/2007','10/04/2010') ,--then the function should return 365 days

dbo.fiscal_days(2010, '06/01/2007','10/04/2010') ,-- then the function should return 365 days

dbo.fiscal_days(2011, '06/01/2007','10/04/2010') --then the function should return 4 days

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

murthyvs SSCommitted Points: 1799 More actions · Answer 6

Thanks Lowell. Pretty intuitive code. It worked on the example I sent. I will test it and tweak it if needed.

Thanks again! Much appreciated.

BTW, select DATEDIFF(d, '20110101', '20110103') returns 2, I wanted the end days included thats why +1.