My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Also we would like to know if you would be running the report once a day, week , month? It seems to me that at any point you execute the report you would like to see the aggregates for that day, that week and that month for the given agent. In theory it looks like you need three record sets, one for each aggregation, and then join them together by the agent ID. You need to start with a Calendar table. Please follow the advise of the previous posts so you can be better guided.

-- for joining below cte statements ,DATEPART(wk, Timecreated) wk -- get the number of the week ,DATEPART(mm, Timecreated) mm -- get the number of the month

,ISNULL(SUM(amount),0) AS DailyAmount -- daily sums to be grouped on

FROM #agent -- source table (temp table in this case)

-- assums the time portion of the date field is not used (all zero's, ex: 2012-08-01 00:00:00.000) -- filter on date parameters WHERE timecreated >= @startdate AND timecreated <= @enddate

-- group the sum on these fields GROUP BY [state] ,AgentName ,Agenttype ,Timecreated), weeklySumsAS ( -- weekly amounts (if spanning multiple years this needs further refinement) SELECT [state] ,AgentName ,Agenttype ,DATEPART(wk, Timecreated) weeknumber -- get the number of the week

,ISNULL(SUM(amount),0) AS WeeklyAmount -- weekly sums

FROM #agent -- source table (temp table in this case)

-- assums the time portion of the date field is not used (all zero's, ex: 2012-08-01 00:00:00.000) -- filter on date parameters WHERE timecreated >= @startdate AND timecreated <= @enddate

-- group the sum on these fields GROUP BY [state] ,AgentName ,Agenttype

---- join each cte statement to get summed values daily, weekly, monthly---- NOTE: this report / query really does not make much sense---- but gives an idea (one out of many) of how this might be doneSELECT d.[state] ,d.AgentName ,d.Agenttype ,d.Timecreated ,d.wk ,d.mm

,d.DailyAmount -- daily sums ,w.WeeklyAmount -- weekly sums (does not make sense to include with daily sums ???? ) ,m.MonthlyAmount -- monthly sums (does not make sense to included with daily and weekly sums ???? )