daily pastebin goal
52%
SHARE
TWEET

Untitled

a guest May 16th, 2018 94 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
Top