Hello I'm a newbie in sql and I received the following question: we need a Top 100/month on the column "Size" and the average on the same column "Size" but on all entries for that month. How can I do that within the same query?
I also have to run this query for every month since the db is in use. How can I do that without changing the date range each time.
asked Jul 11 '11 at 05:52 AM in Default
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'
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
answered Jul 12 '11 at 12:50 AM
I guess we cant select both average (size) and top 100 size in the same query.But you could do something like this.
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.