Produce date range string from a database table

Regarding the points of interest you identified:

1. Using either one or two queries to get your data can be acceptable so long as it meets your performance criteria in actual use. However, I am going to suggest that you package the two results (Start and End dates) within a data object so that the rest of your code can operate on a single context of data from the database.

2. More efficient way to produce the date range string: Using Select Case can become a problem if the number of possible cases can continue to grow over time. It would seem there is only a finite number of ways that the range string will be represented...So, using Select Case looks OK here. On the other hand, the order of the Case statements matter here because they do not specify entirely independent cases. Because of this, I would use If-ElseIf-Else instead because it makes this overlapping condition more obvious. Also, there are some opportunities to apply the Don't Repeat Yourself (DRY) principle within the date range string code.

3. Structure: There are some structural changes that I am suggesting for you to consider. Instantiating a SecureDatabase instance inside the ReportRepository object would be considered inconsistent with SOLID coding practices (in particular, the letters "L" and "D"). In the code below, the ReportRepository is rewritten to consume an IDataAccessObject reference and a JobNumber in its Create function input parameters.

4. (Testing) A consequence of creating the SecureDatabase instance within the ReportRepository object is that it is impossible to test the code without the existence of a database. And, accordingly, I noticed a Sample database identifier within the SQL statements of the provided code. Passing in an IDataAccessObject facilitates testing free of a database (Sample or otherwise) and is more consistent with SOLID coding practices. Further, in the spirit of easy testing, the MonitoringDates function was made Public to allow testing without having to call ReportRepository.Create or have a database.

The code below is a revised version incorporating the points described above.

ReportRepository

'@PredeclaredId
'@Folder("ProjectWriter.Repository")
Option Explicit

Implements IReportRepository

Private Type TReportRepository
    MonitoringDates As String
    JobNumber As String
    StartDate As Date
    EndDate As Date
    SecureDatabase As IDataAccessObject
End Type

Private this As TReportRepository

Public Property Get Self() As ReportRepository
    Set Self = Me
End Property

Public Function Create(ByVal dDB As IDataAccessObject, ByVal JobNumber As String) As IReportRepository
    With New ReportRepository
        .Initialise dDB, JobNumber
        Set Create = .Self
    End With
End Function

Public Sub Initialise(ByVal dDB As IDataAccessObject, ByVal JobNumber As String)
    Set this.SecureDatabase = dDB
    this.JobNumber = JobNumber
    
    Dim dataObject As DateRangeDTO
    Set dataObject = this.SecureDatabase.RetrieveDateRangeDTO(JobNumber)
    
    this.StartDate = dataObject.StartDate
    this.EndDate = dataObject.EndDate
    this.MonitoringDates = MonitoringDates(dataObject.StartDate, dataObject.EndDate)
End Sub

Public Function MonitoringDates(ByVal vbaStartDate As Date, ByVal vbaEndDate As Date) As String

    Dim dStartDateFormat As String
    dStartDateFormat = "dd mmmm yyyy"
    
    Dim endDateExpression As String
    endDateExpression = VBA.Format$(vbaEndDate, dStartDateFormat)

    Dim toEndDateExpression As String
    toEndDateExpression = " - " & endDateExpression
    
    If vbaStartDate = vbaEndDate Then
        MonitoringDates = endDateExpression
    ElseIf VBA.Year(vbaStartDate) = VBA.Year(vbaEndDate) Then
        
        dStartDateFormat = IIf(VBA.Month(vbaStartDate) = VBA.Month(vbaEndDate), "dd", "dd mmmm")
        
        MonitoringDates = VBA.Format$(vbaStartDate, dStartDateFormat) & toEndDateExpression
    Else
        MonitoringDates = VBA.Format$(vbaStartDate, dStartDateFormat) & toEndDateExpression
    End If

End Function

Private Property Get IReportRepository_MonitoringDates() As String
    IReportRepository_MonitoringDates = this.MonitoringDates
End Property

'other properties

IDataAccessObject IDataAccessObject was modified to return a simple data object

Public Function RetrieveDateRangeDTO(ByVal JobNumber As String) As DateRangeDTO
End Function

DateRangeDTO DateRangeDTO keeps the Job number, start and end dates as a single context.

Option Explicit

Private Type TDateRangeDTO
    JobNumber As String
    StartDate As Date
    EndDate As Date
End Type

Private this As TDateRangeDTO

Public Property Get JobNumber() As String
    JobNumber = this.JobNumber
End Property
Public Property Let JobNumber(ByVal RHS As String)
    this.JobNumber = RHS
End Property

Public Property Get StartDate() As Date
    StartDate = this.StartDate
End Property
Public Property Let StartDate(ByVal RHS As Date)
    this.StartDate = RHS
End Property

Public Property Get EndDate() As Date
    EndDate = this.EndDate
End Property
Public Property Let EndDate(ByVal RHS As Date)
    this.EndDate = RHS
End Property

An example of testing without a database using a Fake object

SecureDatabaseFake

Implements IDataAccessObject

Private Type TSecureDatabaseFake
    TestDTO As DateRangeDTO
End Type

Private this As TSecureDatabaseFake

Const ConnectionString As String = "<connection string here>"

Private Function IDataAccessObject_RetrieveDateRangeDTO(ByVal testJobNumber As String) As DateRangeDTO
    this.TestDTO.JobNumber = testJobNumber
    Set IDataAccessObject_RetrieveDateRangeDTOO = this.TestDTO
End Function

Public Sub LoadDateRangeDTO(ByVal testDataDTO As DateRangeDTO)
    Set this.TestDTO = testDataDTO
End Sub

A couple tests made possible by using the database Fake and other changes:

Option Explicit


Sub Test()

    'Arrange
    Dim dDBFake As SecureDatabaseFake
    Set dDBFake = New SecureDatabaseFake

    Dim dDB As IDataAccessObject
    Set dDB = dDBFake

    Dim dDTO As DateRangeDTO
    Set dDTO = New DateRangeDTO
    dDTO.StartDate = DateValue("January 3, 2022")
    dDTO.EndDate = DateValue("August 4, 2045")

    'Specify the results that the fake database should return
    dDBFake.LoadDateRangeDTO dDTO

    'Act
    Dim dRep As IReportRepository
    Set dRep = ReportRepository.Create(dDB, "1234")
    
    'Assert
    If dRep.MonitoringDates <> "03 January 2022 - 04 August 2045" Then
        MsgBox "Test Failed"
        Exit Sub
    End If

    MsgBox "TestPassed"

End Sub

Sub TestMonitoringDatesDirectly()
    Dim dTestResult As String
    
    dTestResult = ReportRepository.MonitoringDates(DateValue("January 3, 2022"), DateValue("January 14, 2022"))
    If dTestResult <> "03 - 14 January 2022" Then
        MsgBox "Test Failed"
        Exit Sub
    End If

    MsgBox "TestPassed"
End Sub