检索前一行以按日期从当前行中减去 [英] Retrieve previous row to subtract from current row by date

问题描述

我有一个只有 2 个字段的表:NAV_Date和NetAssetValue.

I have a table with only 2 fields: NAV_Date and NetAssetValue.

NAV_Date是一季度的最后天.

+----------+--------------+
|NAV_Date  |NetAssetValue |
+----------+--------------+
|12/31/2012|        $4,000|
+----------+--------------+
|03/31/2013|        $5,000|
+----------+--------------+

我正在尝试编写一个从前一个查询中减去最近查询的查询.当前，我的查询只是选择表中的所有值.我知道要执行此操作，每次都必须检索以前的记录，但是我不确定该怎么做.

I am attempting to write a query that subtracts the more recent from the previous. Currently, my query simply selects all values within the table. I know to perform this I would have to retrieve the prior record each time through, however I'm not sure how to do that.

最终我将使用((CurrentValue/PreviousValue)-1)并将值存储为百分比以返回差值.

Ultimately I will use ((CurrentValue/PreviousValue) - 1) and store the value as a percent to return the difference.

我还将针对年度值执行此操作.如果有人做过类似的事情或为我起步提供了线索，有人能举个例子吗?

I will also be performing this for yearly values. Does anyone have an example if they did something similar or perhaps a clue to get me started?

我问的原因是因为我搜索了一个解决方案，但是找不到没有自动编号或ID字段的任何有用示例.

The reason I am asking is because I searched for a solution but could not find any useful examples where there was not an autonumber or an ID field.

推荐答案

您可以使用相关子查询来做到这一点:

You can do this using correlated subqueries:

select NAV_date, NAV_value, (NAV_value / prev_value) - 1
from (select t.*,
             (select top 1 NAV_value
              from YOURTABLENAMEGOESHERE as t2
              where t2.NAV_date < t.NAV_date
              order by t2.NAV_date desc
             ) as prev_value
      from YOURTABLENAMEGOESHERE as t
     ) as t

这篇关于检索前一行以按日期从当前行中减去的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！