daily pastebin goal
40%
SHARE
TWEET

Untitled

a guest May 16th, 2018 95 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. declare @zones table
  2. (
  3.  start datetime,
  4.  stop datetime,
  5.  diff int
  6. )
  7.  
  8. insert into @zones values ('2017-03-15', '2017-11-06', -5)
  9. insert into @zones values ('2017-11-07', '2018-03-04', -6)
  10. insert into @zones values ('2018-03-04', '2018-11-06', -5)
  11.  
  12. ;with requests as
  13. (
  14.  SELECT --dateadd(hour, diff, dateadd(day, datediff(day, 0, UtcDateTimeCreated), 0)) as forday,
  15.    dateadd(hour, diff, dateadd(hour, datediff(hour, 0, UtcDateTimeCreated), 0)) as forhour,
  16.    dateadd(hour, diff, dateadd(minute, datediff(minute, 0, UtcDateTimeCreated), 0)) as forminute,
  17.    --dateadd(hour, diff, dateadd(second, datediff(second, 0, UtcDateTimeCreated), 0)) as forsecond,
  18.    count(*) as total
  19.  FROM dbo.AuditRequestLog l
  20.  INNER JOIN @zones z ON l.UtcDateTimeCreated >= z.start and l.UtcDateTimeCreated < z.stop
  21.  WHERE datepart(weekday, UtcDateTimeCreated) not in (1, 7)
  22.  AND ControllerAction in ('Benefits/', 'Core/')
  23.  GROUP BY
  24.    --dateadd(hour, diff, dateadd(day, datediff(day, 0, UtcDateTimeCreated), 0)),
  25.    dateadd(hour, diff, dateadd(hour, datediff(hour, 0, UtcDateTimeCreated), 0)),
  26.    dateadd(hour, diff, dateadd(minute, datediff(minute, 0, UtcDateTimeCreated), 0)),
  27.    --dateadd(hour, diff, dateadd(second, datediff(second, 0, UtcDateTimeCreated), 0)),
  28.    diff
  29. )
  30. select datepart(minute, forminute),
  31.   min(total) as min_total,
  32.   avg(total) as avg_total,
  33.   max(total) as max_total
  34. from requests
  35. where datepart(hour, forhour) = 10
  36. group by
  37.   datepart(minute, forminute)
  38. order by
  39.   datepart(minute, forminute)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top