Create a fiscal calendar in T-SQL

Question

I am trying to create a fiscal calendar in which the fiscal year starts July 1 and a week is defined as Monday to Sunday.

But for example; if the 1st day in a week in a month is a Saturday, then Saturday to Sunday will be seen as 1 week in that month and the new week starts on Monday and ends on Sunday and so on.

See sample of the table I want to create below:

• Period refers to the months of the fiscal year.
• Week is number of weeks in that month.
• Start date(week start date) is the day the week began
• End date(week end date) is the day the week ended.
• Day of week is the dates between the start date and end date.
• Year

I am thinking that I need a procedure that maybe takes the first day of the fiscal year then iterates through all the days of the year adding the columns start and end date, week number, period and year the day belongs to.

Answer 1

Yet another option. This will generate 50 years is 0.703 seconds

Example

Set DateFirst  1

Declare @Date1 date = '2017-07-01'
Declare @Date2 date = '2019-06-30'


Select Period    = Dense_Rank() over (Partition By FY Order By FM)
      ,Week      = Dense_Rank() over (Partition By FY,FM Order By FW)
      ,StartDate = Min(D) over (Partition By FY,FM,FW )
      ,EndDate   = Max(D) over (Partition By FY,FM,FW )
      ,DayOfWeek = D
      ,Year      = FY
 From (
        Select FY = DatePart(Year,@Date1)-1+sum(case when convert(varchar(5),@Date1,101)=convert(varchar(5),D,101) then 1 else 0 end) over (Order By D)
              ,FM = sum(case when DatePart(Day,D)=DatePart(Day,@Date1) then 1 else 0 end) over (Order By D)
              ,FW = sum(case when DatePart(WeekDay,D)=1 then 1 else 0 end) over (Order By D)
              ,D
         From (
                Select Top (DateDiff(DAY,@Date1,@Date2)+1) 
                       D  = DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),@Date1) 
                 From  master..spt_values n1,master..spt_values n2
              ) A1
      ) A
 Order By D

Answer 2

You can create this table using a common table expression (cte for short) tally table. This involves manually specifying the first 10 rows (the select t from values... bit) and then cross joining this to itself several times in a second cte to exponentially create more rows. Because I have 6 cross joins (the t t1, t t2, t t3... part) I am generating a million rows (10^6), then working out how many I actually need in the top clause and then using row_number to generate an iterative list that is added as days to your period start date.

The output of this is a table of dates you can apply functions to, that will even work across multiple financial years. I would recommend you use this to create a Dates lookup table rather than running every single time:

declare @DateStart date = '20160701'
       ,@DateEnd   date = '20170630';

with t(t) as (select t from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(t))
    ,d(d) as (select top(datediff(d,@DateStart,@DateEnd)+1) dateadd(d,row_number() over (order by (select null))-1,@DateStart) from t t1,t t21,t t31,t t4,t t5,t t6)
select ((dense_rank() over (order by dateadd(m,datediff(m,0,d),0)) - 1) % 12) + 1 as [Period]

      ,dense_rank() over (partition by dateadd(m,datediff(m,0,d),0)
                          order by case when dateadd(d,1-datepart(dw,d),d) < dateadd(m,datediff(m,0,d),0)
                                        then dateadd(m,datediff(m,0,d),0)
                                        else dateadd(d,1-datepart(dw,d),d)
                                        end) as [Week]

      ,case when dateadd(d,1-datepart(dw,d),d) < dateadd(m,datediff(m,0,d),0)
            then dateadd(m,datediff(m,0,d),0)
            else dateadd(d,1-datepart(dw,d),d)
            end as StartDate

      ,case when dateadd(d,7-datepart(dw,d),d) > dateadd(m,datediff(m,0,d)+1,0)
            then dateadd(d,-1,dateadd(m,datediff(m,0,d)+1,0))
            else dateadd(d,7-datepart(dw,d),d)
            end as EndDate

      ,d as DayOfWeek
      ,year(d) as [Year]
from d
order by d;

Output:

+--------+------+-------------------------+-------------------------+------------+------+
| Period | Week |        StartDate        |         EndDate         | DayOfWeek  | Year |
+--------+------+-------------------------+-------------------------+------------+------+
|      1 |    1 | 2016-07-01 00:00:00.000 | 2016-07-02 00:00:00.000 | 2016-07-01 | 2016 |
|      1 |    1 | 2016-07-01 00:00:00.000 | 2016-07-02 00:00:00.000 | 2016-07-02 | 2016 |
|      1 |    2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-03 | 2016 |
|      1 |    2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-04 | 2016 |
|      1 |    2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-05 | 2016 |
|      1 |    2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-06 | 2016 |
|      1 |    2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-07 | 2016 |
|      1 |    2 | 2016-07-03 00:00:00.000 | 2016-07-09 00:00:00.000 | 2016-07-08 | 2017 |
.
.
.
|     12 |    5 | 2017-06-25 00:00:00.000 | 2017-07-01 00:00:00.000 | 2017-06-28 | 2017 |
|     12 |    5 | 2017-06-25 00:00:00.000 | 2017-07-01 00:00:00.000 | 2017-06-29 | 2017 |
|     12 |    5 | 2017-06-25 00:00:00.000 | 2017-07-01 00:00:00.000 | 2017-06-30 | 2017 |
+--------+------+-------------------------+-------------------------+------------+------+

Answer 3

You don't need a procedure, but some functions can be handy.

First of, keep in mind, that some date-time functions have a dependency on @@DATEFIRST. To make your code independent of this setting, you can have a function which normalizes the day number.

CREATE FUNCTION getNormalizedWeekDay
(
      @inputDate DATE
    , @dateFirst TINYINT = 1
)
RETURNS SMALLINT
AS
BEGIN
    RETURN ((DATEPART(WEEKDAY, @inputDate) + @@DATEFIRST - 1 - @dateFirst) % 7) + 1;
END

In this, you can tell which day you want to use and calculates the result based on that. Will be handy to determine if a day is Sunday for example.

If I understand you properly, you want a week function which behaves similarly to the built-in function, but treats 1st of July as the first day of year.

Something like this should do it:

CREATE FUNCTION getFiscalWeekNumber
(
      @inputDate        DATE
    , @firstFiscalMonth TINYINT = 7
    , @dateFirst        TINYINT = 1
)
RETURNS TINYINT
AS
BEGIN
    /* NULL Input handling. */
    IF (@inputDate IS NULL) BEGIN
        RETURN NULL
    END

    DECLARE @inputDateWeekDay        TINYINT = getNormalizedWeekDay(@inputDate, @dateFirst);
    DECLARE @inputWeekStartDate      DATE    = DATEADD(DAY, -(@inputDateWeekDay-1), @inputDate);

    DECLARE @firstDayOfYear          DATE    = DATEFROMPARTS(getFiscalYear(@inputDate, @firstFiscalMonth), @firstFiscalMonth, 1);
    DECLARE @firstWeekStartDate      DATE    = @firstDayOfYear;
    DECLARE @weekDayOfFirstDayOfYear TINYINT = getNormalizedWeekDay(@firstDayOfYear, @dateFirst);

    -- The day is between the first day of year and the beginning of the second week -> 1st (partial) week for non-iso style
    IF (@inputDate >= @firstDayOfYear AND @inputDate < DATEADD(DAY, -(@weekDayOfFirstDayOfYear-1), DATEADD(DAY, 7, @firstWeekStartDate))) BEGIN
        RETURN 1;
    END

    -- Adjust the first day of the weeks to match with @dateFirst.
    SET @firstWeekStartDate = DATEADD(DAY, -(@weekDayOfFirstDayOfYear-1), @firstWeekStartDate);

    RETURN (DATEDIFF(DAY, @firstWeekStartDate, @inputWeekStartDate) / 7) + 1;
END

Please keep in mind, that some of the functions may not exist in older versions of SQL Server, I am using SQL Server 2016.