sql compute difference between 2 rows

Question

I'm looking for a methodology to compare the difference between 2 rows in the same table. From what I found here (How to get difference between two rows for a column field?) it's almost what I wanted. I have done the following code:

create table #tmpTest
(
    id_fund int null,
    id_ShareType int null,
    ValueDate datetime null,
    VarNAV float null,
    FundPerf float null,
)

insert into #tmpTest(id_fund, id_ShareType, ValueDate, VarNAV)
values(1,1,'20140101',100)
insert into #tmpTest(id_fund, id_ShareType, ValueDate, VarNAV) 
values(1,1,'20140102',20)

update #tmpTest
set hrc.FundPerf = (isnull(hrn.VarNAV, 0) - hrc.VarNAV)/hrc.VarNAV
from #tmpTest hrc 
left join #tmpTest hrn on hrn.ValueDate = (select min(ValueDate) from #tmpTest where ValueDate > hrc.ValueDate)
and hrc.id_fund = hrn.id_fund and hrc.id_ShareType = hrn.id_ShareType

My issue is that the result I'm computing starts on line 1 instead of line 2.

Hereunder the result I'm obtaining:

id_fund id_ShareType ValueDate           VarNAV                       FundPerf                     
------- ------------ ------------------- ------- -----------------------------
      1            1 2014-01-01 00:00:00     100                          -0.8
      1            1 2014-01-02 00:00:00      20                            -1

whereas I'd like it to be that way:

id_fund id_ShareType ValueDate           VarNAV                       FundPerf                     
------- ------------ ------------------- ------- -----------------------------
      1            1 2014-01-01 00:00:00     100                            -1
      1            1 2014-01-02 00:00:00      20                          -0.8

What's wrong with my approach?

Why can't you use `LAG` and `LEAD` functions? – Kermit Dec 16 '14 at 14:29 — Kermit, Dec 16 '14 at 14:29

score 1 · Answer 1 · answered Dec 16 '14 at 14:27

You are not restricting the minimum to the same fund and share type.

update #tmpTest
    set hrc.FundPerf = (isnull(hrn.VarNAV, 0) - hrc.VarNAV)/hrc.VarNAV
    from #tmpTest hrc left join
         #tmpTest hrn
         on hrn.ValueDate = (select min(ValueDate)
                             from #tmpTest tt
                             where tt.ValueDate > hrc.ValueDate and
                                   hrc.id_fund = tt.id_fund and hrc.id_ShareType = tt.id_ShareType 
                            ) and
           hrc.id_fund = hrn.id_fund and hrc.id_ShareType = hrn.id_ShareType ;

score 0 · Answer 2 · answered Dec 16 '14 at 14:34

0

Try this:

update hrn
set FundPerf = (isnull(hrn.VarNAV, 0) - hrc.VarNAV)/hrc.VarNAV
from #tmpTest hrc 
left join #tmpTest hrn on hrn.ValueDate = (select min(ValueDate) from #tmpTest where ValueDate > hrc.ValueDate)
and hrc.id_fund = hrn.id_fund and hrc.id_ShareType = hrn.id_ShareType

answered Dec 16 '14 at 14:34

Russop

36
3

Also, restrict the minimum to fund and share type as suggested by @Gordon Linoff – Russop Dec 16 '14 at 14:37
Indeed you're correct: I should restrict. Nevertheless, this doesn't solve my issue. – sa6 Dec 16 '14 at 14:42
It does. If you update hrn instead of hrc, returns are aligned with dates (performance for the first day is NULL). – Russop Dec 16 '14 at 14:46

Dadasaheb Karande · Answer 3 · 2014-12-16T14:49:25.467

Hi you can achieve this using by CTE (Common Table Expression)

create table #tmpTest
(
    id_fund int null,
    id_ShareType int null,
    ValueDate datetime null,
    VarNAV float null,
    FundPerf float null,
)

insert into #tmpTest(id_fund, id_ShareType, ValueDate, VarNAV)
values(1,1,'20140101',100)
insert into #tmpTest(id_fund, id_ShareType, ValueDate, VarNAV) 
values(1,1,'20140102',20)

;With tbl as

( Select Row_Number() OVER (Order by T.ValueDate) as RowNumber,* From #tmpTest T )SELECT Cur.*,(ISNULL(Cur.VarNAV,0) - ISNULL(Prv.VarNAV,0))/Prv.VarNAV as [Col Name] FROM tbl Cur LEFT OUTER JOIN tbl Prv ON Cur.RowNumber = Prv.RowNumber+1 ORDER BY Cur.ValueDate

Highlighting your code and pressing Ctrl+K will format it, making it easier to read - see [how to format here](http://meta.stackexchange.com/a/22189) — AHiggins, Dec 16 '14 at 14:43

sql compute difference between 2 rows

3 Answers3