USE [master]
GO


CREATE DATABASE [DCS] ON  PRIMARY 
( NAME = N'DCS', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DCS.mdf' , SIZE = 11264KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DCS_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DCS_log.ldf' , SIZE = 76736KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_Pref_CP850_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'DCS', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DCS].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [DCS] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [DCS] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [DCS] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [DCS] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [DCS] SET ARITHABORT OFF 
GO
ALTER DATABASE [DCS] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [DCS] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [DCS] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [DCS] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [DCS] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [DCS] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [DCS] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [DCS] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [DCS] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [DCS] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [DCS] SET  ENABLE_BROKER 
GO
ALTER DATABASE [DCS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [DCS] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [DCS] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [DCS] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [DCS] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [DCS] SET  READ_WRITE 
GO
ALTER DATABASE [DCS] SET RECOVERY FULL 
GO
ALTER DATABASE [DCS] SET  MULTI_USER 
GO
ALTER DATABASE [DCS] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [DCS] SET DB_CHAINING OFF 
GO

USE [DCS]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SR3600DataSeries]') AND type in (N'U'))
DROP TABLE [dbo].[SR3600DataSeries]
GO

CREATE TABLE [DCS].[dbo].[SR3600DataSeries]
(
	 [series_id] int identity
	,[system_name] nvarchar(1024) not null
     ,[start_date] datetime not null
     ,[end_date] datetime null
     ,[calibration_factor] numeric(6,2) not null
     ,[channel_name] nvarchar(1024) not null
     ,[property_id] int
     ,[property_description] nvarchar(1024) 
     ,[data_points] int null
	 ,CONSTRAINT [PK_SR3600DataSeries] PRIMARY KEY CLUSTERED 
	 (
		[series_id] ASC
	 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
);
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SR3600Data]') AND type in (N'U'))
DROP TABLE [dbo].[SR3600Data]
GO

CREATE TABLE [DCS].[dbo].[SR3600Data]
(
	 [data_id] int identity
	,[series_id] int not null
	,[dcs_time] datetime not null
	,[dcs_speed_mph] numeric(6,1)
	,[dcs_speed_kph] numeric(6,1)
	,[comment] nvarchar(256) 
	,CONSTRAINT [PK_SR3600Data] PRIMARY KEY CLUSTERED 
	(	
		[data_id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
);

GO


CREATE NONCLUSTERED INDEX [IX_SR3600Data_Time] ON [dbo].[SR3600Data] 
(
	[dcs_time] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
GO

USE [DCS]
GO

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SR3600_fn_MPHAvgTimeBands]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SR3600_fn_MPHAvgTimeBands]
GO

/*	Test: 
	use dcs
	go
	declare @start datetime
	set @start = getdate();
	
	select * from [SR3600_fn_MPHAvgTimeBands] (@start);
*/

CREATE FUNCTION [dbo].[SR3600_fn_MPHAvgTimeBands]
(
	@theDate datetime
)
RETURNS TABLE
AS
RETURN
(
		select 
			convert(datetime, convert(varchar(10), @theDate, 101)) as [theDate],
			pvt.*
		from
		(
			select 
				datepart(hh, dcs_time) as hour,
				dcs_speed_mph
			from 
				[dbo].[SR3600Data]
			where 
				dcs_time between convert(datetime, convert(varchar(10), @theDate, 101))  and convert(datetime, convert(varchar(10), @theDate, 101)) + 1
		) p
		pivot
		(
			avg (dcs_speed_mph)
			FOR hour IN
			( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15], [16], [17], [18], [19],[20], [21], [22], [23])
		) AS pvt
)

GO

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SR3600_fn_MPHSpeedBands]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SR3600_fn_MPHSpeedBands]
GO


/*	Test: 
	use dcs
	go
	declare @start datetime
	set @start = getdate();
	
	select * from [SR3600_fn_MPHSpeedBands] (@start);
*/

CREATE FUNCTION [dbo].[SR3600_fn_MPHSpeedBands]
(
	@theDate datetime
)
RETURNS TABLE
AS
RETURN
(
		select 
			convert(datetime, convert(varchar(10), @theDate, 101)) as [theDate],
			pvt.*
		from
		(
			select 
				convert(int, round(dcs_speed_mph, 0)/5) as speed_band
			from 
				[dbo].[SR3600Data]
			where 
				dcs_time between convert(datetime, convert(varchar(10), @theDate, 101))  and convert(datetime, convert(varchar(10), @theDate, 101)) + 1
		) p
		pivot
		(
			COUNT (speed_band)
			FOR speed_band IN
			( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14])
		) AS pvt
)
GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SR3600_fn_MPHStatistics]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SR3600_fn_MPHStatistics]
GO

/*	Test: 
	declare @start datetime, @end datetime;
	declare @limit float;
	set @start = getdate() - 1;
	set @end = getdate();
	set @limit = 30;
	
	select * from [SR3600_fn_MPHStatistics] (@start, @end, @limit);
*/

CREATE FUNCTION [dbo].[SR3600_fn_MPHStatistics]
(
	@lowerLimit datetime,
	@upperlimit	datetime,
	@speedlimit	float = 30
)
RETURNS TABLE
AS
RETURN
(
	select 
	@lowerLimit as [begin_period],
	@upperLimit as [end_period],
	@speedlimit as [speed_limit],
	(
		select 
			count(*)
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
	) as [count_all],
	(
		select 
			count(*)
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
			and [dcs_speed_mph] > @speedlimit
	) as [count_speeders],
	(
		select 
			count(*)
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
			and [dcs_speed_mph] > @speedlimit + 10
	) as [count_10mph_speeders],
	(
		select 
			count(*)
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
			and [dcs_speed_mph] > @speedlimit + 20
	) as [count_20mph_speeders],
	(
		select 
			count(*)
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
			and [dcs_speed_mph] > @speedlimit
	) * 100.0
	/
	(
		select 
			count(*)
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
	) as [pct_speeders],
	(
		select 
			avg([dcs_speed_mph]-@speedlimit)
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
			and [dcs_speed_mph] > @speedlimit
	) as [average_speed_above_limit],
	(
		select 
			avg([dcs_speed_mph]-@speedlimit)/@speedlimit*100
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
			and [dcs_speed_mph] > @speedlimit
	) as [average_speed_pct_above_limit],
	(
		select 
			avg([dcs_speed_mph])
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
			and [dcs_speed_mph] > 0
	) as [average_speed],
	(
		select 
			max([dcs_speed_mph])
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] between @lowerLimit and @upperLimit
	) as [max_speed],
	(
		select 
			[dcs_speed_mph]
		from 
			[dbo].[SR3600Data]
		where
			[dcs_time] = (select max([dcs_time]) from [dbo].[SR3600Data])
	) as [last_speed],
	(
		select 
			max([dcs_speed_mph])
		from 
			[dbo].[SR3600Data]
	) as [all_time_max_speed],
	(
		select 
			count(*)
		from 
			[dbo].[SR3600Data]
		where 
			[dcs_time] between @lowerLimit and @upperLimit
			and [dcs_speed_mph] = -1
	) as [bad_readings]
)
GO



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SR3600_fn_MPHTimeBands]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[SR3600_fn_MPHTimeBands]
GO

/*	Test: 
	declare @start datetime
	set @start = getdate();
	
	select * from [SR3600_fn_MPHTimeBands] (@start);
*/

CREATE FUNCTION [dbo].[SR3600_fn_MPHTimeBands]
(
	@theDate datetime
)
RETURNS TABLE
AS
RETURN
(
		select 
			convert(datetime, convert(varchar(10), @theDate, 101)) as [theDate],
			pvt.*
		from
		(
			select 
				datepart(hh, dcs_time) as hour,
				dcs_speed_mph
			from 
				[dbo].[SR3600Data]
			where 
				dcs_time between convert(datetime, convert(varchar(10), @theDate, 101))  and convert(datetime, convert(varchar(10), @theDate, 101)) + 1
		) p
		pivot
		(
			COUNT (dcs_speed_mph)
			FOR hour IN
			( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15], [16], [17], [18], [19],[20], [21], [22], [23])
		) AS pvt
)
GO