Generate-ProxyWebAppCounts.sql

USE [IisLogs]
GO

/****** Object:  Table [dbo].[ProxyWebAppCounts]    Script Date: 9/2/2018 6:54:29 PM ******/
CREATE TABLE [dbo].[ProxyWebAppCounts](
	[SiteName] [varchar](255) NOT NULL,
	[WebAppCount] [int] NOT NULL,
 CONSTRAINT [PK_ProxyWebAppCounts] PRIMARY KEY CLUSTERED 
(
	[SiteName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

## generate the data

declare	@dateStart varchar(10) = '2018-08-18',
        @dateEnd varchar(10) = '2018-08-19',
        @includeAll bit = 1

insert into dbo.ProxySiteRequestCounts
select		convert(date, EntryTime) [date]
			,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
			,SiteName
			,count(*) [RequestCounts]
from			dbo.IisLog
where		(EntryTime >= @dateStart and EntryTime < @dateEnd)
	or		@includeAll = 1
group by	convert(date, EntryTime),
			datepart(hour, EntryTime),
			SiteName
order by	[date], [time], SiteName;