SQL/VBA:如何从一个月的第一天以外的一天开始按会计年度分组 [英] SQL/VBA: How to group by a fiscal year starting from a day other than 1st day of month

问题描述

我正在尝试(使用MS Access)按会计年度对某些数据进行分组，该会计年度可能与日历年不同.

I’m trying (using MS Access) to group some data by a fiscal year, which can be different to the calendar year.

如果每个会计年度总是在给定月份的1月1日开始，那么解决方案将很简单:只需使用如下if语句即可:

If every fiscal year always started on the 1st of a given month, then the solution would be simple: one could simply use an if statement like this:

FY: IIf(Month([orderdate])<month([startFYofaCompany]);Year([orderdate])-1;Year([orderdate]))

在选择查询中，例如

SELECT statement here AS FY, Sum(something) AS SumOfSth
GROUP BY statement here;

但是，在我的情况下，困难在于给定公司的会计年度可以从给定月份的1号以外的日期开始.我一直在寻找解决方案，但是每个人都认为不是.

However, the difficulty in my case is that a financial year of a given company can start from date other than 1st of a given month. I’ve looked for a solution, but everyone assumes otherwise.

如果有人遇到同样的问题并且可以分享解决方案，我将不胜感激.

I’d be grateful if someone had the same problem and could share a solution.

没有模式重新.会计年度–数据库由多个公司组成，并且无论某个公司的FY是从第2月，第3日，第15日还是在给定月份的任何其他日期开始，该数据库都应该起作用.开始日期存储在单独的字段中(在上面的伪代码中:startFYofaCompany).

There is no pattern re. fiscal year – the database consists of multiple companies and should work whether some company has a FY starting on 2nd, 3rd, 15th, or any other day of any given month. The start date is stored in a separate field (in the pseudo-code above: startFYofaCompany).

推荐答案

如果要考虑月份和日期，那么显然您将需要在表(NOT NULL DEFAULT 1)中使用相应的2个字段.

If you have month + day to take into consideration, then obviously you will need the corresponding 2 fields in your table (NOT NULL DEFAULT 1).

添加后，将计算以下年份: year(orderDate) - IIF(dateserial(year(orderDate), startFYofaCompany_month, startFYofaCompany_day) > orderDate, 1, 0)

Once this is added, the following will calculate the year: year(orderDate) - IIF(dateserial(year(orderDate), startFYofaCompany_month, startFYofaCompany_day) > orderDate, 1, 0)

将此添加到您在查询中准备的2个地方.

Add this to the 2 places you have prepared in your query.

这篇关于SQL/VBA:如何从一个月的第一天以外的一天开始按会计年度分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！