根据FiscaL年份和期间选择字段

Question

我正在使用的表有一个金额字段、一个会计年度和一个期间字段(以及其他字段)。我需要根据会计年度和期间字段选择两次金额字段。我遇到的问题是如何编写SQL来选择2014财年的记录和之前金额的第10期。我还需要确保它在2014 / 01和之前的期间是2013 / 12。谢谢您的帮助......

SELECT 
a.business_unit,
a.amount as CURRENT_AMOUNT,
a.amount as PRIOR_AMOUNT,
a.fiscal_year as FY,
a.period as AP

FROM Asset_Depreciation a

WHERE
a.business_unit = 'A0001'
AND a.fiscal_year = 2014
AND a.period = 11

RESULTS A0001 500.00 (空白)201411

Answer 1

您可以使用lag()解析函数来查找之前的amount。您可以使用子查询在where筛选器之前应用lag()：

select  prevamount
,       business_unit
,       curamount
,       fiscal_year
,       period
from    (
        select  lag(amount) over (
                    partition by business_unit
                    order by fiscal_year, period) as prevamount
        ,       business_unit
        ,       amount as curamount
        ,       fiscal_year
        ,       period
        from    Asset_Depreciation
        ) SubQueryAlias
where   business_unit = 'A0001'
        and fiscal_year = 2014
        and period = 11

如果没有子查询，lag()将只查看一行，而不会找到任何以前的值。

Working example at SQL Fiddle.