usp_Calculate_ProxySiteBandwidth_For_Date.sql

USE [IisLogs]
GO
/****** Object:  StoredProcedure [dbo].[usp_Calculate_ProxySiteBandwidth_For_Date]    Script Date: 9/2/2018 7:00:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[usp_Calculate_ProxySiteBandwidth_For_Date]
	@Date date
as

	declare		@dateStart date = @Date,
				@dateEnd date = dateadd(d, 1, @Date),
				@intervalInSeconds int = 1

	insert into dbo.ProxySiteBandwidth
	select		[date], [time], SiteName
				,(sum([sum-in-bits-per-second]) / 3600) [avg-bits-per-second]
				,convert(decimal(4,2), ((sum([sum-in-bits-per-second]) / 3600) / 2327838.72)) [lcus]
	from			(
						select		convert(date, EntryTime) [date]
									,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
									,SiteName
									,sum(convert(decimal, csBytes + scBytes) * 8) [sum-in-bits-per-second]
						from			dbo.IisLog
						where		EntryTime >= @dateStart and EntryTime < @dateEnd
						group by	convert(date, EntryTime),
									datepart(hour, EntryTime),
									SiteName,
									datepart(minute, EntryTime),
									datepart(second, EntryTime) / @intervalInSeconds
					)		f
	group by	[date], [time], SiteName