CODEWITHSUNDEEP
sqlreporting-services
Tsukasa
Tsukasa

Reputation: 6552

Expression to get average rows per minute from existing dataset

I have a table driven off a dataset. The dataset contains a datetime column. I have a textbox that I would like to display an Average of the rows per minute.

Trying to figure out the expression I can use that will do this without have to write another long running SQL query because the query in the dataset already takes some time to run.

I know yell at me for not providing code but i'm not sure how to go about this lol.

Upvotes: 0

Views: 116

Answers (2)

lee87
lee87

Reputation: 70

Derive a table with the grouped counts, and get the average from that:

SELECT AVG(MinCount)
FROM (
    SELECT COUNT(*) as MinCount
    FROM MyTable
    GROUP BY DATEPART(minute,MyDate)) as DerivedTable

Replace MyTable and MyDate with your table and datetime field respectively.

Upvotes: 1

Chris Latta
Chris Latta

Reputation: 20560

The number of rows divided by the difference in minutes between the minimum and maximum dates:

=Count(Fields!MyDate.Value, "MyDataset") / DateDiff(DateInterval.Minute, Min(Fields!MyDate.Value, "MyDataset"), Max(Fields!MyDate.Value, "MyDataset"))

Upvotes: 2

Related Questions