Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- declare @zones table
- (
- start datetime,
- stop datetime,
- diff int
- )
- insert into @zones values ('2017-03-15', '2017-11-06', -5)
- insert into @zones values ('2017-11-07', '2018-03-04', -6)
- insert into @zones values ('2018-03-04', '2018-11-06', -5)
- ;with requests as
- (
- SELECT --dateadd(hour, diff, dateadd(day, datediff(day, 0, UtcDateTimeCreated), 0)) as forday,
- dateadd(hour, diff, dateadd(hour, datediff(hour, 0, UtcDateTimeCreated), 0)) as forhour,
- dateadd(hour, diff, dateadd(minute, datediff(minute, 0, UtcDateTimeCreated), 0)) as forminute,
- --dateadd(hour, diff, dateadd(second, datediff(second, 0, UtcDateTimeCreated), 0)) as forsecond,
- count(*) as total
- FROM dbo.AuditRequestLog l
- INNER JOIN @zones z ON l.UtcDateTimeCreated >= z.start and l.UtcDateTimeCreated < z.stop
- WHERE datepart(weekday, UtcDateTimeCreated) not in (1, 7)
- AND ControllerAction in ('Benefits/', 'Core/')
- GROUP BY
- --dateadd(hour, diff, dateadd(day, datediff(day, 0, UtcDateTimeCreated), 0)),
- dateadd(hour, diff, dateadd(hour, datediff(hour, 0, UtcDateTimeCreated), 0)),
- dateadd(hour, diff, dateadd(minute, datediff(minute, 0, UtcDateTimeCreated), 0)),
- --dateadd(hour, diff, dateadd(second, datediff(second, 0, UtcDateTimeCreated), 0)),
- diff
- )
- select datepart(minute, forminute),
- min(total) as min_total,
- avg(total) as avg_total,
- max(total) as max_total
- from requests
- where datepart(hour, forhour) = 10
- group by
- datepart(minute, forminute)
- order by
- datepart(minute, forminute)
Add Comment
Please, Sign In to add comment