Summation over UK tax year

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Summation over UK tax year

Post by 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 :cheers: )
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 :sad: )
To add complexity, the start date of the UK tax year varies each year.
Can this be done, please?
Silverback

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Summation over UK tax year

Post by HansV »

silverback wrote:
05 Apr 2022, 13:57
This starts on the Wednesday of the first week in March.
Did you mean April instead of March?
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Summation over UK tax year

Post by silverback »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Summation over UK tax year

Post by 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");
Best wishes,
Hans

User avatar
silverback
5StarLounger
Posts: 771
Joined: 29 Jan 2010, 13:30

Re: Summation over UK tax year

Post by silverback »

Hans
I owe you more beer :cheers: :cheers: :cheers:
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