用于将YYYY MM与财务年份分开的SQL

I have a column which states month and year YYYY MM. I've separated those into two columns (Year and Month). The problem is, the year is the calendar year whereas ideally I need the fiscal year I use (Apr 01 to Mar 31 - This will never change).

我有一个列表明月和年YYYY MM。我将它们分成两列(年和月)。问题是,年份是历年,而理想情况下我需要我使用的会计年度(4月1日至3月31日 - 这将永远不会改变)。

Other solutions I've seen are based on date format, whereas my original column is string.

我见过的其他解决方案都基于日期格式,而我的原始列是字符串。

I need a statement that returns the fiscal year for my new year column instead of the calendar year.

我需要一个声明来返回我的新年列而不是日历年的会计年度。

My current statement is:

我目前的陈述是:

Select Month,
parsename(replace(Month,' ','.'),1) as MonthM,
parsename(replace(Month,' ','.'),2) as Year
FROM TblTrade

Which works to separate the columns.

哪个用于分隔列。

So expected results would be for example: Feb 15 becomes Feb and 2015. Apr 15 becomes Apr and 2016.

因此预期结果将是例如:2月15日变为2月和2015年。4月15日变为4月和2016年。

Please advise.

4 个解决方案

#1

Sql server:

declare @date datetime = getdate();
select 
(YEAR(DATEADD(Month,-((DATEPART(Month,@date)+5) %12),@date))) AS Financial_Year

Assuming April is month 1

假设四月是第一个月

#2

Try this

    select case 
             when to_char(to_date(column_name,'yyyy mm'),'mm') between 01 and 03 
                  then to_char(trunc(to_date(column_name,'yyyy mm')),'yyyy')-1
             else to_number(to_char(trunc(to_date(column_name,'yyyy mm')),'yyyy')) end 
    fiscal_year
    from table_name

I'm using oracle db This will work when column is string and has valid data i.e date in format like yyyy mm

我正在使用oracle db当列是字符串并且具有有效数据时,这将起作用,即日期格式为yyyy mm

#3

Since you've read those other articles (you should really mention what research you've done in your question) and you're still having problems, I've had a play for you.

既然你已经阅读了其他文章(你应该真的提到你在你的问题中做了什么研究),而你仍然遇到问题,我已经为你做了一个游戏。

If I understand correctly, you have a varchar with YYYY MM eg

如果我理解正确,你有一个带有YYYY MM的varchar例如

etc And you want

等你想要的

Jan 2014
Feb 2014
Mar 2014
Apr 2015

Here goes...

Setup some test data

设置一些测试数据

    DROP TABLE IF EXISTS #Test;

    WITH Dates AS (
        SELECT CAST(GETDATE() AS DATE) AS Date
        UNION ALL
        SELECT DATEADD(MONTH, -1, Date) FROM Dates
        WHERE Date > '20140101'
    )
    SELECT DISTINCT
        CONVERT(VARCHAR(4), YEAR(Date)) + ' ' +RIGHT(CONVERT(VARCHAR(6), Date, 112), 2) YearMonth
    INTO #Test 
     FROM Dates
    OPTION (MAXRECURSION 0);

    SELECT * FROM #Test

YearMonth
---------
2013 12
2014 01
2014 02
2014 03
2014 04
2014 05
etc

Find Fiscal Year

找到财政年度

    SELECT 
        LEFT(YEARMONTH, 4) Year
        ,RIGHT(YEARMONTH, 2) Month
        ,LEFT(DATENAME(MONTH , DateAdd( month , CONVERT(INT,RIGHT(YEARMONTH, 2)) , -1 )), 3) MonthName
        ,IIF(CONVERT(INT, RIGHT(YEARMONTH, 2)) >= 4, CONVERT(INT,LEFT(YEARMONTH, 4)), CONVERT(INT,LEFT(YEARMONTH, 4)-1 )) FiscalYear
    FROM #TEST

    Year Month MonthName FiscalYear
    ---- ----- --------- -----------
    2013 12    Dec       2013
    2014 01    Jan       2013
    2014 02    Feb       2013
    2014 03    Mar       2013
    2014 04    Apr       2014
    2014 05    May       2014
    2014 06    Jun       2014
    etc

You could put the year/month parsing in a sub query just to make the code cleaner and some of the nasty formatting could be replaced with FORMAT since you're on 2012.

您可以将年/月解析放在子查询中,只是为了使代码更清晰,一些讨厌的格式可以用FORMAT替换,因为你在2012年。

Hope this is what you're after and helps.

希望这是你所追求和帮助的。

#4

Since you included the Tableau tag, I'll describe the Tableau approach -- which is a little different than the other answers since you tend to specify what you want to Tableau, and let its driver generate the necessary SQL for your database.

由于您包含了Tableau标记,因此我将描述Tableau方法 - 这与其他答案略有不同,因为您倾向于指定Tableau的内容,并让其驱动程序为您的数据库生成必要的SQL。

First, it will work best if you have a single field that has datatype DATE instead of separate fields for month and year.

首先,如果您有一个具有数据类型DATE的单个字段而不是月份和年份的单独字段,那么它将最有效。

You can then roll up dates to the nearest year, month, day etc (actually truncating to the beginning of the period) or extract specific parts of dates year, month, day etc as needed for grouping/display.

然后,您可以将日期汇总到最近的年,月,日等(实际上截断到期间的开头)或根据分组/显示的需要提取日期年,月,日等的特定部分。

The added benefit of working with a true DATE datatype is that you can tell Tableau the beginning of your fiscal year for each data source, and it will sort dates appropriately. Just right click on a data source and set the date properties. You can also set the start of the week and the date format.

使用真正的DATE数据类型的额外好处是,您可以告诉Tableau每个数据源的会计年度的开始,并且它将适当地对日期进行排序。只需右键单击数据源并设置日期属性即可。您还可以设置一周的开始和日期格式。

#1