CODEWITHSUNDEEP
sql-servert-sql
Powellellogram
Powellellogram

Reputation: 436

SELECT Average Count of IIS method calls per second

I'm trying to find out the average count of IIS calls per second (GET, POST, PUT, etc) in a time span of one week

Here's an example of a SELECT I'd use to get total counts in a week:

  SELECT Method, COUNT(*) 
      AS TotalCalls
    FROM IISLog
   WHERE dDate 
 BETWEEN '2018-05-10' 
     AND '2018-05-17'
GROUP BY Method

How can I granularise this SELECT to get an average count of Method calls per second in this date range?

Expected output for calls per second:

Method    CallsPerSecond
Get       15
Put       10
Post      14
Delete    12

The number counts are just example estimates

Upvotes: 1

Views: 263

Answers (1)

EzLo
EzLo

Reputation: 14209

Just divide by the amount of seconds that compose your filter ranges.

DECLARE @StartDate DATETIME = '2018-05-10'
DECLARE @EndDate DATETIME = '2018-05-17'

SELECT 
    Method, 
    CallsPerSecond = COUNT(*) / NULLIF(DATEDIFF(SECOND, @StartDate, @EndDate), 0),
    TotalCalls = COUNT(*)
FROM 
    IISLog AS I
WHERE 
    I.dDate BETWEEN @StartDate AND @EndDate
GROUP BY 
    I.Method

Upvotes: 2

Related Questions