Generating fiscal calendar dates

I need to generate a T-SQL script for loading data into this table:

create table dwd.FiscalCalendars (
_Id int not null identity(1,1)
,_DateInserted datetime not null
,_DateUpdated datetime null
,CalendarDate date not null
,CalendarDayOfWeek int not null
,CalendarDayOfMonth int not null
,CalendarDayOfYear int not null
,CalendarWeekOfYear int not null
,CalendarMonthOfYear int not null
,CalendarYear int not null
,FiscalDayOfWeek int not null
,FiscalDayOfMonth int not null
,FiscalDayOfQuarter int not null
,FiscalDayOfYear int not null
,FiscalWeekOfMonth int not null
,FiscalWeekOfQuarter int not null
,FiscalWeekOfYear int not null
,FiscalMonthOfQuarter int not null
,FiscalMonthOfYear int not null
,FiscalQuarterOfYear int not null
,FiscalYear int not null
,Holiday nvarchar(50) null
,constraint PK_FiscalCalendars primary key clustered (_Id asc)
,constraint NK_FiscalCalendars unique (CalendarDate)
);


In order to generate the script, I first need to generate each record. I decided to write some VBA code to do that. Here's the function I came up with - I'll make another to work backwards, so as to generate records for previous fiscal years for which I don't know the start date - hence I called this one "GenerateForwardCalendar":

```
Private Function GenerateForwardCalendar(ByVal startDate As Date, ByVal fStartYear As Integer, ByVal years As Integer) As Collection

Dim result As New Collection

Dim current As FiscalCalendarDate
Dim currentDate As Date
currentDate = startDate

Dim fYear As Integer
Dim fQuarterOfYear As Integer
Dim fMonthOfQuarter As Integer
Dim fWeekOfMonth As Integer
Dim fDayOfWeek As Integer

For fYear = fStartYear To fStartYear + years
Set current = FiscalCalendarDate.Create(currentDate, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, fYear, vbNullString)

For fQuarterOfYear = 1 To 4

current.FiscalDayOfQua

Why duplicate the for loop?

If ...
    For fWeekOfMonth = 1 To 5
        GoSub ProcessWeekDays
    Next
Else
    For fWeekOfMonth = 1 To 4
        GoSub ProcessWeekDays
    Next
End If

I suggest to store the 5 or 4 in a variable and parameterize one loop with it.

I can see this easily becoming a nightmare:

result = Framework.Strings.Format("INSERT INTO dwd.FiscalCalendars (_DateInserted,CalendarDate,CalendarDayOfWeek,CalendarDayOfMonth,CalendarDayOfYear,CalendarWeekOfMonth,CalendarWeekOfYear,CalendarMonthOfYear,CalendarYear," & _
                                  "FiscalDayOfWeek,FiscalDayOfMonth,FiscalDayOfQuarter,FiscalDayOfYear,FiscalWeekOfMonth,FiscalWeekOfQuarter,FiscalWeekOfYear,FiscalMonthOfQuarter,FiscalMonthOfYear,FiscalQuarterOfYear,FiscalYear,Holiday) ", _
                                  "VALUES (@ts,'{0}',{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},'{19}');", _
                                .CalendarDate, .CalendarDayOfWeek, .CalendarDayOfMonth, .CalendarDayOfYear, _
                                .CalendarWeekOfMonth, .CalendarWeekOfYear, .CalendarMonthOfYear, .calendarYear, _
                                .FiscalDayOfWeek, .FiscalDayOfMonth, .FiscalDayOfQuarter, .FiscalDayOfYear, _
                                .FiscalWeekOfMonth, .FiscalWeekOfQuarter, .FiscalWeekOfYear, _
                                .FiscalMonthOfQuarter, .FiscalMonthOfYear, .FiscalQuarterOfYear, .FiscalYear, .Holiday)

Too many things can go wrong:

• You might make a mistake in the {n} count

• You might make a mistake when aligning the quoted '{n}' items with the correct parameters

The problem is that you have too many things that have to be well aligned:

• the column list in the INSERT

• the column list in the format string

• the column list in the format params

Rewrite with good old-fashioned concatenation instead of Strings.Format.
That way you eliminate one potential error vector.
The result will also be more readable.

It might be a good idea to make the columns appear on their own lines,
both when writing the list for INSERT and the values.
That way you could easily copy-paste one of the lists in notepad and put side-by-side with the other to see where you missed something.
(It can happen that you swear everything is well-aligned,
but you just won't see the mistake until you actually break up the lines and put them side by side.
Speaking from experience here, unfortunately.)

StackExchange Code Review Q#70734, answer score: 5