Select and average on same table

Hello Mrs_Fatherjack (and everyone else)

This is what I have done so far with the result for a specific month:

declare @dateinf char(10) declare @datesup char(10)

set @dateinf = '2010-11-01' set @datesup = '2010-12-01'

SELECT TOP 100 [content_size] AS 'Size' ,[object_id] AS 'Document ID' ,CONVERT(varchar(10), create_date, 103) AS 'Creation Date'

FROM [DB1].[dbo].[Test]

WHERE [Create_Date] >= @dateinf and [Create_Date] < @datesup

Order by content_size DESC

The goal is to get this result on monthly bases, by means of a SSIS package, together with the average of "content_size" for every entry in that column (not only for the TOP 100).

@ Kannan: I will try your solution this afternoon and let you know about the result.

Grtz and thnx to all for the reply

Hi ,

I guess we cant select both average (size) and top 100 size in the same query.But you could do something like this.

--Average size of the month    
declare @average int     
SELECT  @average =  AVG(size) from dbo.Test where month = datename(month, getdate())

-- top 100 sizes of the month along with average    
select TOP 100 SIZE , @average from dbo.Test  

In order to execute the query every month. You need to create a sql server job with this query and schedule it every month.

Hope this was useful.

Thanks,
Kannan