sql server 2008 - tsql - Can i do this in a CASE statement? -

i have 2 results sets shown below:

@rounding

portfolioid duration aaa     -0.1 

@finaloutput

reportingdate   fundcode    sector      rank    duration    weight 31/07/2013      aaa         sector1     1       0           33.5 31/07/2013      aaa         sector2     2       0.9         29.6 31/07/2013      aaa         sector3     3       0.6         17.3 31/07/2013      aaa         sector4     4       0.8         11.8 31/07/2013      aaa         sector5     5       0.1         3.1 31/07/2013      aaa         sector6     6       0.1         1.3 31/07/2013      aaa         sector7     7       0           0.4 31/07/2013      aaa         sector8     8       -0.9        0 31/07/2013      aaa         sector9     11      0           -1.3 31/07/2013      aaa         sector10    100     0           2.8 31/07/2013      aaa         sector11    101     0           1.5 31/07/2013      aaa         total       102     1.6         100 

what need able subtract duration in @rounding rank 1 , rank 102 durations, if rank 1 "sector1" need subtracted rank 2. possible in case statement? i've created below can't think how cater case "sector1" never subtracted upon if rank 1?

    select          reportingdate     ,   fundcode     ,   sector     ,   [rank]      ,   case             when [rank] in (1,102) [duration contribution] - rd1.duration             else [duration contribution]          end     [duration contribution]      ,   case              when [rank] in (1,102) percentage - rd.[weight]             else percentage          end     percentage      cte cte          inner join @roundingdifference rd  -- portfolio weight rounding             on rd.portfolioid = cte.fundcode          inner join @roundingdifferencedur rd1 -- duration contribution rounding             on rd1.portfolioid = cte.fundcode      (percentage <> 0.0     or [duration contribution] <> 0.0)      order    reportingdate             ,   fundcode             ,   [rank] 

so result i'm looking sector 1 duration still = 0 sector 2 duration , total 1.0 , 1.7 respectively.

not possible sql know when dealing row n in set whether or not found particular value in row n-1. means can't tell special rank 2 record if rank 1 record had specific sector value.

you could, though, use subquery check if set has row rank 1 , has sector1.

you'd need detailed case statement , performance bad if dataset large.

select     case         when rank = 102             [duration contribution] - rd1.duration         when rank = 1 , sector != 'sector1'             [duration contribution] - rd1.duration         when rank = 2 , exists (select * cte rank = 1 , sector = 'sector1')             [duration contribution - rd1.duration         else [duration contribution]     end