usp_Calculate_ProxySiteNewConnections_For_Date.sql

USE [IisLogs]
GO
/****** Object:  StoredProcedure [dbo].[usp_Calculate_ProxySiteNewConnections_For_Date]    Script Date: 9/2/2018 7:01:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

	declare	@dateStart varchar(10) = @Date,
			@dateEnd varchar(10) = dateadd(d, 1, @Date),
			@includeAll bit = 0,
			@intervalInSeconds int = 1

	insert into dbo.ProxySiteNewConnections
	select		[date], [time], [SiteName]
				,sum([connections]) [total-connections]
				,((sum([connections]) * 1.0) / 3600) [connections-per-second]
				,convert(decimal(4,2), (((sum([connections]) * 1.0) / 3600) / 25.0)) [lcus]
	from		(
		select		[date], [time], [SiteName], [type], count(*) [connections]
		from			(
							select		convert(date, EntryTime) [date]
										,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
										,SiteName
										,min(EntryTime) [Start]
										,cIp
										,'not services conns' [type]
										,count(*) [cnt]
							from			dbo.IisLog
							where		(
												EntryTime >= @dateStart and EntryTime < @dateEnd
											or	@includeAll = 1
										)
								and		not (
												csUriStem like '/webservices/%'
											or	(
														SiteName in ('blue.sa.ucsb.edu', 'isis.sa.ucsb.edu')
													and	csUriStem like '/services/%'
												)
										)
							group by	convert(date, EntryTime),
										datepart(hour, EntryTime),
										[SiteName],
										datepart(minute, EntryTime),
										datepart(second, EntryTime) / @intervalInSeconds,
								cIp
						)		f
		group by	[date], [time], [SiteName], [type]

		union

		select		convert(date, EntryTime) [date]
					,format(datepart(hour, EntryTime), '0#') + ':00:00' [time]
					,SiteName
					,'services conns' [type]
					,count(*) [sum]
		from			dbo.IisLog
		where		(
							EntryTime >= @dateStart and EntryTime < @dateEnd
						or	@includeAll = 1
					)
			and		(
							csUriStem like '/webservices/%'
						or	(
									SiteName in ('blue.sa.ucsb.edu', 'isis.sa.ucsb.edu')
								and	csUriStem like '/services/%'
							)
					)
		group by	convert(date, EntryTime),
					datepart(hour, EntryTime),
					[SiteName]
	)		d
	group by	[date], [time], SiteName;