使用日历表SQL查找去年的会计年度数据

My database has a calendar table that provides the fiscal year, period, etc. based on date (using Teradata with SQLAssistant). I'm trying to get last year's data in a SQL query which I thought would be as simple as adding a -1 in the where clause but it isn't working. I don't want to hard code a date in as I don't want to remember to update the query when the fiscal year changes.

我的数据库有一个日历表，它根据日期提供会计年度，期间等（使用带有SQLAssistant的Teradata）。我试图在SQL查询中获取去年的数据，我认为这就像在where子句中添加-1一样简单，但它不起作用。我不想硬编码日期，因为我不想记得在会计年度变化时更新查询。

Simplified query that works and brings back sales data for all fiscal years:

简化的查询可以工作并带回所有会计年度的销售数据：

Select s.sales, c.fiscalPeriod, c.fiscalYear
from sales s
join calendar c
on s.salesDate = c.calDate

What I've tried, but it's bringing back no data:

我尝试了什么，但它没有带回任何数据：

Select s.sales, c.fiscalPeriod, c.fiscalYear
from sales s
join calendar c
on s.salesDate = c.calDate
where c.fiscalYear=c.fiscalYear-1

And this:

和这个：

Select s.sales, c.fiscalPeriod, c.fiscalYear, c.fiscalYear-1 AS lastYear
from sales s
join calendar c
on s.salesDate = c.calDate
where c.fiscalYear=lastYear

As is probably obvious, I'm fairly new to SQL. Any help would be greatly appreciated.

很明显，我对SQL很新。任何帮助将不胜感激。

3 个解决方案

#1

If you only want to get last years fiscal data then probably the best way should be to set the fiscalyear as a constant in the where clause.

如果您只想获得去年的财政数据，那么最好的方法可能是将fiscalyear设置为where子句中的常量。

For example:

例如：

to_char(fiscalYear,'yyyy')='2015'

There are many date altering fuctions, search for them, they are handy.

有许多日期改变功能，搜索它们，它们很方便。

#2

If your financial year starts in march subtract two months from today and then use TRUNC to find Jan 1st of that year:

如果您的财政年度从3月开始减去两个月，然后使用TRUNC查找当年1月1日：

trunc(add_months(calendar_date,-2), 'y')

And then apply add_months:

然后应用add_months：

Select s.sales, c.fiscalPeriod, c.fiscalYear
from sales s
join calendar c
on s.salesDate = c.calDate
where c.calDate between add_months(trunc(add_months(current_date,-2), 'y'), -10)
                    and add_months(trunc(add_months(current_date,-2), 'y'), 2)-1

#3

I hope I haven't misunderstood your question, but, if the calendar table is filled up to the current date, you could use this query.

我希望我没有误解你的问题，但是，如果日历表填写到当前日期，你可以使用此查询。

  Select s.sales, c_lastyear.fiscalPeriod, c_lastyear.fiscalYear
    from sales s
         join calendar c_lastyear
           on s.salesDate = c_lastyear.calDate
         join calendar c
           on c_lastyear.fiscalYear=c.fiscalYear-1
   where c.calDate= current_date

The steps are:

步骤是：

find the current fiscal year by accessing the calendar table with the current date (the alias c)

通过使用当前日期（别名c）访问日历表来查找当前会计年度
get all the dates of the previous fiscal year (the alias c_lastyear)

获取上一个会计年度的所有日期（别名c_lastyear）
get all the sales of the previous fiscal year (the alias s)

获得上一财年的所有销售额（别名）

(the performance won't be as good as the other proposals, but this query doesn't make assumptions on the actual definition of your fiscal year)

（性能不如其他提案好，但此查询不会对您的会计年度的实际定义做出假设）

#1