Good day. There is the following table:

BAL_DATE LS_VH BS_SIGN LS_NOM 20160930 0.25 A 20621 20160930 1247.7 L 20648 20161004 0.27 A 20621 20160930 3254.4 A 20624 20161004 1987.7 L 20648 20161004 3254.3 A 20624

Explanations to the table: LS_NOM is the account number, LS_VH is the account balance, BAL_DATE - as of what date the values ​​are indicated.

It is necessary to calculate how the balance of each account has changed from the previous date. Like that:

  BAL_DATE LS_VH BS_SIGN LS_NOM Difference 20160930 0.25 A 20621 0.00 20160930 1247.7 L 20648 0.00 20161004 0.27 A 20621 0.02 20160930 3254.4 A 20624 0.00 20161004 1987.7 L 20648 740.00 20161004 3254.3 A 20624 -0.01

I tried to make the next request, but it does not work correctly, because it considers only the difference between the lines and does not take into account the account number.

  SELECT [current].BAL_DATE, [current].LS_VH, [current].BS_SIGN, ISNULL([next].LS_VH, 0) - [current].LS_VH AS Difference, [current].LS_NOM FROM SQL_LP AS [current] LEFT OUTER JOIN SQL_LP AS [next] ON [next].LS_VH = (SELECT MIN(LS_VH) FROM SQL_LP WHERE (LS_VH > [current].LS_VH))

I would be grateful if you tell me how you can implement such a query (MS SQL Server 2012). Thank!

    1 answer 1

    In SQL Server 2012, there is a wonderful window function LAG () which gives just the previous value of the required column.

     SELECT [current].BAL_DATE, [current].LS_VH, [current].BS_SIGN, lag(LS_VH,1,0) over(partition by LS_NOM order by BAL_DATE) - [current].LS_VH AS Difference, [current].LS_NOM FROM SQL_LP AS [current]