--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
		WHERE	timecreated >= @startdate
			AND	timecreated <= @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
	WHERE	timecreated >= @startdate
		AND	timecreated <= @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
	WHERE	timecreated >= @startdate
		AND	timecreated <= @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 
--FROM	dailySums	

/* weekly cte statement */
--SELECT	[state], AgentName, Agenttype, weeknumber, WeeklyAmount
--FROM	weeklySums

/* monthly cte statement */
--SELECT	[state], AgentName, Agenttype, monthnumber, MonthlyAmount
--FROM	monthlySums



---- 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
SELECT	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 ???? )
				
FROM	dailySums d							-- first cte statement

	INNER JOIN	weeklySums w				-- second cte statement
		ON		d.[state] = w.[state]
		AND		d.AgentName = w.AgentName
		AND		d.Agenttype = w.Agenttype
		
		AND		d.wk = w.weeknumber			-- number value of the week

	INNER JOIN	monthlySums m				-- third cte statement
		ON		d.[state] = m.[state]
		AND		d.AgentName = m.AgentName
		AND		d.Agenttype = m.Agenttype
		
		AND		d.mm = m.monthnumber		-- number value of the year