使用MSAccess数据库跟踪十二个月公式

我有一个名为Cash_Flow_Statements的数据库,我需要创建一个保存的查询,计算一个追踪十二个月(TTM)的自由现金流。

我有一个名为Cash_Flow_Statements的数据库,我需要创建一个保存的查询,计算一个追踪十二个月(TTM)的自由现金流。

下面是我的数据库的一个例子:

Cash_Flow_Statement

这里是我保存的查询的代码。我已经将此代码用于其他计算,所以我知道它的工作原理。我只是不知道如何在 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 中。

UPDATE

Per @ 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)

这是结果:

enter image description here

再次,这是非常接近的,但不是我所需要的。我相信我知道是什么原因导致代码在适应时不起作用,但我不知道如何修复它。在 @ 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)

本站系公益性非盈利分享网址,本文来自用户投稿,不代表码文网立场,如若转载,请注明出处

(2)
移动网站的规格是什么 (website design specs)
上一篇
如何在不使用excel互操作库的情况下将excel工作簿转换为 pdf
下一篇

相关推荐

  • type c接口是啥改变你的充电体验

    示例示例Type C接口是一种新型的USB连接器,它可以支持更高的数据传输速度,比传统的USB 0接口更快。与传统的USB接口不同,Type C接口可以在两端都使用,无需额外的翻转,使连接更加方便快捷。下面是一个使用Type C接口的代码示例:…

    2023-01-12 02:06:09
    0 46 46
  • carwings是什么意思智能汽车连接系统的未来

    Carwings是日产汽车公司推出的一款远程智能汽车服务,它可以帮助用户远程监控汽车的状态和行驶信息,并且可以远程控制汽车的功能。…

    2023-01-19 13:55:38
    0 46 70
  • cvt变速箱油分几种:CVT变速箱油的种类及用法

    cvt变速箱油一般分为三种,分别是:传统cvt变速箱油:代码:CVT-1…

    2023-01-27 12:23:25
    0 41 95
  • cv人生履历:从零开始,一步步走向成功——[姓名]的职业生涯

    cv人生履历是一种概括性的文件,用于描述一个人的教育背景、工作经历、技能、专业知识和其他重要信息的文件。它可以帮助雇主评估求职者的资格,并决定是否面试或录用他们。…

    2023-02-01 13:25:09
    0 10 11
  • contextcapture集群:如何使用ContextCapture技术构建高效的3D集群

    ContextCapture集群是一种分布式的技术,它可以让多台服务器共享数据,从而提高应用程序的性能和可靠性。它可以使用多台服务器来收集、存储和处理数据,而不需要单独的服务器来执行这些任务。…

    2023-01-05 08:03:52
    0 13 53
  • cdb文件解锁您的数据库

    CDB文件是一种关联数据库文件格式,用于存储结构化的键/值数据。它是一种高效的文件格式,可以快速检索和更新数据。它可以用作存储用户配置文件,字典,索引,缓存等。…

    2023-02-04 07:44:32
    0 61 51
  • code128条码字体下载:使用 Code128 条码字体创建条码标识

    code128条码字体下载是指下载code128条码字体的过程。Code128条码字体是一种用于生成Code128条形码的字体。它可以将文本转换为条形码,并将条形码打印在各种类型的媒体上。要下载code128条码字体,可以从网上搜索“code128条码字体下载”,然后选择一个可靠的网站下载。一旦下载完成,就可以使用该字体创建code128条形码。…

    2023-01-19 14:44:33
    0 96 18
  • txvlog com糖心改变你的生活,让你的心情更加甜蜜!

    txvlog com糖心是一款以视频分享为主的社交APP,可以让用户上传自己的视频,并与其他用户进行互动。它的主要功能有:用户注册:用户可以通过手机号码、邮箱地址或者社交媒体账号注册,并设置自己的个人信息;…

    2023-01-10 05:08:15
    0 97 99

发表评论

登录 后才能评论

评论列表(3条)