/*insert into bfm values(1,1,1,25,1,'1/10/2013')insert into bfm values(2,2,1,25,1,'1/27/2013')insert into bfm values(3,3,1,50,1,'3/01/2013')insert into bfm values(5,5,5,400,2,'01/20/2013')

*/

Below is query that I am trying but want to select different data:declare @runDate dateset @runDate = '6/11/2013'

SELECT c.c_id,f.e_dt,@runDate as RunDate,bfm.alloc_dt,bfm.amt as match,f.amt_gross as gross,case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) elsedatediff(day,f.e_dt,@runDate) end as Days

SELECT * FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY c.c_id Order by COALESCE(alloc_dt, '1/1/3000'),
(case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end) DESC) as RN, c.c_id,
f.e_dt,@runDate as RunDate,bfm.alloc_dt,COALESCE(bfm.amt, 0) as match,COALESCE(f.amt_gross, 0) as gross,
case when bfm.amt is not null then datediff(day,bf.actual_dt,@runDate) else
datediff(day,f.e_dt,@runDate) end as Days
from @c c
inner join f f on c.c_id = f.c_id
left join bfm bfm on bfm.f_id = f.F_id
left join bf bf on bf.bf_id = bfm.bf_id
where f.e_Dt <= @runDate and (COALESCE(bfm.amt, 0) <> COALESCE(f.amt_gross, 0))) A
WHERE RN = 1 order by c_id, days desc