我有一个名为Cash_Flow_Statements的数据库，我需要创建一个保存的查询，计算一个追踪十二个月（TTM）的自由现金流。
是我的数据库的一个例子：
这里是我保存的查询的代码。我已经将此代码用于其他计算,所以我知道它的工作原理。我只是不知道如何在 SQL 中做一个 Trailing Twelve Month。
CREATE PROC " & Calculation & " AS _
SELECT Income_Statements.Ticker, Income_Statements.[Year], Income_Statements.Period, _
" & Formula & " AS TTM _
FROM (Income_Statements AS Income_Statements INNER JOIN Balance_Sheets AS Balance_Sheets ON (Income_Statements.Ticker = Balance_Sheets.Ticker) AND (Income_Statements.[Year] = Balance_Sheets.[Year]) AND (Income_Statements.Period = Balance_Sheets.Period)) _
INNER JOIN Cash_Flow_Statements AS Cash_Flow_Statements ON (Balance_Sheets.Ticker = Cash_Flow_Statements.Ticker) AND (Balance_Sheets.[Year] = Cash_Flow_Statements.[Year]) AND (Balance_Sheets.Period = Cash_Flow_Statements.Period)
我需要变量Formula来包含将计算自由现金流的拖尾十二个月的方程。
自由现金流 TTM = Sum (经营现金流 MRQ1 + 经营现金流 MRQ2 + 经营现金流 MRQ3 + 经营现金流 MRQ4)-Sum (资本支出 MRQ1 + 资本支出 MRQ2 + 资本支出 MRQ3 + 资本支出 MRQ4)
MRQ = 最近季度
我需要这个公式在 SQL 中。
UPDATEPer @ Gord Thompson 的回答我想出了这个代码：
SELECT Ticker, [Year], Period, (SELECT (SUM(su.Net_Cash_Flow_Operating) - SUM(su.Capital_Expenditures)) _
FROM (SELECT Ticker, [Year], Period, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements _
UNION ALL _
SELECT Ticker, [Year] + 1, Period - 4, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements) su _
WHERE su.Ticker = s.Ticker AND su.[Year] = s.[Year] AND (su.Period Between s.Period - 3 And s.Period)) _
AS SalesLast12Months _
FROM Cash_Flow_Statements AS s
但是正如我在我的原始帖子中提到的，我需要能够设置变量Formula以包含 SQL 语句，然后将其插入到现有的 SQL 语句中。
所以我试图适应 @ Gord Thompson 的答案，我已经提出了这个。
公式:
Formula = “(SELECT (SUM(su.Net_Cash_Flow_Operating) - SUM(su.Capital_Expenditures)) _
FROM (SELECT Ticker, [Year], Period, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements UNION ALL SELECT Ticker, [Year] + 1, Period - 4, Net_Cash_Flow_Operating, Capital_Expenditures _
FROM Cash_Flow_Statements) su _
WHERE su.Ticker = Cash_Flow_Statements.Ticker AND su.[Year] = Cash_Flow_Statements.[Year] AND (su.Period Between Cash_Flow_Statements.Period - 3 And Cash_Flow_Statements.Period))”
SQL 语句:
"CREATE PROC " & Calculation & " AS SELECT Income_Statements.Ticker, Income_Statements.[Year], Income_Statements.Period, " & Formula & " AS TTM _
FROM (Income_Statements AS Income_Statements _
INNER JOIN Balance_Sheets AS Balance_Sheets ON (Income_Statements.Ticker = Balance_Sheets.Ticker) AND (Income_Statements.[Year] = Balance_Sheets.[Year]) AND (Income_Statements.Period = Balance_Sheets.Period)) _
INNER JOIN Cash_Flow_Statements AS Cash_Flow_Statements ON (Balance_Sheets.Ticker = Cash_Flow_Statements.Ticker) AND (Balance_Sheets.[Year] = Cash_Flow_Statements.[Year]) AND (Balance_Sheets.Period = Cash_Flow_Statements.Period)
这是结果：
再次，这是非常接近的，但不是我所需要的。我相信我知道是什么原因导致代码在适应时不起作用，但我不知道如何修复它。在 @ Gord Thompson 代码中，最后一行是FROM Cash_Flow_Statements AS s。当我将变量公式插入到我原来的帖子中的现有 SQL 语句中时，代码是FROM (Income_Statements AS Income_Statements _ INNER JOIN Balance_Sheets AS Balance_Sheets ON (Income_Statements.Ticker = Balance_Sheets.Ticker) AND (Income_Statements.[Year] = Balance_Sheets.[Year]) AND (Income_Statements.Period = Balance_Sheets.Period)) _ INNER JOIN Cash_Flow_Statements AS Cash_Flow_Statements ON (Balance_Sheets.Ticker = Cash_Flow_Statements.Ticker) AND (Balance_Sheets.[Year] = Cash_Flow_Statements.[Year]) AND (Balance_Sheets.Period = Cash_Flow_Statements.Period)。
2
让我们不要太纠结于列，让我们集中精力选择合适的行。
假设我们有一个名为 [SalesSummary] 的表，其中包含
Item FiscalYear Quarter TotalSales
-------- ---------- ------- ----------
bicycles 2011 1 100
bicycles 2011 2 200
bicycles 2011 3 300
bicycles 2011 4 400
bicycles 2012 1 500
bicycles 2012 2 600
bicycles 2012 3 700
bicycles 2012 4 800
ham 2011 1 10
ham 2011 2 20
ham 2011 3 30
ham 2011 4 40
ham 2012 1 50
ham 2012 2 60
ham 2012 3 70
ham 2012 4 80
如果我们想创建一个查询，以显示过去 12 个月（当前季度加上前 3 个季度）的季度数据以及总销售额，则可能会认为我们必须开始使用IIf()来处理从一个 FiscalYear 到下一个 FiscalYear 的“环绕”。幸运的是，我们没有，因为我们可以将相同的数据与下一个 FiscalYear_2012 的“假
SELECT
Item,
FiscalYear,
Quarter,
TotalSales
FROM SalesSummary
UNION ALL
SELECT
Item,
FiscalYear + 1,
Quarter - 4,
TotalSales
FROM SalesSummary
返回
Item FiscalYear Quarter TotalSales
-------- ---------- ------- ----------
bicycles 2011 1 100
bicycles 2011 2 200
bicycles 2011 3 300
bicycles 2011 4 400
bicycles 2012 1 500
bicycles 2012 2 600
bicycles 2012 3 700
bicycles 2012 4 800
ham 2011 1 10
ham 2011 2 20
ham 2011 3 30
ham 2011 4 40
ham 2012 1 50
ham 2012 2 60
ham 2012 3 70
ham 2012 4 80
bicycles 2012 -3 100
bicycles 2012 -2 200
bicycles 2012 -1 300
bicycles 2012 0 400
bicycles 2013 -3 500
bicycles 2013 -2 600
bicycles 2013 -1 700
bicycles 2013 0 800
ham 2012 -3 10
ham 2012 -2 20
ham 2012 -1 30
ham 2012 0 40
ham 2013 -3 50
ham 2013 -2 60
ham 2013 -1 70
ham 2013 0 80
如果我们将该查询保存为 [SalesUnion]，那么我们可以使用它来生成我们的总数，如下所示
SELECT
Item,
FiscalYear,
Quarter,
TotalSales,
(
SELECT
SUM(su.TotalSales)
FROM SalesUnion su
WHERE su.Item = s.Item
AND su.FiscalYear = s.FiscalYear
AND (su.Quarter Between s.Quarter - 3 And s.Quarter)
) AS SalesLast12Months
FROM SalesSummary s
或者，如果你不想依靠一个保存的查询，你可以做这一切在一个去：
SELECT
Item,
FiscalYear,
Quarter,
TotalSales,
(
SELECT
SUM(su.TotalSales)
FROM
(
SELECT
Item,
FiscalYear,
Quarter,
TotalSales
FROM SalesSummary
UNION ALL
SELECT
Item,
FiscalYear + 1,
Quarter - 4,
TotalSales
FROM SalesSummary
) su
WHERE su.Item = s.Item
AND su.FiscalYear = s.FiscalYear
AND (su.Quarter Between s.Quarter - 3 And s.Quarter)
) AS SalesLast12Months
FROM SalesSummary s
无论哪种方式，结果是
Item FiscalYear Quarter TotalSales SalesLast12Months
-------- ---------- ------- ---------- -----------------
bicycles 2011 1 100 100
bicycles 2011 2 200 300
bicycles 2011 3 300 600
bicycles 2011 4 400 1000
bicycles 2012 1 500 1400
bicycles 2012 2 600 1800
bicycles 2012 3 700 2200
bicycles 2012 4 800 2600
ham 2011 1 10 10
ham 2011 2 20 30
ham 2011 3 30 60
ham 2011 4 40 100
ham 2012 1 50 140
ham 2012 2 60 180
ham 2012 3 70 220
ham 2012 4 80 260
0
变量Formula应包含以下内容：
SELECT (SUM(su.Net_Cash_Flow_Operating) - SUM(su.Capital_Expenditures)) FROM (SELECT Ticker, [Year], Period, Net_Cash_Flow_Operating, Capital_Expenditures FROM Cash_Flow_Statements UNION ALL SELECT Ticker, [Year] + 1, Period - 4, Net_Cash_Flow_Operating, Capital_Expenditures FROM Cash_Flow_Statements) su WHERE su.Ticker = c.Ticker AND su.[Year] = c.[Year] AND (su.Period Between c.Period - 3 And c.Period)
本站系公益性非盈利分享网址，本文来自用户投稿，不代表码文网立场，如若转载，请注明出处
评论列表（27条）