CODEWITHSUNDEEP
sqlsql-serversql-server-2008
euge1220
euge1220

Reputation: 235

Subtracting values based on date SQL

I have data that looks like this but with many more index names and index values

dt                     indx_nm      indx_val
2013-08-13 00:00:00.000 ABQI        1571.93
2013-08-12 00:00:00.000 ABQI        1568.55
2013-08-09 00:00:00.000 ABQI        1566.1
2013-08-08 00:00:00.000 ABQI        1573.98
2013-08-07 00:00:00.000 ABQI        1567.42
2013-08-06 00:00:00.000 ABQI        1579.49
2013-08-13 00:00:00.000 ACNACTR     280.3931
2013-08-12 00:00:00.000 ACNACTR     275.1943
2013-08-09 00:00:00.000 ACNACTR     268.8868
2013-08-08 00:00:00.000 ACNACTR     265.7915
2013-08-07 00:00:00.000 ACNACTR     264.8321
2013-08-13 00:00:00.000 ACNITTR     145.3845
2013-08-12 00:00:00.000 ACNITTR     144.1252
2013-08-09 00:00:00.000 ACNITTR     143.2331
2013-08-08 00:00:00.000 ACNITTR     141.4968
2013-08-07 00:00:00.000 ACNITTR     140.6079

How could I write a SQL query in order to subtract the indx_val by the indx_val of the previous date for all dates. I would like to find daily returns essentially. For example for ABQI it would be 1571.93 - 1568.55 as the return for the date 2012-08-13 and so forth. I would like for the result to have a dt column, indx_nm column, and dly_rtn column. Thank you!

Upvotes: 3

Views: 654

Answers (2)

i-one
i-one

Reputation: 5120

Try following:

;with cte as (
    select dt, indx_nm, indx_val,
        rn = row_number() over (partition by indx_nm order by dt desc)
    from TableName
)
select c1.dt, c1.indx_nm, c1.indx_val - c2.indx_val as dly_rtn
from cte c1
    left join cte c2 on c2.rn = c1.rn + 1 and c2.indx_nm = c1.indx_nm
order by c1.indx_nm, c1.dt desc

Also, if query is supposed to be run quite frequently, I would recommend having index:

create unique index IX_TableName_1
    on TableName (indx_nm, dt desc) include (indx_val)

Upvotes: 4

Sonam
Sonam

Reputation: 3466

select a.dt,a.indx_nm ,(b.indx_val-a.indx_val)dly_rtn From table1 a join 
Table1 b on a.dt=dateadd(dd,1,b.dt) and 
a.indx_nm=b.indx_nm

Upvotes: 0

Related Questions