CODEWITHSUNDEEP
sqlsql-serverreporting-services
Lyle
Lyle

Reputation: 421

trying to calculate peak average response time for a day? Can this be done in a sql statemen or do I need to use a stored procedure?

looking to get the peak average time for a response time, so far this is the query:

SELECT DATEPART(Year, TransactionDate) AS ReportYear
     , DATEPART(Month, TransactionDate) AS ReportMonth
     , DATEPART(wk, TransactionDate) AS ReportWeek
     , DATEPART(Day, TransactionDate) AS ReportDay
     , DATEPART(Hour, TransactionDate) AS ReportHour
     , ProcessName
     , ResponseTime
FROM PerformanceLog
GROUP BY    ProcessName
          , DATEPART(Year, TransactionDate)
          , DATEPART(Month, TransactionDate)
          , DATEPART(wk, TransactionDate)
          , DATEPART(Day, TransactionDate)
          , DATEPART(Hour, TransactionDate)
          ,  ResponseTime
ORDER BY   ReportWeek, ReportDay, ProcessName, ReportHour, ResponseTime

Upvotes: 1

Views: 683

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32402

The query below will return the maximum response time per hour per process name. If you want to include ties, use rank() instead of row_number().

SELECT * FROM (
    SELECT DATEPART(Year, TransactionDate) AS ReportYear
        , DATEPART(Month, TransactionDate) AS ReportMonth
        , DATEPART(wk, TransactionDate) AS ReportWeek
        , DATEPART(Day, TransactionDate) AS ReportDay
        , DATEPART(Hour, TransactionDate) AS ReportHour
        , ProcessName
        , ResponseTime
        , row_number() over (partition by DATEPART(Year, TransactionDate)
            , DATEPART(Month, TransactionDate)
            , DATEPART(wk, TransactionDate)
            , DATEPART(Day, TransactionDate) 
            , DATEPART(Hour, TransactionDate)
            , ProcessName
            order by ResponseTime desc) rn
    FROM PerformanceLog
) t1 WHERE rn = 1

Upvotes: 1

Related Questions