Custom Date Buckets — Fiscal Weeks and Rolling Windows

Q: Why does September 2020 fall under Fiscal Year 2021 in a July-June cycle?

Because the fiscal period starting in July 2020 runs through June 2021, and fiscal years take the year of the final period.

Q: Can a fiscal year start on any arbitrary month?

Yes, fiscal years can start on any month as long as they span 12 consecutive months based on organizational accounting rules.

Q: When should I use a CASE statement instead of a User-Defined Function?

Use a one-off CASE statement for single queries or high-scale data warehouse queries to avoid UDF execution overhead.

When DATE_TRUNC isn't enough — custom fiscal calendars, rolling buckets, ISO weeks. FIND_VIDEO: search 'SQL date bucketing fiscal calendar' — recommended channel: EverSQL. Aim for 10 min or under.

9 minutesVideo LessonPDF notes

Watch Custom Date Buckets — Fiscal Weeks and Rolling Windows on YouTube

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Loading progress...

Key moments

Introduction & Scope — The instructor outlines the goal of calculating fiscal years via CASE expressions and user-defined functions.
Calendar vs. Fiscal Year — The timeline difference between standard calendar years and custom July-to-June fiscal cycles is explained.
Logic Definition — The instructor introduces sample test dates (April and September) and the conditional month-checking strategy.
Writing the CASE Statement — The SQL CASE expression is constructed to conditionally increment the calendar year based on the month value.
Testing Date Scenarios — The query is tested against April 2020 (FY 2020) and September 2020 (FY 2021) to verify calculation accuracy.
Encapsulating with UDFs — The instructor demonstrates how to wrap the CASE logic inside a reusable scalar function for cleaner SQL queries.

PDF notes

Bottom line: You will learn how to map dates into custom fiscal year buckets using conditional CASE statements and wrap that logic inside user-defined functions (UDFs). You will master shifting year boundaries based on arbitrary starting months, such as a July-to-June fiscal cycle.

This lesson on Custom Date Buckets — Fiscal Weeks and Rolling Windows is hands-on and example-driven. You will learn how to map dates into custom fiscal year buckets using conditional CASE statements and wrap that logic inside user-defined functions (UDFs). You will master shifting year boundaries based on arbitrary starting months, such as a July-to-June fiscal cycle.

What You'll Be Able To Do

Differentiate calendar year boundaries from custom fiscal year period definitions.
Calculate the fiscal year from a given date using conditional CASE logic.
Extract date parts like month and calendar year to drive custom fiscal arithmetic.
Encapsulate repetitive fiscal date bucketing logic inside reusable user-defined functions.
Evaluate performance trade-offs between inline CASE expressions and scalar UDFs on large datasets.

Detailed Concept Walkthrough

1. Calendar Year vs. Fiscal Year Mechanics

A calendar year strictly spans January 1 to December 31, whereas a fiscal year is an arbitrary 12-month accounting period named after the year in which its final period ends.

Mechanism: A fiscal year can start on any designated month and spans 12 continuous months. The naming convention assigns the fiscal year the calendar year of its final month (e.g., July 2019 through June 2020 is Fiscal Year 2020).
Boundary Comparison: In a July-to-June fiscal structure, dates occurring from January through June belong to the ending period of that same calendar year. Dates occurring from July through December belong to the starting period of the next calendar year's ending period.
Best Practice: Always clarify the organization's cutoff month and naming rules before writing date queries, as some conventions label the fiscal year by the start year instead of the end year.

-- Inspecting date parts to analyze fiscal periods (July to June definition)
SELECT 
    EnrollmentDate,
    MONTH(EnrollmentDate) AS CalMonth,
    YEAR(EnrollmentDate)  AS CalYear
FROM Students;

Key Takeaway: A fiscal year is designated by the calendar year in which its final accounting month concludes.

2. Conditional Fiscal Year Calculation

Using SQL CASE logic, you can conditionally add 1 to the calendar year when an enrollment date falls after the fiscal cycle cutoff.

Mechanism: Extract the month integer using MONTH(date). If the value is strictly less than the starting month threshold (e.g., < 7 for a July start), the fiscal year equals YEAR(date).
Execution Flow: When MONTH(date) >= 7 (July through December), the date falls into the subsequent fiscal cycle, requiring an offset: YEAR(date) + 1.
Syntax Rule: The CASE expression evaluates sequentially, returning the calculated integer year without mutating the original date column.

-- Compute fiscal year dynamically for arbitrary dates
SELECT 
    EnrollmentDate,
    MONTH(EnrollmentDate) AS [Month],
    YEAR(EnrollmentDate)  AS [CalendarYear],
    CASE 
        WHEN MONTH(EnrollmentDate) < 7 THEN YEAR(EnrollmentDate)
        ELSE YEAR(EnrollmentDate) + 1
    END AS [FiscalYear]
FROM Students;

Key Takeaway: Add 1 to the calendar year for months at or beyond the fiscal starting month to align with the fiscal year's ending period.

3. Encapsulating Logic in User-Defined Functions

User-Defined Functions (UDFs) package repetitive transformation logic into reusable database routines to eliminate code duplication across queries.

Mechanism: A scalar UDF takes a date parameter as input, executes the internal CASE expression, and returns the computed fiscal year integer directly.
Under the Hood: Calling a scalar UDF inside a SELECT clause invokes the function per row, which abstracts away complex multi-line CASE logic from queries.
Best Practice: Use scalar UDFs for moderate workloads where code maintainability is key, but be cautious of execution overhead on massive data warehouse scale tables.

-- Example function definition and query usage
CREATE FUNCTION dbo.FiscalYear (@DateVal DATETIME)
RETURNS INT
AS
BEGIN
    RETURN (
        CASE 
            WHEN MONTH(@DateVal) < 7 THEN YEAR(@DateVal)
            ELSE YEAR(@DateVal) + 1
        END
    );
END;
GO

-- Calling the UDF in a SELECT statement
SELECT 
    EnrollmentDate,
    dbo.FiscalYear(EnrollmentDate) AS FiscalYear
FROM Students;

Key Takeaway: Encapsulating conditional date calculations in a UDF makes queries cleaner but requires performance consideration on massive datasets.

Topics Covered in Custom Date Buckets — Fiscal Weeks and Rolling Windows

Introduction & Scope (0:00 - 0:25) — The instructor outlines the goal of calculating fiscal years via CASE expressions and user-defined functions.
Calendar vs. Fiscal Year (0:25 - 1:05) — The timeline difference between standard calendar years and custom July-to-June fiscal cycles is explained.
Logic Definition (1:05 - 1:45) — The instructor introduces sample test dates (April and September) and the conditional month-checking strategy.
Writing the CASE Statement (1:45 - 2:30) — The SQL CASE expression is constructed to conditionally increment the calendar year based on the month value.
Testing Date Scenarios (2:30 - 3:15) — The query is tested against April 2020 (FY 2020) and September 2020 (FY 2021) to verify calculation accuracy.
Encapsulating with UDFs (3:15 - 4:00) — The instructor demonstrates how to wrap the CASE logic inside a reusable scalar function for cleaner SQL queries.

SQL Cheat Sheet

MONTH(date_col) — Returns the month integer (1-12) from a date
```
SELECT MONTH('2020-04-15'); -- Returns 4
```
YEAR(date_col) — Returns the four-digit calendar year integer
```
SELECT YEAR('2020-09-01'); -- Returns 2020
```
CASE WHEN MONTH(d) < 7 THEN YEAR(d) ELSE YEAR(d) + 1 END — Calculates July-to-June fiscal year dynamically
```
SELECT CASE WHEN MONTH(dt) < 7 THEN YEAR(dt) ELSE YEAR(dt) + 1 END;
```
dbo.FiscalYear(date_col) — Calls scalar UDF to return fiscal year
```
SELECT dbo.FiscalYear(EnrollmentDate) FROM Enrollments;
```

Comparison Table

Attribute	Calendar Year	Fiscal Year (July-June)
Start Month	January (Month 1)	July (Month 7)
End Month	December (Month 12)	June (Month 6)
Naming Rule	Current calendar year	Year of final month
Sept 2020 Value	2020	2021
April 2020 Value	2020	2020

Common Pitfalls

Mistake: Assuming fiscal years always start in January. Avoid: Verify company-specific fiscal calendars, which often begin in July, October, or other months.
Mistake: Forgetting to add one to the year for months past the cutoff. Avoid: Use conditional logic to increment the year for dates in the second half of the calendar year.
Mistake: Overusing scalar UDFs on massive data warehouse tables. Avoid: Use inline CASE statements or dimension tables when query performance on millions of rows is critical.

FAQs

Why does September 2020 fall under Fiscal Year 2021 in a July-June cycle? Because the fiscal period starting in July 2020 runs through June 2021, and fiscal years take the year of the final period.
Can a fiscal year start on any arbitrary month? Yes, fiscal years can start on any month as long as they span 12 consecutive months based on organizational accounting rules.
When should I use a CASE statement instead of a User-Defined Function? Use a one-off CASE statement for single queries or high-scale data warehouse queries to avoid UDF execution overhead.

Common pitfalls

Mistake: Assuming fiscal years always start in January. Avoid: Verify company-specific fiscal calendars, which often begin in July, October, or other months.
Mistake: Forgetting to add one to the year for months past the cutoff. Avoid: Use conditional logic to increment the year for dates in the second half of the calendar year.
Mistake: Overusing scalar UDFs on massive data warehouse tables. Avoid: Use inline CASE statements or dimension tables when query performance on millions of rows is critical.

Hey, welcome to another SQL Minute and in this episode we're going to talk about calculating a fiscal year. I'm going to quickly show you how you can do it with a CASE statement and then we're going to wrap up with a user-defined function. But before we get started, let's just re- really make sure we all understand um the difference between a calendar year and a fiscal year. So typically a calendar year starts in January and goes through December. That's how it works in the the western part of the world. Uh so a calendar year takes the year of the last period, right? So December 2019, my calendar year is 2019. Now a fiscal year can start in any month and then goes for 12 months. So in my example here, I have a fiscal year starting in July, and then it goes through June 2020. In this case, the fiscal year for that period of time would be 2020. What I've done here in in this timeline is also visualize that so that you can see I have the calendar year starting in the months December going all the way out to December and you can see the um fiscal year, okay? I mean the calendar year, excuse me. So below that I've done the fiscal years where you can see I got a fiscal year that ends in 2006 and, you know, presumably went past in time. Here I have 2020 ending in 2006, going all the way to July, and this is uh fiscal year 2020 cuz its ending month is in the year 2020. So that's kind of how this works. So hopefully this is going to help you understand um the interplay. And now let's get into the SQL. So what we're going to do is we're going to figure out what the fiscal year would be for April and also for September. And I have some sample code to do this. So I'm going to bring that in in steps here. So here's the the variables I'm going to use to as test data. Uh so here's the April test data and here's the September, and then I am going to bring in our formula to calculate the fiscal year. And I will now explain this. Okay, so the idea here is is that the scenario is that you would have a um CASE statement in your SELECT and what it's going to bring back to you is whether essentially the fiscal year for the enrollment date. How this works, and this is this is the tricky part, is the enrollment we calculate the month of the enrollment date, okay, and then based on that month we either add a 1 to the fiscal or to the calendar year or we leave it alone. So if if you look at our example here, and let's look in um it'd be the calendar year 2020. The trick here is is what month is my enrollment date in? If my enrollment date is in a month that is less than 7, so it's between 1 and 6, then I am in fiscal year 2020 because my period, the June, which is the end of the fiscal year, resides in the calendar year 2020, right? So in this case, all I need to do is return the year of the enrollment date. So far so good. Now the trick's going to be when we get into an enrollment date that's past June. So we're in the, I guess it'd be the beginning semester of the fiscal year, so that would be 07 through 12. In this case, I can't just take the year and call it the fiscal year because our definition for the the year for the fiscal year is is the year of um the last period, right? And the last period is way out here in June of 2021. So what I need to do here is I need to add one to the um year to get my fiscal year. So let's see how this works. Now make this better. I'm going to do a SELECT and we'll do um MONTH at EnrollmentDate and then the year so you can see the components. And I'll just add I You know what, I'm just going to add these right into this statement. All right. Get rid of this. So we're going to get the month, the year, and then the fiscal year. So the month is 4, the enrollment date's um year is 2000, and the fiscal year is 2020. Let's see how this works with the logic. So we we know that when our month is between 1 and 6 that we are going to use the calendar year of the current period, and that's 2020. So we're good. All right, so now let's change this to September, and then run this. So now my month is 9, my calendar year is 2020, and my fiscal year is 2021. So how's this working? All right, so I got 9, and what we've said is is that our fiscal year should take the um should be the year at the end of the period, right? And so if we look at the end of the period for my fiscal year, that's in June, and at this point, I'm in a 2021 calendar year. So that's the year that comes back, right? So I'm using the calendar year function to bring back the year, and then depending on the month, I'm adding one. Make sense? Okay, so this this works good, and if you only have to do it once, then I would say you could roll with the CASE statement. But what you'll find yourself um getting into situations is where you will use this a lot, okay? And if you don't have enormous amounts of data that you're working with, like data warehouse scale data, then you probably can get away with using what I would call a um user-defined function to slip into your SQL. So I'm not going to go into how to necessarily use the function, I just want to let you know that they're there for you to use, and the idea is is that the function can accept a parameter, this one I called date1, and then what we're doing is we're really rolling it through the same logic, right? So this CASE statement here is the same as this CASE statement. And I roll that parameter through the same logic, and then I return the result. So now instead of having to have this big, glommy CASE statement with all this logic exposed in every SQL statement I write, I can encapsulate it into a function called FiscalYear, and then just pass in like FiscalYear(EnrollmentDate). So, I guess I'll I'll show I'll just show you what that would look like real fast. If we were to do that here, it would be it'd look like that, and then this would call that user-defined function that I'd set up, do the calculation, and pull back the right result. So I hope this has helped you understand how to calculate a fiscal year. They can be very mind-bending, and um if you have any questions, leave them in the comments, and I'll try to address them. Have a good day.

Frequently asked questions

Why does September 2020 fall under Fiscal Year 2021 in a July-June cycle?