Calculate Fiscal Year in SQL Select Statement

Calculate fiscal year in SQL Server

I suggest you use a User-Defined Function based on the Fiscal year of your application.

CREATE FUNCTION dbo.fnc_FiscalYear(
    @AsOf           DATETIME
)
RETURNS INT
AS
BEGIN

    DECLARE @Answer     INT

    -- You define what you want here (September being your changeover month)
    IF ( MONTH(@AsOf) < 9 )
        SET @Answer = YEAR(@AsOf) - 1
    ELSE
        SET @Answer = YEAR(@AsOf)

    RETURN @Answer

END

GO

Use it like this:

SELECT dbo.fnc_FiscalYear('9/1/2009')

SELECT dbo.fnc_FiscalYear('8/31/2009')

Alternative ways to get records between a time frame in SQL (calculate fiscal year)

You don't actually need a calendar table for this. If the fiscal year starts on April 1, you can just subtract 3 months and extract the year:

SELECT v.FiscalYear, SUM(dpy.DIVIDENDPERSHARE),
       DATEFROMPARTS(v.FiscalYear, 4, 1) as StartFiscalYear,
       DATEFROMPARTS(v.FiscalYear, 3, 31) as EndFiscalYear
FROM TCS_DIVIDEND_PER_YEAR dpy CROSS APPLY
     (VALUES (YEAR(DATEADD(MONTH, -3, dpy.RECORD_DATE)))) v(FiscalYear)
GROUP BY v.fiscalYear;

This should be much faster than using a calendar table.

Calculate Fiscal |Year

Would this work? It says "if april and up, take the current year, else, take the previous year". For instance, April 2015 is fiscal year 2015, but March 2015 is fiscal year 2014.

case when month(timeofcall) >= 4
then year(timeofcall)
else year(timeofcall) - 1
end as fiscal

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

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.

(SQL Server) Trying to figure out a calculation for consecutive fiscal years from today's date

Here's a solution that's pretty similar to @Matt's, but which performed much faster in my testing. I saw your comments that this would be done on a table that contains a lot of data, so I created a table in my database called dbo.Donation and loaded it with 666,667 copies of your sample data set, incrementing the ID values with each copy, for a total of 4,000,002 records. Then I shifted the PostedDate back by a year for every record with ID > 200000, which effectively limits the number of matching donors to 66,667 (one-tenth of the number of copies of your data set). I don't know how closely this matches the actual number of donors you expect to see returned from this query; I just picked a number that seemed reasonable.

On my machine, the query returned the correct result set in about two seconds. So I'm hopeful that it will work for you. The comments in the query explain how it works.

By the way, I also saw your comment that your boss doesn't want a CTE, and I should point out that there's nothing about the CTEs that I'm using that makes them intrinsically slower than an alternate formulation. In fact, it's trivial to write an alternate formulation that uses subqueries in place of CTEs, and when I did so, I found that both queries produced exactly the same execution plan. The CTE version is just easier to read—and therefore easier to maintain—since you can interpret it from the top down rather than from the inside out. Let me know if you have any questions.

-- Compute the current fiscal year.
declare @ThisFiscalYear int = year(getdate()) + case when month(getdate()) <= 6 then 0 else 1 end;

-- This CTE computes the fiscal year of each donation and gets the set of unique fiscal years in
-- which each donor (ID) made a contribution.
with IDYearComboCTE as
(
    select distinct
        D.ID,
        FiscalYear = year(D.PostedDate) + case when month(D.PostedDate) <= 6 then 0 else 1 end
    from
        dbo.Donation D
),

-- The second CTE produces two values for each fiscal year: 
--
--     1. FiscalYearOffset is the number of fiscal years that lie between the current fiscal year
--        and the fiscal year in which the donation was posted, inclusive. So a donation from the
--        current fiscal year will have FiscalYearOffset = 1, one from the previous fiscal year
--        will have FiscalYearOffset = 2, and so on.
--
--     2. FiscalYearIndex is a reverse chronological ordering of the unique fiscal years for each
--        donor. In other words, the most recent donation for every donor, regardless of how long
--        ago it was, will have FiscalYearOffset = 1.
--
-- The important point to realize here is that FiscalYearOffset and FiscalYearIndex will be the
-- same for a given donation IF AND ONLY IF the donor has posted a donation in that year and in
-- every subsequent year up to and including the current fiscal year. If the donor has skipped a
-- year, then FiscalYearOffset will increase by more than one while FiscalYearIndex increases by
-- only one (because its values are always contiguous), and if the donor hasn't given in the
-- current fiscal year, then the record with FiscalYearIndex = 1 will have FiscalYearOffset > 1.
--
FiscalYearOrderingCTE as
(
    select
        C.ID,
        FiscalYearOffset = @ThisFiscalYear - C.FiscalYear + 1,
        FiscalYearIndex = row_number() over (partition by C.ID order by C.FiscalYear desc)
    from
        IDYearComboCTE C
    where
        C.FiscalYear <= @ThisFiscalYear
)

-- Since, as described above, records with FiscalYearOffset = FiscalYearIndex represent contiguous
-- years in which the same donor made a contribution, we select only those records, then limit the
-- result set to those IDs with two or more records (i.e. with donations in two or more contiguous 
-- fiscal years starting with the current fiscal year). 
select
    O.ID,
    ConsecYears = max(FiscalYearIndex)
from
    FiscalYearOrderingCTE O
where
    O.FiscalYearOffset = O.FiscalYearIndex
group by
    O.ID
having
    max(FiscalYearIndex) >= 2;

Edit: I will try to explain a little more thoroughly what the second CTE is doing, since that's where the magic happens.

First, be sure that you understand SQL Server's row_number() function. Because of the partition by C.ID clause, the row numbers generated by this function start over at 1 for each set of records that have the same ID. And because of the order by C.FiscalYear desc clause, we know that for any two records that have the same ID, the record whose fiscal year is later will have a smaller FiscalYearIndex value. Also note that the records we're operating on are those selected by the previous CTE, IDYearComboCTE, and because that query uses distinct, no two records with the same ID will also have the same fiscal year. Therefore, for any set of records that have the same ID, if you were to arrange them in decreasing chronological order by FiscalYear, you'd find that their FiscalYearIndex values form a sequence of contiguous integers that always begins with the value 1.

Second, consider the formula that I'm using to produce FiscalYearOffset: I'm just subtracting fiscal years from a constant which is equal to the latest fiscal year that we expect to occur in the result set^ǂ, and then adding 1. Some important observations here:

Because we are considering only donations that occurred in or prior to the current fiscal year, the lowest FiscalYearOffset value that can occur is 1, and it will occur precisely for donations that occurred in the current fiscal year.
Because no two records from IDYearComboCTE having the same ID will have the same FiscalYear (as discussed above), it is also true that no two records with the same ID will have the same FiscalYearOffset.
Because of point 2, and because a lower FiscalYear value will produce a higher FiscalYearOffset value, note that for any set of records with the same ID, if you were to arrange them in decreasing chronological order by FiscalYear, you'd find that their FiscalYearOffset values form a strictly increasing sequence. This is very similar to my observation on FiscalYearIndex above, with the important distinctions that the sequence of FiscalYearIndex values for a particular ID always starts with 1 and contains no gaps. Neither of those is true of a sequence of FiscalYearOffset values.

Now, suppose we have a record from IDYearComboCTE that represents the most recent donation from some particular donor. Since it's the most recent, we know that its FiscalYearIndex will be 1, because that's how row_number() works. We also know that its FiscalYearOffset will be 1 if and only if the donation occurred in the current fiscal year. If that most recent donation was from a previous fiscal year, then you can see by the formula for FiscalYearOffset that you'll get a value greater than 1. Therefore, for any donor's most recent donation, we know that FiscalYearOffset = FiscalYearIndex if and only if the donation was in the current fiscal year.

Next, consider a record from IDYearComboCTE that represents the second-most recent donation by some donor. Since it's the second-most recent, we know that its FiscalYearIndex will be 2, because again, that's how row_number() works. Crucially, we also know that its FiscalYearOffset value will be greater than or equal to 2. It cannot be 1 because this is the second-most recent donation year for the donor, and we said above that the same FiscalYear will not occur twice for the same ID in IDYearComboCTE. It will be 2 if the donation is from the previous fiscal year, and it will be some number greater than 2 if the donation is earlier than that.

Here's the critical piece: suppose that in that second-most recent donation record, FiscalYearOffset is greater than 2, meaning that it's greater than FiscalYearIndex. As we go to the third-most recent donation year, fourth-most recent, and so on, for that particular ID, FiscalYearOffset and FiscalYearIndex will never again be equal. This is because with each successive record, we know that the value of FiscalYearIndex will be one greater than the FiscalYearIndex of the previous record. Because of this, and because FiscalYearOffset > FiscalYearIndex for the second-most recent donation, in order for some further record's FiscalYearIndex to "catch up" to the FiscalYearOffset, FiscalYearOffset would have to increase by some number less than 1. But there is no positive integer less than 1, and we already showed above that the set of FiscalYearOffset values for a given ID is a strictly increasing sequence. For FiscalYearOffset to increase by less than 1 is thus impossible.

Therefore, the only records from the second CTE where FiscalYearOffset = FiscalYearIndex are those that represent donations in an unbroken sequence of years beginning with the current fiscal year and working backwards, and for a given ID, the number of donations in that unbroken sequence is equal to the number of records with that ID in the second CTE for which that equality holds.

This is a lot to take in. If it's still unclear, I'd recommend putting some sample data together and then changing the final select from my query to just select * from FiscalYearOrderingCTE so you can see the raw output from that second CTE.

^ǂ—While I was writing this edit, I realized that my original query failed to account for the fact that your database might contain future-dated donations, particularly donations occurring after the current fiscal year. This would break the query as originally written, so I've revised it by including where C.FiscalYear <= @CurrentFiscalYear in the second CTE.

SQL - How to determine fiscal quarter when quarters are determined by # of days?

Here is a solution which does an integer division to get the number of years after 2018. We then subtract 364 times this number from DATEDIFF the base year to get the number of days from the start of the current tax year. We can use your case statement to determine the quarter.

create function dbo.quarter (@date date)
returns varchar(100)
as
begin
declare @days int = datediff(dd,'2018-04-02',@date)
declare @years int = @days / 364
set @days  = @days - (@years * 364)
set @years = @years + 2018
declare @quarter char(2)= case 
      when @days between 0   and 111 then 'Q1'
      when @days between 112 and 195 then 'Q2'
      when @days between 196 and 279 then 'Q3'
      when @days between 280 and 363 then 'Q4'
      else 'Q0' end
      return concat(@years,'-', @quarter)
 end

GO

✓

select dbo.quarter('5/4/2018') quarter;
select dbo.quarter('2020-04-02') quarter;
select dbo.quarter('2022-01-24') quarter;
select dbo.quarter(GETDATE()) quarter;
select dbo.quarter('2021-12-25') quarter;
GO


| quarter |
| :------ |
| 2018-Q1 |

| quarter |
| :------ |
| 2020-Q1 |

| quarter |
| :------ |
| 2021-Q4 |

| quarter |
| :------ |
| 2021-Q4 |

| quarter |
| :------ |
| 2021-Q3 |

db<>fiddle here

Calculate financial year start and end date based on year entered SQL Server and SSRS

Using DATEADD and DATEDIFF you can computer for your fiscal years:

DECLARE @year INT = 2015

SELECT
    start_date = DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @year - 1900, 0)) - 1, 0)),
    end_date = DATEADD(DAY, -1, DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @year - 1900, 0)), 0)))

Read here for more common date routines.

To use this in a stored procedure:

CREATE PROCEDURE procedure_name
    @year AS INT
AS
BEGIN
SET NOCOUNT ON

SELECT
    start_date = DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @year - 1900, 0)) - 1, 0)),
    end_date = DATEADD(DAY, -1, DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @year - 1900, 0)), 0)))

END