Thanks for quick reply!

But I need both columns i.e. one for alloc_date and other for Days.

so output should be like:

c_id alloc_dt Days

1 2013-03-01 132 (days for maximum date)

2 2013-01-20 172 (days corresponding to maximum date for c_id 2)

3 NULL 71 (max days because all rows are null)

Please let me know if I am not clear.

Thanks,

Surinder

EDIT:

those results are expected for

set @runDate = '7/11/2013'