SQL 显示日期在财政年度的哪个季度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8738818/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Showing what quarter of a financial year a date is in
提问by Michael A
I'm trying to construct a query that will map two columns, one, a date from a table, the second column an alias to show what quarter and financial year the date falls into.
我正在尝试构建一个查询，该查询将映射两列，一列是表中的日期，第二列是别名以显示日期所属的季度和财政年度。
Unfortunately I don't have enough knowledge of SQL to know where to begin. I know that I'd do this with a combination of getdate()and dateadd(MONTH,,)however nothing that I've put together has come close to working.
不幸的是，我没有足够的 SQL 知识来知道从哪里开始。我知道，我会用的组合做到这一点getdate()，并dateadd(MONTH,,)却没有，我已经把已接近工作。
To further complicate this the financial years in Australia go from 1st July- 30th of June so quarter one of financial year 2012 would start from July 1st 2012.
更复杂的是，澳大利亚的财政年度是从 7 月 1 日至 6 月 30 日，因此 2012 财政年度的第一季度将从 2012 年 7 月 1 日开始。
I can do this without a statement however I'd much rather have it in a statement as it's going to be used in an SSRS report and a C# application and it would make maintenance significantly easier.
我可以在没有声明的情况下执行此操作，但是我更愿意将其包含在声明中，因为它将用于 SSRS 报告和 C# 应用程序中，这将使维护变得更加容易。
回答by Adam Ralph
This should work:-
这应该有效：-
SELECT
MyDate,
CASE
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN convert(char(4), YEAR(MyDate) - 1) + 'Q3'
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN convert(char(4), YEAR(MyDate) - 1) + 'Q4'
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN convert(char(4), YEAR(MyDate) - 0) + 'Q1'
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN convert(char(4), YEAR(MyDate) - 0) + 'Q2'
END AS Quarter
FROM
MyTable
Output:-
输出：-
MyDate Quarter
---------- --------
2011-01-01 "2010Q3"
2011-04-01 "2010Q4"
2011-07-01 "2011Q1"
2011-10-01 "2011Q2"
回答by Granger
This method doesn't require a CASE statement, and works for any monthly fiscal calendar.
此方法不需要 CASE 语句，适用于任何月度会计日历。
DECLARE @firstMonthOfFiscalQ1 int = 7; --1=January
SELECT DateColumn
, FLOOR(((12 + MONTH(DateColumn) - @firstMonthOfFiscalQ1) % 12) / 3 ) + 1 AS FiscalQuarter
FROM SomeTable
WHERE 1=1;
回答by Explosion Pills
I think it would be easiest to do this with CASE
我认为这样做最容易 CASE
SELECT
date
, CASE
WHEN MONTH(date) BETWEEN 7 AND 9 THEN 'First Quarter'
WHEN MONTH(date) BETWEEN 10 AND 12 THEN 'Second Quarter'
WHEN MONTH(date) BETWEEN 1 AND 3 THEN 'Third Quarter'
WHEN MONTH(date) BETWEEN 4 AND 6 THEN 'Fourth Quarter'
END AS quarter
FROM Stuff
回答by Rory
Just in case you want to do some wacky mathematical operations to avoid a case statement:
以防万一你想做一些古怪的数学运算来避免 case 语句：
select mydate,
convert(char(4), year(mydate) - (((DATEPART(qq, mydate) - ((DATEPART(qq, mydate) + 1) % 4) + 1) / -4) + 1)) +
'Q' + convert(char(1), ((DATEPART(qq, mydate) + 1) % 4) + 1) quar
from sampleData
回答by rejj
Consider creating an equivalent of a Date Dimension table from a data warehouse. This would have one row per day for every year your data could possibly cover (start from some reasonable epoch and go forward until some reasonably far future date).
考虑从数据仓库创建一个等效的日期维度表。对于您的数据可能涵盖的每一年，这将每天一行（从某个合理的时期开始，直到某个合理的未来日期）。
Within this table, you would have columns describing info about every day - including calendar year, half-year, quarter, month; and fiscal year, half-year, quarter.
在这个表中，你会有描述每一天信息的列——包括日历年、半年、季度、月份；和财政年度，半年，季度。
Your report then simply joins to this table based on date to pull this info. This gives you future support for other info about dates also, and removes the responsibility of this calculation from each individual reporting query.
然后，您的报告只需根据日期加入此表即可提取此信息。这也为您提供了有关日期的其他信息的未来支持，并从每个单独的报告查询中删除了此计算的责任。
回答by MrsAdmin
I found that using 'Explosion Pills' result worked, but I wanted mine to look like the answer by 'Adam Ralph', however I kept experiencing errors. I was able to work around this and created a new CASEexample:
我发现使用 ' Explosion Pills' 结果有效，但我希望我的看起来像 ' Adam Ralph'的答案，但是我一直遇到错误。我能够解决这个问题并创建了一个新CASE示例：
CASE -- Results: 2011 (Financial Year )
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN (YEAR(MyDate) - 1)
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN (year(MyDate) - 1)
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN (YEAR(MyDate) - 0)
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN (YEAR(MyDate) - 0)
END AS FYr,
CASE -- Results: Q4 (Financial Qtr)
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN 'Q1'
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN 'Q2'
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN 'Q3'
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN 'Q4'
END AS FQtr,
-- https://stackoverflow.com/a/22170892/2337102
CASE -- Results: 2011-Q4 (Financial Yr-Qtr)
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN concat((YEAR(MyDate) - 1), '-', 'Q3')
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN concat((YEAR(MyDate) - 1), '-', 'Q4')
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN concat((YEAR(MyDate) - 0), '-', 'Q1')
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN concat((YEAR(MyDate) - 0), '-', 'Q2')
END AS FYrQtr,
CASE -- Results: 11-Q4 (Financial (short) Yr-Qtr)
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN concat((date_format(MyDate, '%y') - 1), '-', 'Q3')
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN concat((date_format(MyDate, '%y') - 1), '-', 'Q4')
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN concat((date_format(MyDate, '%y') - 0), '-', 'Q1')
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN concat((date_format(MyDate, '%y') - 0), '-', 'Q2')
END AS FYrQtr,
CASE -- Results: 2011-2012/Q4 (Financial Range Yr/Qtr)
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN concat(YEAR(MyDate) - 1,'-', YEAR(MyDate), '/', 'Q3')
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN concat(YEAR(MyDate) - 1,'-', YEAR(MyDate), '/', 'Q4')
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN concat(YEAR(MyDate) - 0,'-', YEAR(MyDate) + 1, '/', 'Q1')
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN concat(YEAR(MyDate) - 0,'-', YEAR(MyDate) + 1, '/', 'Q2')
END AS FRangeQtr,
CASE -- Results: 11-12/Q4 (Financial Range (short) Yr/Qtr)
WHEN MONTH(MyDate) BETWEEN 1 AND 3 THEN concat((date_format(MyDate, '%y') - 1),'-', (date_format(MyDate, '%y')), '/', 'Q3')
WHEN MONTH(MyDate) BETWEEN 4 AND 6 THEN concat((date_format(MyDate, '%y') - 1),'-', (date_format(MyDate, '%y')), '/', 'Q4')
WHEN MONTH(MyDate) BETWEEN 7 AND 9 THEN concat((date_format(MyDate, '%y') - 0),'-', (date_format(MyDate, '%y') + 1), '/', 'Q1')
WHEN MONTH(MyDate) BETWEEN 10 AND 12 THEN concat((date_format(MyDate, '%y') - 0),'-', (date_format(MyDate, '%y') + 1), '/', 'Q2')
END AS FRangeQtr2,
I am adding my answer hoping it will help someone else who comes via Google and experiences issues.
我正在添加我的答案，希望它可以帮助通过 Google 访问并遇到问题的其他人。
回答by Alex Addison
A few years late to the game, but here's my quick-easy-clean method for Australians using GetDate(). Even your accounting calendar is upside-down :)
比赛晚了几年，但这是我使用 GetDate() 为澳大利亚人提供的快速简便的清洁方法。甚至你的会计日历也是颠倒的 :)
SELECT CHOOSE(MONTH(GetDate()),'3Q','3Q','3Q','4Q','4Q','4Q','1Q','1Q','1Q','2Q','2Q','2Q') AS AccountingQuarter
The SQL choose command takes the month of the date as the parameter, then chooses from the 12 available options. Of course, most other systems would start with 1Q/Q1 in the first choice.
SQL 选择命令将日期的月份作为参数，然后从 12 个可用选项中进行选择。当然，大多数其他系统会从 1Q/Q1 开始作为首选。
回答by Dan
We already have a sysDate table that contains dates from Jan 1, 1900 to Dec 31, 2030 that we use for processing. We were asked to do fiscal year handling, and so we modified our table to include FiscalYearString varchar(9), FiscalYear intand FiscalQuarter intcolumns.
我们已经有一个 sysDate 表，其中包含我们用于处理的从 1900 年 1 月 1 日到 2030 年 12 月 31 日的日期。我们被要求进行会计年度处理，因此我们修改了我们的表格以包含FiscalYearString varchar(9),FiscalYear int和FiscalQuarter int列。
The script we use to update these columns is this:
我们用来更新这些列的脚本是这样的：
DECLARE @StartingMonthOfFiscalYear INT = 4 -- 1-Jan, 2-Feb, 3-Mar, 4-Apr, 5-May, 6-Jun, 7-Jul, 8-Aug, 9-Sep, 10-Oct, 11-Nov, 12-Dec
UPDATE sysDate
SET FiscalYear = CASE WHEN MONTH([Date]) < @StartingMonthOfFiscalYear
THEN YEAR([Date])-1
ELSE YEAR([Date]) END,
FiscalYearString = CASE WHEN MONTH([Date]) < @StartingMonthOfFiscalYear
THEN CAST(YEAR([Date])-1 AS Varchar(4))+'/'+CAST(YEAR([Date]) AS Varchar(4))
ELSE CAST(YEAR([Date]) AS Varchar(4))+'/'+CAST(YEAR([Date])+1 AS Varchar(4)) END,
FiscalQuarter = CASE WHEN MONTH([Date]) < @StartingMonthOfFiscalYear
THEN ((MONTH([Date]) + 12 - @StartingMonthOfFiscalYear) / 3) + 1
ELSE ((MONTH([Date]) - @StartingMonthOfFiscalYear) / 3) + 1 END
This updates over 47,000 rows in less than 1 second, saving us from having to recalculate every time we need it.
这在不到 1 秒的时间内更新了超过 47,000 行，使我们不必每次需要时都重新计算。
回答by Jim
I stumbled over this post by accident but didn't find a simple answer. All I do is add the correct number of months using the applicable function and then take the strings needed for the proper quarter. Keeping it simple is always the best.
我偶然发现了这篇文章，但没有找到简单的答案。我所做的就是使用适用的函数添加正确的月数，然后获取正确季度所需的字符串。保持简单总是最好的。
Year end begins in Aug so push the date by 5 months. An example in MSACCESS:
年终从 8 月开始，因此将日期推迟 5 个月。MSACCESS 中的一个例子：
Quarter: DatePart('yyyy',DateAdd('m',+5,[YOURDATEFIELD])) & "/Q" & DatePart('q',DateAdd('m',+5,[YOURDATEFIELD]))
季度：DatePart('yyyy',DateAdd('m',+5,[YOURDATEFIELD])) & "/Q" & DatePart('q',DateAdd('m',+5,[YOURDATEFIELD]))
回答by user6913197
declare @sdate date='2017-12-01'
declare @edate date='2018-07-01'
;with rs as
(
select 1 [QNum],@sdate StDT
union all
select [QNum]+1, DATEADD(qq,1,stdt) from rs where [QNum]<=datediff(qq,@sdate,@edate)
)
select QNum, convert(varchar(10),StDT,105) StDT,convert(varchar(10),dateAdd(m,3,DATEADD(d, -1, DATEADD(qq,DATEDIFF(qq, 0, stdt) + 1, 0))),105)EndDT from rs
--select left(datename(mm,s),3) + ' ' +cast(year(s) as varchar) from rs
