OK the loop is causing the problem.

Taking a look at the original logic, you would like to return the rows from the #SampleData table where the trandate is between a parameterised start and end dates. If no rows are returned then move the start and end dates back until rows are returned.

It is probably easier to work out the latest period for which rows would be returned using a query such as below:

select max(TranDate) as LatestDateTime

from #sampledata

where Trandate between dateadd(dd,-10,@PFromDate) and @PToDate

I have selected to move the earliest date back by 10 days just in case there is a large break between transactions.

The SSRS query would then look something like the below query to return all #sampledata rows from the last day.

select * from #sampledata

where convert(date,TranDate) =

(select convert(date,max(TranDate)) as LatestDate

from #sampledata

where Trandate between dateadd(dd,-10,@PFromDate) and @PToDate)

This would remove the requirement for the loop, the #Temp and the CTE.

Fitz