Hi

You could try the following. The results of the MAX aggregates need to be cast to varchar so that they can be provided in the same column.

declare @runDate date

set @runDate = '6/11/2013';

with originalQuery as (

select c.c_id,c.p_id,

f.e_dt,@runDate as RunDate,f.f_id,f.amt f_amt,

bf.actual_dt,bf.bf_id,bf.c_id as bfC_id,

bfm.bfm_id,bfm.f_id as bfmF_id,bfm.amt bfm_amt,bfm.alloc_dt,

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 #cc

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

)

select c_id,

coalesce(cast(max(alloc_dt) as varchar(20)), cast(max(days) as varchar(20))) as maxValue,

case when max(alloc_dt) is null then 'Days' else 'Date' end as maxValueOf

from originalQuery

group by c_id