CODEWITHSUNDEEP
sqlsql-server
tpandas
tpandas

Reputation: 61

query performance issue for finding elapsed time between failures

I'm writing code to pull out a TTR (Time to Repair) metric for our components. The data is given as below as a list of the location name, datetime, and status codes. The code is meant to log the time elapsed from when a component fails to when it's repaired, as long as it's been over 24 hours (to filter out planned maintenance).

enter image description here

Here is the code I have below. The subquery part seems to be working, and it seems to work if I just have the (seqnum - seqnum_2) in the groupby. The problem is that it returns nothing if I group by Sitename as well, which I need to do to pull location names into the data. What am I doing wrong in getting the location names in?

SELECT t.sitename
       ,
       Min(recorddatetime) AS 'CompFailStart',
       Max(recorddatetime) AS 'CompFailEnd',
       Datediff(second, Min(recorddatetime), Max(recorddatetime)) / (
       24.0 * 60 * 60 )
                           AS decimal_days
FROM   (SELECT sitename,
               R.recorddatetime,
               e.stringval,
               c.pdc_a_stateid,
               Row_number()
                 OVER (
                   partition BY c.pdc_a_stateid
                   ORDER BY recorddatetime) AS seqnum_2,
               Row_number()
                 OVER (
                   ORDER BY recorddatetime) AS seqnum
        FROM   record r WITH(nolock)
               JOIN compressor c WITH(nolock)
                 ON r.recordid = c.recordid
               JOIN site s WITH(nolock)
                 ON r.siteid = s.siteid
               JOIN enummap e WITH(nolock)
                 ON c.pdc_a_stateid = e.enummapid
        WHERE  r.recorddatetime > Dateadd(yy, -1, Getdate())) t
WHERE  t.stringval LIKE '%Shutdown%'
GROUP  BY ( seqnum - seqnum_2 )
HAVING Max(recorddatetime) > Dateadd(day, 1, Min(recorddatetime))

Upvotes: 1

Views: 36

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

I suspect that your code should look like this:

SELECT t.sitename,
       Min(recorddatetime) AS CompFailStart,
       Max(recorddatetime) AS CompFailEnd,
       Datediff(second, Min(recorddatetime), Max(recorddatetime)) / (
       24.0 * 60 * 60 ) AS decimal_days
FROM   (SELECT . . . 
               Row_number() OVER (partition BY sitename, c.pdc_a_stateid ORDER BY recorddatetime) AS seqnum_2,
               Row_number() OVER (partition by sitename ORDER BY recorddatetime) AS seqnum
        FROM . . . 
        WHERE  r.recorddatetime > Dateadd(yy, -1, Getdate())
       ) t
WHERE t.stringval LIKE '%Shutdown%'
GROUP BY sitename, ( seqnum - seqnum_2 )
HAVING Max(recorddatetime) > Dateadd(day, 1, Min(recorddatetime)) ;

Note the changes to the GROUP BY and PARTITION BY clauses.

Upvotes: 3

Related Questions