1

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

NAV_Date is the last day of a quarter. 

+----------+--------------+
|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.

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?

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
Mark C.
  • 6,332
  • 4
  • 35
  • 71

2 Answers2

3

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I'm replacing: "t" with my table name, value with my value field, and date with my date field, is that right? What about t2? (I'm still trying to learn SQL...) Thanks!! – Mark C. Jan 21 '14 at 14:11
  • In Access SQL, you must include `AS` when you alias a field expression. Add it here: `) AS prev_value from YOURTABLENAMEGOESHERE` – HansUp Jan 21 '14 at 17:31
  • 1
    Also both date and value are reserved words. Rename the fields if possible. If not, enclose them in square brackets or qualify them with the table alias everywhere those names appear in the query. – HansUp Jan 21 '14 at 17:32
  • 1
    `select [date], [value], ([value] / prev_value) - 1 from (select t.*, (select top 1 [value] from YourTable t2 where t2.date < t.date order by t2.date desc) AS prev_value from YourTable t) t;` – HansUp Jan 21 '14 at 18:06
  • I'm getting hung up on the reference to my date and value fields. Where should I be putting the real names of the colums. NAV_Date and NetAssetValue? @HansUp – Mark C. Jan 21 '14 at 18:10
  • The SQL from my last comment was tested in Access 2007 with a table named `YourTable` which includes fields named `Date` and `Value`. The query ran without error. If you actually have different field names than those in your question, substitute those names. – HansUp Jan 21 '14 at 18:15
  • Without you guys I would be screwed. Thanks, @HansUp. Sorry if I was difficult? It seems to be working perfectly – Mark C. Jan 21 '14 at 18:19
0

I think you wanted something like this, using MySQL variables. Also, it seemed like you wanted to do a percent change calculation, but your formula is wrong. If not, let me know and this can get tweaked to precisely what you need:

SELECT
    nt.NAV_Date,
    nt.NetAssetValue,
    (@last_value := (SELECT NetAssetValue FROM NAV_Tbl WHERE NAV_Date < nt.NAV_Date ORDER BY NAV_Date DESC LIMIT 1)) AS last_value,
    IF(@last_value,(((nt.NetAssetValue - @last_value) / @last_value) * 100),NULL) AS percent_change
FROM
    NAV_Tbl nt,
    (SELECT
        @row_num:=0,
        @last_value:=0
    ) r
ORDER BY
    NAV_Date ASC

Result (2 left columns were original data, 2 right columns were calculated via query):

==============================================
| date       | value | last_value | percent  |
==============================================
| 2012-10-01 | 3500  | NULL       | NULL     |
| 2013-01-01 | 4500  | 3500       | 28.5714  |
| 2013-04-01 | 1000  | 4500       | -77.7778 |
| 2013-07-01 | 2500  | 1000       | 150.0000 |
| 2013-10-01 | 9000  | 2500       | 260.0000 |
==============================================
omgitsfletch
  • 469
  • 1
  • 4
  • 15
  • Thanks, I'll give it a shot and let you know my results. – Mark C. Jan 21 '14 at 14:12
  • Ok, great. Remember that `quarterly` should be changed to your table name (leave all the qs, or change them consistently). And I'm assuming your column names are `value` and `date`, if they are something else they need to be changed accordingly! Let me know if you have any problems. – omgitsfletch Jan 21 '14 at 16:58
  • I'm getting a syntax error right here: SELECT NetAssetValue FROM NAV_Tbl WHERE date < q.NAV_Date ORDER BY date DESC LIMIT 1) [This is MS Access, so I removed the MySQL syntax, I think] – Mark C. Jan 21 '14 at 17:18
  • So the fields are NAV_Date, NetAssetValue, and the table is `NAV_Tbl`? I'll modify my original query, and then we can work out if there are any kinks from MySQL vs SQL Server. – omgitsfletch Jan 21 '14 at 17:22
  • Okay, changed them. This works in MySQL. If it doesn't work in SQL Server let me know the exact error and we can try and tweak it. – omgitsfletch Jan 21 '14 at 17:28
  • I appreciate it! Still receiving the same error on that SELECT statement. "Syntax error in query expression '(last_value = (SELECT NetAssetValue FROM NAV_Tbl WHERE NAV_Date < nt.NAV_Date ORDER BY NAV_Date DESC LIMIT 1))' is exactly what the error says if it helps. – Mark C. Jan 21 '14 at 17:31
  • Also, I don't see any of the special characters related to variables in that error...I can't recall if it's a different syntax in SQL Server. One thing I know for sure is you need `SELECT TOP 1 NetAssetValue FROM NAV_Tbl WHERE NAV_Date < nt.NAV_Date ORDER BY NAV_Date DESC` instead of `SELECT NetAssetValue FROM NAV_Tbl WHERE NAV_Date < nt.NAV_Date ORDER BY NAV_Date DESC LIMIT 1`, limit syntax is different. – omgitsfletch Jan 21 '14 at 17:39
  • Well, thanks for your assistance, @omgitsfletch! I truly appreciate it. I will try to figure it out using your logic. – Mark C. Jan 21 '14 at 18:02