Dynamically calculate end of fiscal year(s) and weeks to that date

Question

I'm looking to calculate how many weeks an employee will have worked if they started mid year until the end of the current fiscal year (1st April - 31st March).

For example, an employee started working at the company on 01/10/2017 (UK date) I need to calculate the number of weeks they will have worked until 31/03/2018 (inclusive).

The field for the employee start date is 'START_DATE' from table 'Employee'. I also have a calendar table with every date format you could imagine and also includes fiscal year.

I found this question but it doesn't quite solve my problem: Calculate totals of field based on current fiscal year only - SQL

Any help much appreciated.

Tanner Tanner · Accepted Answer · 2017-10-17T09:59:16

It does depend on how you classify what a week is. Does it have to be a full week? Does starting on a day that is not a Monday mean that it's not counted as a full week if they finish on a day that is not Friday? This is where you have to identify your business logic.

Here are some fundamental DATEDIFF operations that you can use to work out differences between two dates, which you can use as a basis for your calculations:

DECLARE @startDate DATE = '2017-10-01'
DECLARE @endDate DATE = '2018-03-31'

SELECT @startDate StartDate, @endDate EndDate, 
        DATEDIFF(DAY, @startDate, @endDate) DaysPassed,
        DATEDIFF(WEEK, @startDate, @endDate) WeeksWorked,
        DATEDIFF(DAY, @startDate, @endDate) / 7.0 CalculatedWeeksWorked

Produces:

StartDate  EndDate    DaysPassed  WeeksWorked CalculatedWeeksWorked
---------- ---------- ----------- ----------- ---------------------
2017-10-01 2018-03-31 181         25          25.857142

Also, you may want to consider the number of days worked excluding weekends to work out how many full weeks are worked, if so, have a look at this post:

get DATEDIFF excluding weekends using sql server

Fiscal Year

To work out the fiscal year, you should be able to simply look at the month value of the date like so:

DECLARE @startDate DATE = '2017-10-01';

-- if the month is greater than 3, add a year, else take the current year
SELECT CASE
           WHEN DATEPART(MONTH, @startDate) > 3 THEN
               CAST(DATEPART(YEAR, @startDate) + 1 AS VARCHAR(10)) + '-03-31'
           ELSE
               CAST(DATEPART(YEAR, @startDate) AS VARCHAR(10)) + '-03-31'
       END AS fiscalYearEnd;

Edit the @startDate and test the above, it should hopefully work for most cases. I've given it a quick test and it seems to return the expected result.

Dynamically calculate end of fiscal year(s) and weeks to that date

2 Answers