查询同一业务年度内的退回记录

Question

为什么在31/07/2021之前输入的记录仍然显示在此查询中？我只想显示当前业务年度的记录。

公司的业务年度从01/08/YYYY开始，第二年在31/07/YYYY结束。该查询是否适用于每个业务年度？即“在明年的这个时候”。

到目前为止，我有以下SQL代码：

SELECT 
    FORMAT(DATEADD("m", 5, [OrderDate]), "\Qq") AS Quarter, *
FROM 
    tblOrders
WHERE 
    [OrderDate] BETWEEN DateSerial(Year(Date()) - 1, 8, 1) 
                    AND DateSerial(Year(Date()), 8, 0)
ORDER BY 
    tblOrders.OrderDate DESC;

Answer 1

DateSerial(Year(Date()) - 1，8，1)返回01.08.2020，DateSerial(Year(Date())，8，0)返回31.07.2021。所以最终你会得到01.08.2020到31.07.2021之间的所有日期。

我想你可以让Year(Date())动态化。如果当前日期等于或超过01.08.XXXX，但不超过31.12.XXXX，则它应该是当前年和当前年+1，否则它应该保持现在-当前年-1和当前年。

可能是这样的：

SELECT 
    FORMAT(DATEADD("m", 5, [OrderDate]), "\Qq") AS Quarter, *
FROM 
    tblOrders
WHERE 
    [OrderDate] BETWEEN IIF(Date() >= DateSerial(Year(Date()), 8, 1)  AND Date() <= DateSerial(Year(Date()), 13, 0), DateSerial(Year(Date()), 8, 1), DateSerial(Year(Date()) - 1, 8, 1))
                AND IIF(Date() >= DateSerial(Year(Date()), 8, 1) AND Date() <= DateSerial(Year(Date()), 13, 0), DateSerial(Year(Date()) + 1, 8, 0), DateSerial(Year(Date()), 8, 0))
ORDER BY 
    tblOrders.OrderDate DESC;

Answer 2

您可以在这里使用模块DateBank中的这两个函数：VBA.Date。

' Returns the primo calendar date of the specified financial/fiscal year.
' Returns the primo calender date of the current financial year, if no
' financial year is specified.
'
' 2021-05-09. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function DateFinancialYearPrimo( _
    Optional ByVal FinancialYear As Integer) _
    As Date
    
    Dim Month       As Integer
    Dim Day         As Integer
    Dim Years       As Integer
    Dim Primo       As Date
    
    Month = VBA.Month(DateFinancialStart())
    Day = VBA.Day(DateFinancialStart())
    
    If IsYear(FinancialYear) Then
        Years = FinancialYear - VBA.Year(FinancialStart)
        If Month = MinMonthValue And Day = MinDayValue Then
            ' The financial year is the calendar year.
        Else
            Years = Years - 1
        End If
    Else
        Years = VBA.Year(DateCalendar(Date)) - VBA.Year(FinancialStart)
    End If
    
    Primo = DateAdd("yyyy", Years, FinancialStart)

    DateFinancialYearPrimo = Primo
    
End Function


' Returns the ultimo calendar date of the specified financial/fiscal year.
' Returns the ultimo calender date of the current financial year, if no
' financial year is specified.
'
' 2021-05-09. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function DateFinancialYearUltimo( _
    Optional ByVal FinancialYear As Integer) _
    As Date
    
    Dim Ultimo      As Date
        
    Ultimo = DateAdd("d", -1, DateAdd("yyyy", 1, DateFinancialYearPrimo(FinancialYear)))
    
    DateFinancialYearUltimo = Ultimo

End Function


' Gets or sets the start day and month of the financial/fiscal year as a
' date value applied a neutral year.
'
' The start month can be any month.
' The start day can be any day less than or equal 28, which is the
' highest day value valid for any month.
'
' Default value is January 1st.
'
' Examples:
'   ' Set financial year.
'   StartDate = DateFinancialStart(10, 1)
'   ' StartDate -> 2000-10-01
'
'   ' Get financial year.
'   StartDate = DateFinancialStart
'   ' StartDate -> 2000-10-01
'   EndDate = DateFinancialEnd
'   ' EndDate -> 2000-09-30
'
' 2021-05-08. Gustav Brock. Cactus Data ApS, CPH.
'
Public Function DateFinancialStart( _
    Optional ByVal StartMonth As Integer, _
    Optional ByVal StartDay As Integer) _
    As Date
    
    ' Validate input.
    If IsMonth(StartMonth) And IsDayAllMonths(StartDay) Then
        FinancialStart = DateSerial(Year(DefaultStart), StartMonth, StartDay)
    End If
    If FinancialStart = #12:00:00 AM# Then
        FinancialStart = DefaultStart
    End If
    
    DateFinancialStart = FinancialStart

End Function

注:仅部分代码。