Get the difference between top and last value in result set

Question

My table looks something like

deal_id   test_value    run_date
820117648   1.2     2014-03-31
820117648   1.33    2014-04-30
820117648   1.33    2014-05-30
820117648   1.26    2014-06-30
820117648   1.11    2014-07-31
820117648   0.58    2014-09-30
820117648   1.64    2014-10-31
820117648   0.64    2014-11-28
820117648   3.65    2014-12-31
820117648   3.8     2015-03-11

820117649   0.64    2014-09-31
820117649   0.23    2014-10-31
820117649   0.64    2014-11-28
820117649   3.65    2014-12-31
820117649   3.8     2015-03-11


SELECT deal_id,test_value,run_date  FROM  ems.cdotests
  WHERE run_date >= Dateadd(month, -4 Getdate())

I am trying to grab the last 4 run_date record from the whole set and then I need to find the difference between the testValue on the first rundate of my select criteria and the last rundate i.e for deal_id 820117649 3.8 - 0.23 = 3.57 and for deal_id 820117648 it should be 3.8 -1.64 = 2.16 This is the large table with multiple deal id and several run_date associated with each dealid and may have data for the last 15 years or so Any suggestion would be really helpfull

Answer 1

So late to answer, but a way is this:

;with t as (
    select *, 
        row_number() over (partition by deal_id order by run_date desc) seq
    from ems.cdotests 
)
select 
    t1.deal_id, t1.run_date run_date1, t1.test_value test_value1, 
                t2.run_date run_date4, t2.test_value test_value4, 
    t1.test_value - t2.test_value diff
from t t1
join t t2
  on t1.deal_id = t2.deal_id
where t1.seq = 1
  and t2.seq = 4
  -- as an extra condition of those should have at least 5 records:
  and exists (select 1 from t ti where ti.deal_id = t1.deal_id and ti.seq > 5)

[SQL Fiddle]

Answer 2

Try this solution. It will not be bound to particular deal_id.

DECLARE @t TABLE ( ID INT, V MONEY, D DATE )

INSERT  INTO @t
VALUES  ( 1, 1, '20140101' ),
        ( 1, 4, '20140102' ),
        ( 1, 2, '20140103' ),
        ( 1, 7, '20140104' ),
        ( 2, 5, '20140104' ),
        ( 2, 8, '20140110' ),
        ( 2, 11, '20140105' );

WITH    cte
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        MIN(D) OVER ( PARTITION BY ID ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Min ,
                        MAX(D) OVER ( PARTITION BY ID ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Max
               FROM     @t
             )
    SELECT  c1.ID ,
            c2.V - c1.V AS V
    FROM    cte c1
            JOIN cte c2 ON c1.ID = c2.ID AND c1.D < c2.D
    WHERE   ( c1.D = c1.MIN OR c1.D = c1.MAX ) AND ( c2.D = c2.MIN OR c2.D = c2.MAX )

Output:

ID  V
1   6.00
2   3.00

You have changed you question. For latest 6 month, add filter CTE above main CTE:

;WITH    filter
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY D DESC ) AS RN
               FROM     @t
             ),
        cte
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        MIN(D) OVER ( PARTITION BY ID ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Min ,
                        MAX(D) OVER ( PARTITION BY ID ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Max
               FROM     filter
               WHERE    RN <= 6
             )
    SELECT  c1.ID ,
            c2.V - c1.V AS V
    FROM    cte c1
            JOIN cte c2 ON c1.ID = c2.ID AND c1.D < c2.D
    WHERE   ( c1.D = c1.MIN OR c1.D = c1.MAX ) 
            AND ( c2.D = c2.MIN OR c2.D = c2.MAX ) 
            AND (c2.V - c1.V > 5)

Answer 3

You can use window functions

WITH C AS(
    SELECT ROW_NUMBER() OVER (PARTITION BY deal_Id ORDER BY deal_Id) Rn
           ,deal_Id
           ,test_value
           ,run_date
    FROM ems.cdotests
)
SELECT id1 deal_id, t2 - t1 diff
FROM (SELECT c1.deal_id id1
             ,c1.test_value t1
             ,c2.deal_id id2
             , c2.test_value t2
      FROM C c1 
      INNER JOIN C c2 ON c1.deal_id = c2.deal_id
      WHERE c1.Rn = 1
      AND c2.Rn = (SELECT TOP 1 MAX(Rn) FROM C GROUP BY deal_id)
     ) t