Guest User

Untitled

a guest
May 16th, 2018
124
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.46 KB | None | 0 0
  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)
Add Comment
Please, Sign In to add comment