Subtract two values in two rows based on date

• ram4tech

  Hall of Fame

  Points: 3140

  More actions

  February 13, 2007 at 5:34 am

  #117483

  Hi,

  I have data in the following format.

  Id Date Value

  1 9/01/2000 123.344

  2 9/30/2000 127.553

  3 10/01/2000 223.678

  4 10/31/2000 267.773

  I want to retrieve the following result set.

  Date Value

  09/30/2000 127.553-123.344

  10/31/2000 267.773-223.678

  Thanks for your help.

• Ken McKelvey

  SSCoach

  Points: 18938

  More actions

  February 13, 2007 at 6:19 am

  #689427

  If you only have two dates per month, something like the following should work:

  SELECT T1.[Date]

          ,T1.Value - T2.Value AS Value

  FROM YourTable T1

          JOIN YourTable T2

                  ON T2.[Date] < T1.[Date]

                           AND YEAR(T1.[Date]) = YEAR(T2.[Date])

                           AND MONTH(T1.[Date]) = MONTH(T2.[Date])