Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Handle Calculations Related to fiscal year and quarter

By Raymond Xie,

Fiscal Year

In reality we often are required to provide statistics based on fiscal year/quarter, for example, the fiscal year might start on Nov 1 every year, and the first fiscal quarter ends on Jan 31 next year, and so on.

To get the right data based on this new “year” and “quarter”, we need to redefine the start of year and quarter.

The basic idea is to calculate the years between the given date and the base datetime 1900-01-01 00:00:00.000, we can use DATEDIFF function to do that:

DATEDIFF(datepart , startdate , enddate)

From SQL online help:

datepart

Is the part of startdate and enddate that specifies the type of boundary crossed

startdate

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate

Enddate

                Same as startdate

We will also use DATEADD in the calculation

DATEADD(datepart, number, date)

From SQL online help:

datepart

Is the parameter that specifies which part of the date to add a number to.

number

Is the value used to increment datepart. The value must be an integer value that is known when the expression is parsed.

date

Is an expression that returns a valid date or a string in date format.

code step by step:

  1. Get the number of years between a given date and base datetime 1900-01-01 00:00:00.000
    SELECT DateDiff(yy,0,Getdate())

    This returns 114 for this year 2014

  2. For cases that Fiscal year starts at a different date instead of January 1, we need to add the difference in. For example, if the case that Fiscal year starts on November 1, that means the year starts two month earlier, we need to slightly modify the code to

         SELECT DateDiff(yy,0,DateAdd(mm,2,Getdate()))

If we run this query now (mid of the year), there is no difference than the previous query, but if we run this on October 31 and compare it with November 1, we will see the difference:

Declare @CurrentDate datetime

Set @CurrentDate = '2013-10-31'

SELECT DateDiff(yy,0,@CurrentDate)

Returns 113

SELECT DateDiff(yy,0,DateAdd(mm,2,@CurrentDate))

Returns 113

Now let's do the same check to a new date: 2013-11-01

Declare @CurrentDate datetime

Set @CurrentDate = '2013-11-01'

SELECT DateDiff(yy,0,@CurrentDate)

Still returns 113

SELECT DateDiff(yy,0,DateAdd(mm,2,@CurrentDate))

Returns 114

So now we have the right number of years gap, we convert the gap into the first date of the year:

SELECT DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)

This returns: 2014-01-01 00:00:00.000

Note we need to make the adjustment to the real Fiscal year, so it would be:

SELECT DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))

This returns: 2013-11-01 00:00:00.000

Note in the DateAdd second argument, it’s -2 if the Fiscal year starts on November 1, meaning 2013-11-01 is the Fiscal year 2014

Here is an example to use the new starting date in other query:

SELECT

     FYStart

     ,FYNextStart = DateAdd(yy,1,FYStart)

     ,FYPrevStart = DateAdd(yy,-1,FYStart)

FROM

     (SELECT FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) dt1

It returns:

FYStart                 FYNextStart             FYPrevStart

2013-11-01 00:00:00.000 2014-11-01 00:00:00.000 2012-11-01 00:00:00.000

Example of using it with other query:

SELECT * FROM dbo.Document  d

    CROSS JOIN (SELECT

                    FYStart

                    ,FYNextStart = DateAdd(yy,1,FYStart)

                    ,FYPrevStart = DateAdd(yy,-1,FYStart)

                FROM

                    (SELECT FYStart = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0)))dt1

                  ) fy

WHERE

d.DateFirstIssued > fy.FYStart and d.DateFirstIssued < fy.FYNextStart

This will return all records with DateFirstIssued within the current fiscal year

Fiscal quarters

Calculating fiscal quarter is very similar to calculating fiscal year, here is the code to get each of the four quarters’ starting date:

    SELECT

            FYQ1

            ,FYQ2 = DateAdd(mm, 3, FYQ1)

            ,FYQ3 = DateAdd(mm, 6, FYQ1)

            ,FYQ4 = DateAdd(mm, 9, FYQ1)

      FROM

(SELECT FYQ1 = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) dt1

It returns:

FYQ1                          FYQ2                       FYQ3                       FYQ4

2013-11-01 00:00:00.000       2014-02-01 00:00:00.000    2014-05-01 00:00:00.000    2014-08-01 00:00:00.000

Code example:

SELECT * FROM dbo.Document  d

    CROSS JOIN (    SELECT

            FYQ1

            ,FYQ2 = DateAdd(mm, 3, FYQ1)

            ,FYQ3 = DateAdd(mm, 6, FYQ1)

            ,FYQ4 = DateAdd(mm, 9, FYQ1)

      FROM

(SELECT FYQ1 = DateAdd(mm,-2,DateAdd(yy,DateDiff(yy,0,DateAdd(mm,2,Getdate())),0))) dt1) fy

WHERE

d.DateFirstIssued > fy.FYQ2 and d.DateFirstIssued < fy.FYQ3

This query lists all records with DateFirstIssued in Fiscal year second quarter, that is: between 2014-02-01 00:00:00.000 and 2014-05-01 00:00:00.000

This article’s credit should mostly be given to Lynn Pettis and Jeff Moden on sqlservercentral, I hope it is simple and useful to someone with the need.

Total article views: 5820 | Views in the last 30 days: 42
 
Related Articles
ARTICLE

Date Manipulation with DATEADD/DATEDIFF

Learn how to use Dateadd/Datediff functions to manipulate dates in this short article from Seth Phel...

FORUM

select 21/(datediff(dd,getdate(),getdate())

select 21/(datediff(dd,getdate(),getdate())

FORUM

DATEDIFF WEEKS!!!

DATEDIFF WEEKS !!!

FORUM

dateAdd inside where clause

dateAdd function syntax help

FORUM

DateAdd together with DateDiff

Hi, I would like to achieve the following: I need an update query wich would do the following....

Tags
fiscal quarter    
fiscal year    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones