Running Delta value on isolated rows - SQL Server 2008

Question

I have below table with columns Position_Date, Deal_Nr and Market Value. Now I want to create a fourth column which calculates the Delta of Market Value between 2 days on every single Deal. For example Deal nr 100, MV 14/9 = 500. MV 13/9 = 600. 500-600 = -100...

I know how to do this if I sum and group on position date but is there a way to calculate the delta isolated on every deal without making case by and putting deal_nr as condition? I have like 100 different deal_nr and new deals will come so I want the query to be static.

      Position_date |Deal_Nr|   Market Value    |   Delta Market Value
        2016-09-14  |   100 |   500             |   -100
        2016-09-14  |   101 |   1000            |   200
        2016-09-14  |   102 |   120             |   -30
        2016-09-14  |   103 |   400             |   -40
        2016-09-13  |   100 |   600             |   -300
        2016-09-13  |   101 |   800             |   100
        2016-09-13  |   102 |   150             |   -150
        2016-09-13  |   103 |   440             |   240
        2016-09-12  |   100 |   900             |   N/A
        2016-09-12  |   101 |   700             |   N/A
        2016-09-12  |   102 |   300             |   N/A
        2016-09-12  |   103 |   200             |   N/A

If I were to calculate the aggregated delta, grouped on position date, the following works.

          Select  
        Position_date,
        Market_Value,
        Delta_MV = sum(Market_value) - (select sum(Market_value) from t1 
                                       where position_Date = a.position_date -1 
                                       Group by position_date)

        from t1 as a
        Group by position_date   

Answer 1

You should use LEFT JOIN as the below:

    DECLARE @Tbl TABLE (Position_date DATETIME, MarketValue INT,  Deal_Nr INT)
INSERT INTO @Tbl
VALUES 
('2016-09-14', 500 ,100  ),
('2016-09-14', 1000,101  ),
('2016-09-14', 120 ,102  ),
('2016-09-14', 400 ,103  ),
('2016-09-13', 600 ,100  ),
('2016-09-13', 800 ,101  ),
('2016-09-13', 150 ,102  ),
('2016-09-13', 440 ,103  ),
('2016-09-12', 900 ,100   ),
('2016-09-12', 700 ,101   ),
('2016-09-12', 300 ,102   ),
('2016-09-12', 200 ,103   )

SELECT
    A.Position_date,
    A.MarketValue,
    A.MarketValue - B.MarketValue AS DeltaMarketValue       
FROM
    @Tbl A LEFT JOIN 
    @Tbl B ON A.Deal_Nr = B.Deal_Nr AND 
              A.Position_date <> B.Position_date AND
              DATEADD(DAY, -1, A.Position_date) = B.Position_date

Result:

Position_date   MarketValue DeltaMarketValue
--------------- ----------- ----------------
2016-09-14          500         -100
2016-09-14          1000        200
2016-09-14          120         -30
2016-09-14          400         -40
2016-09-13          600         -300
2016-09-13          800         100
2016-09-13          150         -150
2016-09-13          440         240
2016-09-12          900         NULL
2016-09-12          700         NULL
2016-09-12          300         NULL
2016-09-12          200         NULL