在同一个select语句中的两个日期/期间和每个期间的返回数据

Question

好的，我有一段代码可以根据位置，操作，异常和最终消费获取与美元视图相关的数据。这由FYPeriod完成。可以说，这返回FYPeriod 11702这是2016年8月。我也希望它在同一行FYPeriod 11602是2015年8月返回。我尝试添加另一个连接，只是做一个（FYPeriod - 100），但当然这没有返回。这是返回FYPeriod的工作代码。

SELECT dbo.fiscalmonthyearstring(f.FYPeriod) As MonthYear, f.FYPeriod, c.SYSTEMPLANT, e.SpendingAcct, 
c.DOLLARS, f.Data AS Units, d.exceptions, f.PLActivity 
    FROM [vw_TotalDollars] c 
    LEFT JOIN [dbo].[location] b ON b.PlantId = c.SYSTEMPLANT 
    LEFT JOIN [Operations] f ON f.FYPeriod = c.PST 
AND f.location = b.location 
    JOIN [dbo].[aexceptions] d ON b.PlantId = d.Id 
AND d.acc = c.ACCN 
AND exceptions IN ('Z2', 'X2' , 'Z7' , 'X7') 
    LEFT JOIN [dbo].[Spending] e ON e.SpendingId = d.SpendingId 
    WHERE f.PLActivity = 'Total Produced' 

我希望我已经解释正确，如果没有，我会尽力这样做。

Answer 1

假设所有的过滤是必要的也得到了前一年的数据，我们可以在一个common table expression包你当前的查询，以便我们可以引用它反对它，像这样：

;with cte as (
    select 
     o.fyperiod 
    , td.systemplant 
    , s.SpendingAcct 
    , td.dollars 
    , o.[Data] as Units 
    , ae.exceptions 
    , o.plactivity 
    from [vw_TotalDollars] td 
    inner join [Operations] o 
     on o.fyperiod = td.pst 
    and o.plactivity = 'Total Produced' 
    left join [dbo].[location] l 
     on l.PlantId = td.systemplant 
    and l.location = o.location 
    left join [dbo].[aexceptions] ae 
     on ae.Id = td.systemplant 
    and ae.acc = td.accn 
    and ae.exceptions in ('Z2','X2','Z7','X7') 
    left join [dbo].[Spending] s 
     on s.SpendingId = ae.SpendingId 
) 
select 
    cte.FyPeriod 
    --, MonthYear = dbo.fiscalmonthyearstring(cte.fyperiod) 
    , cte.SystemPlan 
    , cte.SpendingAcct 
    , cte.Dollars 
    , cte.Units 
    , cte.Exceptions 
    , cte.Plactivity 
    --, pyMonthYear = dbo.fiscalmonthyearstring(py.fyperiod) 
    , pyDollars = py.Dollars 
    , pyUnits  = py.Units 
from cte 
    left join cte as py 
    on py.fyperiod  = cte.fyperiod-100 
    and py.systemplant = cte.systemplant 
    and py.exceptions = cte.exceptions 
    and py.SpendingAcct = cte.SpendingAcct 

注意：如果重写为内联表值函数，那么标量函数dbo.fiscalmonthyearstring()的性能会更好。

内联表值函数VS标量函数引用：

• When is a SQL function not a function? "If it’s not inline, it’s rubbish." - Rob Farley
• Inline Scalar Functions - Itzik Ben-Gan
• Scalar functions, inlining, and performance: An entertaining title for a boring post - Adam Machanic