Summation over UK tax year

[silverback]

We have already had help from the forum in producing a query to display costs by ascending month and year. The query displays costs for months January to December by calendar year. This is the SQL

TRANSFORM Sum(tblOutgoing.Cost) AS SumOfCost
SELECT Year([PurchaseDate]) AS [Year]
FROM tblCategory INNER JOIN tblOutgoing ON tblCategory.CategoryID = tblOutgoing.Category
GROUP BY Year([PurchaseDate])
PIVOT MonthName(Month([PurchaseDate]),True) In (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec);

The list of months is necessary otherwise the query lists the months in alphabetical order. (Thanks, Hans )
We would now like to bring this in line with the UK tax year. This starts on the Wednesday of the first week in March. So, today is the last day of the UK tax year 2021-22. This means that the cost summary for this April will have to omit this year's few days of April 2022 (1st, 2nd, 3rd and 4th) which are in the tax year 2021-22 but the end of the tax year 2022-23 will have a few days of April to cope with. We could either list costs for a 4 day April 2023 or add these days into the March 2023 total. (but we don't know how to do any of this )
To add complexity, the start date of the UK tax year varies each year.
Can this be done, please?
Silverback

[HansV]

This starts on the Wednesday of the first week in March.

Did you mean April instead of March?

[silverback]

Oops. Yes. UK Tax year starts Wednesday of the first week in April.
Silverback

[HansV]

It can probably be done with a complicated formula, but I'd create a small custom function in a module in the Visual Basic Editor to calculate the tax year:

Code: Select all

Function GetTaxYear(d)
    Dim s As Date
    Dim y As Long
    If IsDate(d) Then
        s = DateSerial(Year(d), 4, 1)
        s = s + 7 - Weekday(s + 3)
        y = Year(d)
        If d < s Then
            y = y - 1
        End If
        GetTaxYear = y & "-" & y + 1
    Else
        GetTaxYear = Null
    End If
End Function

The SQL could look like this:

TRANSFORM Sum(tblOutgoing.Cost) AS SumOfCost
SELECT GetTaxYear([PurchaseDate]) AS [TaxYear]
FROM tblCategory INNER JOIN tblOutgoing ON tblCategory.CategoryID = tblOutgoing.Category
GROUP BY GetTaxYear([PurchaseDate])
PIVOT Format([PurchaseDate],"yyyy-mm");

[silverback]

Hans
I owe you more beer
Your solution worked fine.
I changed the SQL to

TRANSFORM Sum(tblOutgoing.Cost) AS SumOfCost
SELECT GetTaxYear([PurchaseDate]) AS TaxYear
FROM tblCategory INNER JOIN tblOutgoing ON tblCategory.CategoryID = tblOutgoing.Category
GROUP BY GetTaxYear([PurchaseDate])
PIVOT MonthName(Month([PurchaseDate]),True) In (Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar );

in order to list the sums by month.
Thank you again
Silverback