-4

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?

vbjay
  • 107
  • 10

1 Answers1

0

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 );
vbjay
  • 107
  • 10