USE [master]GOCREATE DATABASE [DeviceStats];GOALTER DATABASE [DeviceStats] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [DeviceStats] SET ANSI_NULLS OFF GOALTER DATABASE [DeviceStats] SET ANSI_PADDING OFF GOALTER DATABASE [DeviceStats] SET ANSI_WARNINGS OFF GOALTER DATABASE [DeviceStats] SET ARITHABORT OFF GOALTER DATABASE [DeviceStats] SET AUTO_CLOSE OFF GOALTER DATABASE [DeviceStats] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [DeviceStats] SET AUTO_SHRINK OFF GOALTER DATABASE [DeviceStats] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [DeviceStats] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [DeviceStats] SET CURSOR_DEFAULT  GLOBAL GOALTER DATABASE [DeviceStats] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [DeviceStats] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [DeviceStats] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [DeviceStats] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [DeviceStats] SET  ENABLE_BROKER GOALTER DATABASE [DeviceStats] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [DeviceStats] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [DeviceStats] SET TRUSTWORTHY OFF GOALTER DATABASE [DeviceStats] SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE [DeviceStats] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [DeviceStats] SET  READ_WRITE GOALTER DATABASE [DeviceStats] SET RECOVERY SIMPLE GOALTER DATABASE [DeviceStats] SET  MULTI_USER GOALTER DATABASE [DeviceStats] SET PAGE_VERIFY CHECKSUM  GOALTER DATABASE [DeviceStats] SET DB_CHAINING OFF GOUSE [DeviceStats]GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[DailyStats](    [DeviceID] [int] NOT NULL,    [Timestamp] [date] NOT NULL,    [ReceivedCount] [int] NOT NULL,    [DataErrorCount] [int] NOT NULL,    [TimeErrorCount] [int] NOT NULL,    [MinLatency] [int] NULL,    [MaxLatency] [int] NULL,    [AvgLatency] [int] NULL, CONSTRAINT [PK_DailyStats] PRIMARY KEY CLUSTERED (    [DeviceID] ASC,    [Timestamp] 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 SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Device](    [ID] [int] IDENTITY(1,1) NOT NULL,    [UniqueID] [uniqueidentifier] NOT NULL,    [Name] [varchar](200) NULL,    [NameWISP] [varchar](200) NULL,    [NamePMU] [varchar](200) NULL,    [NamePDC] [varchar](200) NULL,    [SubstationID] [int] NULL,    [Acronym] [varchar](200) NOT NULL,    [ParentAcronym] [varchar](200) NULL,    [Protocol] [varchar](200) NULL,    [Longitude] [decimal](9, 6) NULL,    [Latitude] [decimal](9, 6) NULL,    [FramesPerSecond] [int] NOT NULL, CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED (    [ID] 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 SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[MinuteStats](    [DeviceID] [int] NOT NULL,    [Timestamp] [smalldatetime] NOT NULL,    [ReceivedCount] [smallint] NOT NULL,    [DataErrorCount] [smallint] NOT NULL,    [TimeErrorCount] [smallint] NOT NULL,    [MinLatency] [int] NULL,    [MaxLatency] [int] NULL,    [AvgLatency] [int] NULL, CONSTRAINT [PK_MinuteStats14] PRIMARY KEY CLUSTERED (    [DeviceID] ASC,    [Timestamp] 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 SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[PMULines](    [NameEMS] [nvarchar](50) NOT NULL,    [NameEMSClean] [nvarchar](50) NULL,    [NameLong] [nvarchar](100) NOT NULL,    [FromSubstationID] [int] NULL,    [ToSubstationID] [int] NULL,    [NominalVoltage] [float] NOT NULL) ON [PRIMARY]GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Substation](    [ID] [int] IDENTITY(1,1) NOT NULL,    [Name] [varchar](200) NOT NULL,    [NameShort] [varchar](200) NOT NULL,    [NameEMS] [varchar](200) NOT NULL,    [PMUPrefix] [varchar](200) NOT NULL,    [Longitude] [decimal](9, 6) NOT NULL,    [Latitude] [decimal](9, 6) NOT NULL,    [HasPMUs] [bit] NOT NULL, CONSTRAINT [PK_Substation] PRIMARY KEY CLUSTERED (    [ID] 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 ALTER TABLE [dbo].[Device] ADD  CONSTRAINT [DF__Device__FramesPerSec]  DEFAULT ((30)) FOR [FramesPerSecond]GOALTER TABLE [dbo].[MinuteStats] ADD  CONSTRAINT [DF_MinuteStats_DataErrorCount]  DEFAULT ((0)) FOR [DataErrorCount] GOALTER TABLE [dbo].[MinuteStats] ADD  CONSTRAINT [DF_MinuteStats_TimeErrorCount]  DEFAULT ((0)) FOR [TimeErrorCount] GOALTER TABLE [dbo].[DailyStats]  WITH CHECK ADD  CONSTRAINT [FK_DailyStats_Device] FOREIGN KEY([DeviceID])REFERENCES [dbo].[Device] ([ID])GO ALTER TABLE [dbo].[DailyStats] CHECK CONSTRAINT [FK_DailyStats_Device]GO ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Substation] FOREIGN KEY([SubstationID])REFERENCES [dbo].[Substation] ([ID])GOALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_Substation]GO ALTER TABLE [dbo].[PMULines]  WITH CHECK ADD  CONSTRAINT [FK_PMULines__From_Substation] FOREIGN KEY([FromSubstationID])REFERENCES [dbo].[Substation] ([ID])GOALTER TABLE [dbo].[PMULines] CHECK CONSTRAINT [FK_PMULines__From_Substation]GO ALTER TABLE [dbo].[PMULines]  WITH CHECK ADD  CONSTRAINT [FK_PMULines_To_Substation] FOREIGN KEY([ToSubstationID])REFERENCES [dbo].[Substation] ([ID])GO ALTER TABLE [dbo].[PMULines] CHECK CONSTRAINT [FK_PMULines_To_Substation]GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO /********************************//* STEP 3                       *//* Create new stored procedures *//********************************/CREATE PROCEDURE [dbo].[InsertMissingMinuteStats]ASBEGIN    DECLARE @StartTimestamp SMALLDATETIME;    DECLARE @EndTimestamp SMALLDATETIME;     SELECT @StartTimeStamp = MIN(Timestamp) FROM MinuteStats;    SELECT @EndTimestamp = DATEADD(MINUTE, -2, GETDATE());     ;WITH MinutesCTE (Timestamp) AS    (        SELECT @StartTimestamp UNION ALL        SELECT DateAdd(MINUTE, 1, Timestamp)        FROM   MinutesCTE WHERE Timestamp < @EndTimestamp    )    INSERT MinuteStats (DeviceID, Timestamp, ReceivedCount, DataErrorCount, TimeErrorCount)    SELECT d.ID, m.Timestamp, 0, 0, 0    FROM MinutesCTE m    JOIN Device d ON 1=1    LEFT OUTER JOIN MinuteStats mm ON mm.DeviceID=d.ID AND mm.Timestamp=m.Timestamp    WHERE m.Timestamp < @EndTimestamp AND mm.DeviceID IS NULL    ORDER BY 1,2    OPTION  (MaxRecursion 0)ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[QueryPDCDailyStats] (@PDC varchar(20), @FromMS BIGINT, @ToMS BIGINT)ASBEGIN    DECLARE @DeviceID int;     SELECT @DeviceID=MIN(ID) FROM Device WHERE COALESCE(ParentAcronym, Acronym) = @PDC;     SELECT DATEDIFF(SECOND, '1970-01-01', [Timestamp]) AS time        , (ds.ReceivedCount / (d.FramesPerSecond * 86400.0) * 100.0) as value        , @PDC as metric    FROM  [DailyStats] ds    JOIN [Device] d on d.ID=ds.DeviceID    WHERE [DeviceID] = @DeviceID      AND DATEDIFF(SECOND, '1970-01-01', [Timestamp]) BETWEEN @FromMS/1000 AND @ToMS/1000    ORDER BY [Timestamp];ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[QueryPDCMinuteStats] (@PDC varchar(20), @From DATETIME, @To DATETIME)ASBEGIN    DECLARE @DeviceID int;     SELECT @DeviceID=MIN(ID) FROM Device WHERE COALESCE(ParentAcronym,Acronym) = @PDC;     SELECT DATEDIFF(second, '1970-01-01', [Timestamp]) AS time, [ReceivedCount] as value, @PDC as metric    FROM  [MinuteStats]    WHERE [DeviceID] = @DeviceID      AND [Timestamp] BETWEEN @From AND @To    ORDER BY [Timestamp];ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[QueryPDCNames] ASBEGIN    SET NOCOUNT ON;     -- Find all PDCs with valid DailyStats data in the last month    SELECT DISTINCT COALESCE(ParentAcronym, Acronym) PDC    FROM Device d    JOIN DailyStats s on s.DeviceID=d.ID    WHERE [Timestamp]>DATEADD(MONTH, -1, CONVERT(date,GETUTCDATE()))    ORDER BY 1ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[QueryPMUDataErrors] (@ToMS BIGINT, @NumMinutes INT, @Filter varchar(max), @Substation varchar(max))ASBEGIN    DECLARE @Last SMALLDATETIME;     DECLARE @To SMALLDATETIME;    DECLARE @From SMALLDATETIME;     SELECT @Last=MAX(Timestamp) FROM MinuteStats WHERE AvgLatency IS NOT NULL;    SELECT @To=DATEADD(s, @ToMS/1000, '1970-01-01');     IF (@To > @Last) SELECT @To=@LAST     SELECT @From=DATEADD(MINUTE, -@NumMinutes, @To)     SELECT COALESCE(Acronym, ParentAcronym) PMU,        MAX(Timestamp) LastTimestamp,        AVG(ReceivedCount) ReceivedCount,        AVG(DataErrorCount) DataErrorCount,        AVG(TimeErrorCount) TimeErrorCount,        MAX(s.Name) Substation        FROM MinuteStats ms        JOIN Device d on d.ID=ms.DeviceID        JOIN Substation s on s.ID=d.SubstationID        WHERE [Timestamp] BETWEEN @From AND DATEADD(MINUTE, -1, @To)        AND (@Filter='all' OR DataErrorCount>0)        AND (@Substation='' OR s.Name=@Substation)        GROUP BY COALESCE(Acronym, ParentAcronym)        ORDER BY 1, 2ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[QuerySubstationNames] ASBEGIN    SET NOCOUNT ON;     -- Find all Substations with valid DailyStats data in the last month    SELECT DISTINCT s.Name AS Substation --COALESCE(ParentAcronym, Acronym) PDC    FROM Device d    JOIN DailyStats ds on ds.DeviceID=d.ID    JOIN Substation s on s.ID=d.SubstationID    WHERE [Timestamp]>DATEADD(MONTH, -1, CONVERT(date,GETUTCDATE()))    ORDER BY 1ENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO CREATE PROCEDURE [dbo].[UpdateDailyDeviceStats]ASBEGIN    SET NOCOUNT ON;    SET ANSI_WARNINGS OFF     DELETE FROM dbo.MinuteStats WHERE Timestamp < DATEADD(YEAR, -1, GETDATE());     -- Insert dummy (empty) missing MinuteStats records for any active devices on days with zero records in MinuteStats    EXEC [dbo].[InsertMissingMinuteStats];     INSERT INTO DailyStats (DeviceID, Timestamp, ReceivedCount, DataErrorCount, TimeErrorCount, MaxLatency, MinLatency, AvgLatency)         SELECT m.DeviceID,         CONVERT(date, m.Timestamp),         SUM(m.[ReceivedCount]),         SUM(m.[DataErrorCount]),        SUM(m.[TimeErrorCount]),        MAX(m.[MaxLatency]),        MIN(m.[MinLatency]),        CAST(AVG(CAST(m.[AvgLatency] AS FLOAT)) AS INT)        FROM MinuteStats m        LEFT OUTER JOIN DailyStats dd on dd.DeviceID=m.DeviceID AND dd.Timestamp=CONVERT(date, m.Timestamp)        WHERE CONVERT(date, m.Timestamp) < CONVERT(date, getdate()) AND dd.DeviceID IS NULL        GROUP BY m.DeviceID, CONVERT(date, m.Timestamp);     SET ANSI_WARNINGS ONENDGO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[DailyStatsDetail]ASSELECT dbo.Device.Acronym AS PMU, dbo.Device.ParentAcronym AS PDC, dbo.DailyStats.ReceivedCount, dbo.DailyStats.DataErrorCount, dbo.DailyStats.TimeErrorCountFROM   dbo.DailyStats INNER JOIN       dbo.Device ON dbo.DailyStats.DeviceID = dbo.Device.IDWHERE  dbo.DailyStats.Timestamp = (SELECT MAX(Timestamp) AS MaxTime FROM dbo.DailyStats)GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[MinuteStatsDetail]ASSELECT D.Acronym AS PMU, D.ParentAcronym AS PDC, MS.ReceivedCount AS Received, MS.DataErrorCount AS DataErrors, MS.TimeErrorCount AS TimeErrorsFROM   dbo.MinuteStats AS MS INNER JOIN       dbo.Device AS D ON MS.DeviceID = D.IDWHERE MS.Timestamp = (SELECT MAX(Timestamp) AS MaxTime FROM dbo.MinuteStats)