Something to help get you started. One of many ways including numbers/tally tables.

--DROP TABLE #Agent

-- create a temp table

CREATE TABLE #Agent ([state] varchar(50)

,AgentName varchar(50)

,Agenttype varchar(50)

,Timecreated datetime

,amount int

)

-- insert data into temp table

INSERT INTO #Agent

SELECT 'Kolkata', '000123AA', 'Distributor', '2012-08-01', 0.00

UNION ALL

SELECT'Kolkata', '000123AA', 'Distributor', '2012-08-01', 2.00

UNION ALL

SELECT'Kolkata', '000123AA', 'Distributor', '2012-08-21', 15.00

UNION ALL

SELECT'Kolkata', '000123AA', 'Distributor', '2012-09-01', 20.00

UNION ALL

SELECT 'Kolkata','000555VB','Distributor','2012-09-04', 10.00

UNION ALL

SELECT 'Kolkata','000555VB','Distributor','2012-09-05', 10.00

UNION ALL

SELECT 'mumbai','000567AD','Agent','2012-09-05', 100.00

UNION ALL

select 'mumbai','000567AD','Agent','2012-09-05', 200.00

UNION ALL

SELECT 'mumbai','000567AD','Agent','2012-10-05', 50.00

-- declare parameters

DECLARE @startdate DATETIME

DECLARE @enddate DATETIME

-- set date range in parameters

SET @startdate= '20120801'

SET @enddate = '20121005'

---- possible dates in temp table (use for parameters above)

----2012-08-01

----2012-08-21

----2012-09-01

----2012-09-04

----2012-09-05

----2012-09-05

----2012-10-05

-- create cte's (common table expression) to hold calculated values for days, weeks, months

-- does not account for different years for date parameters, use DATEPART with year

;WITH dailySums

AS (

-- daily amounts

SELECT[state]

,AgentName

,Agenttype

,Timecreated

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

WHEREtimecreated >= @startdate

ANDtimecreated <= @enddate

-- group the sum on these fields

GROUP BY [state]

,AgentName

,Agenttype

,Timecreated

),

weeklySums

AS (

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

WHEREtimecreated >= @startdate

ANDtimecreated <= @enddate

-- group the sum on these fields

GROUP BY [state]

,AgentName

,Agenttype

,DATEPART(wk, Timecreated)

),

monthlySums

AS (

-- monthly amounts (if spanning multiple years this needs further refinement)

SELECT[state]

,AgentName

,Agenttype

,DATEPART(mm, Timecreated) monthnumber -- get the number of the month

,ISNULL(SUM(amount),0) AS MonthlyAmount

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

WHEREtimecreated >= @startdate

ANDtimecreated <= @enddate

-- group the sum on these fields

GROUP BY [state]

,AgentName

,Agenttype

,DATEPART(mm, Timecreated)

)

-- NOTE: only one cte statement can be executed at a time, comment all others

-- test each individually

/* daily cte statment */

--SELECT[state], AgentName, Agenttype, Timecreated, wk, mm, DailyAmount

--FROMdailySums

/* weekly cte statement */

--SELECT[state], AgentName, Agenttype, weeknumber, WeeklyAmount

--FROMweeklySums

/* monthly cte statement */

--SELECT[state], AgentName, Agenttype, monthnumber, MonthlyAmount

--FROMmonthlySums

---- 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 done

SELECTd.[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 ???? )

FROMdailySums d-- first cte statement

INNER JOINweeklySums w-- second cte statement

ONd.[state] = w.[state]

ANDd.AgentName = w.AgentName

ANDd.Agenttype = w.Agenttype

ANDd.wk = w.weeknumber-- number value of the week

INNER JOINmonthlySums m-- third cte statement

ONd.[state] = m.[state]

ANDd.AgentName = m.AgentName

ANDd.Agenttype = m.Agenttype

ANDd.mm = m.monthnumber-- number value of the year