Generate Fiscal Calendar from Calendar Year and a specified Fiscal Month using T-SQL

Question

I am answering posts similar to

How do you generate a fiscal calendar from a calendar year and dynamic fiscal start month with week numbers of the month in T-SQL?

Answer 1

I wanted to show my sql table valued function to generate fiscal calendar details from a calendar year and a specified fiscal month start. Usage:

SELECT *
  FROM [dbo].[GenerateFiscalCalendarFromCalendarYear](YEAR(GETDATE()), 8)
 WHERE [CMonth] IN ( 7, 8, 9 )
   AND [WeekNumOfMonth]IN(1,2);

CMonth	FMonth	FYear	CFirstDay	CLastDay	Day	WeekNumOfMonth	FYearStartMonth
7	12	2022	2022-07-01	2022-07-31	2022-07-01	1	8
7	12	2022	2022-07-01	2022-07-31	2022-07-02	1	8
7	12	2022	2022-07-01	2022-07-31	2022-07-03	2	8
7	12	2022	2022-07-01	2022-07-31	2022-07-04	2	8
7	12	2022	2022-07-01	2022-07-31	2022-07-05	2	8
7	12	2022	2022-07-01	2022-07-31	2022-07-06	2	8
7	12	2022	2022-07-01	2022-07-31	2022-07-07	2	8
7	12	2022	2022-07-01	2022-07-31	2022-07-08	2	8
7	12	2022	2022-07-01	2022-07-31	2022-07-09	2	8
8	1	2023	2022-08-01	2022-08-31	2022-08-01	1	8
8	1	2023	2022-08-01	2022-08-31	2022-08-02	1	8
8	1	2023	2022-08-01	2022-08-31	2022-08-03	1	8
8	1	2023	2022-08-01	2022-08-31	2022-08-04	1	8
8	1	2023	2022-08-01	2022-08-31	2022-08-05	1	8
8	1	2023	2022-08-01	2022-08-31	2022-08-06	1	8
8	1	2023	2022-08-01	2022-08-31	2022-08-07	2	8
8	1	2023	2022-08-01	2022-08-31	2022-08-08	2	8
8	1	2023	2022-08-01	2022-08-31	2022-08-09	2	8
8	1	2023	2022-08-01	2022-08-31	2022-08-10	2	8
8	1	2023	2022-08-01	2022-08-31	2022-08-11	2	8
8	1	2023	2022-08-01	2022-08-31	2022-08-12	2	8
8	1	2023	2022-08-01	2022-08-31	2022-08-13	2	8
9	2	2023	2022-09-01	2022-09-30	2022-09-01	1	8
9	2	2023	2022-09-01	2022-09-30	2022-09-02	1	8
9	2	2023	2022-09-01	2022-09-30	2022-09-03	1	8
9	2	2023	2022-09-01	2022-09-30	2022-09-04	2	8
9	2	2023	2022-09-01	2022-09-30	2022-09-05	2	8
9	2	2023	2022-09-01	2022-09-30	2022-09-06	2	8
9	2	2023	2022-09-01	2022-09-30	2022-09-07	2	8
9	2	2023	2022-09-01	2022-09-30	2022-09-08	2	8
9	2	2023	2022-09-01	2022-09-30	2022-09-09	2	8
9	2	2023	2022-09-01	2022-09-30	2022-09-10	2	8

-- ================================================
-- Template generated from Template Explorer using:
-- Create Multi-Statement Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:      Jay Asbury
-- Create date: 8/5/2022
-- Description: Generates Calendar to Fiscal year Table for a year.
-- =============================================
CREATE FUNCTION [dbo].[GenerateFiscalCalendarFromCalendarYear] (
-- Add the parameters for the function here
@CalendarYear INT,
@FicalYearStartMonth INT)
RETURNS TABLE
--(
---- Add the column definitions for the TABLE variable here
--[CMonth] INT,
--[FMonth] INT,
--[FYear] INT,
--[CFirstDay] DATE,
--[CLastDay] DATE,
--[Day] DATE,
--[WeekNumOfMonth] INT,
--[FYearStartMonth] INT)
AS
RETURN (   WITH [months]
             AS (SELECT 1 [CMonth]
                 UNION ALL
                 SELECT [CMonth] + 1
                   FROM [months]
                  WHERE [months].[CMonth] <= 11),
                [Fyears]
             AS (SELECT *,
                        CASE
                             WHEN [months].[CMonth] >= @FicalYearStartMonth THEN @CalendarYear + 1
                             ELSE @CalendarYear END [FYear],
                        @CalendarYear [Cyear]
                   FROM [months]),
                [Ranges]
             AS (SELECT *,
                        DATEFROMPARTS([Fyears].[Cyear], [Fyears].[CMonth], 1) [CFirstDay],
                        EOMONTH(DATEFROMPARTS([Fyears].[Cyear], [Fyears].[CMonth], 1)) [CLastDay],
                        MONTH(
                            DATEADD(
                                MONTH,
                                MONTH(DATEFROMPARTS([Fyears].[Cyear], [Fyears].[CMonth], 1)) - @FicalYearStartMonth,
                                DATEFROMPARTS(
                                    DATEPART(YEAR, DATEFROMPARTS([Fyears].[Cyear], [Fyears].[CMonth], 1)), 1, 1))) [FMonth]
                   FROM [Fyears]),
                [Days]
             AS (SELECT [Ranges].[CMonth],
                        [Ranges].[FMonth],
                        [Ranges].[FYear],
                        [Ranges].[CFirstDay],
                        [Ranges].[CLastDay],
                        [Ranges].[CFirstDay] [Day]
                   FROM [Ranges]
                 UNION ALL
                 SELECT [Days].[CMonth],
                        [FMonth],
                        [Days].[FYear],
                        [Days].[CFirstDay],
                        [Days].[CLastDay],
                        DATEADD(DAY, 1, [Days].[Day]) [Day]
                   FROM [Days]
                  WHERE DATEADD(DAY, 1, [Days].[Day]) <= [Days].[CLastDay])
           SELECT [Days].[CMonth],
                  [Days].[FMonth],
                  [Days].[FYear],
                  [Days].[CFirstDay],
                  [Days].[CLastDay],
                  [Days].[Day],
                  (DATEPART(WEEK, [Days].[Day]) - DATEPART(WEEK, DATEADD(DAY, 1, EOMONTH([Days].[Day], -1)))) + 1 [WeekNumOfMonth],
                  @FicalYearStartMonth [FYearStartMonth]
             FROM [Days]);






GO

SELECT *
  FROM [dbo].[GenerateFiscalCalendarFromCalendarYear](YEAR(GETDATE()), 8)
 WHERE [CMonth] IN ( 7, 8, 9 )
   AND [WeekNumOfMonth] IN ( 1, 2 );