Last active
August 3, 2023 20:06
-
-
Save IDisposable/c81197333f57687322a8b2726280370d to your computer and use it in GitHub Desktop.
Sometimes you have to break out the sledge hammer...
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Creates the event reporting rollup tables and configures the triggers | |
SET ANSI_NULLS ON | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[EventAccumHourly]( | |
[SliceDate] DATETIME NOT NULL CONSTRAINT [CK_EventAccumHourly_SliceDate] CHECK ([SliceDate]=DATEADD(HOUR,DATEDIFF(HOUR,0,[SliceDate]),0)) | |
,[ClientId] BIGINT NOT NULL | |
,[SiteId] BIGINT NOT NULL | |
,[AlertId] BIGINT NOT NULL | |
,[Events_1] INT NOT NULL | |
,[Events_2] INT NOT NULL | |
,[Events_3] INT NOT NULL | |
,[Events_4] INT NOT NULL | |
,[Events_5] INT NOT NULL | |
,[Events_6] INT NOT NULL | |
,[Events_7] INT NOT NULL | |
,[Events_8] INT NOT NULL | |
,[Events_9] INT NOT NULL | |
CONSTRAINT [PK_EventAccumHourly] PRIMARY KEY CLUSTERED | |
( | |
[SliceDate] ASC | |
,[ClientId] ASC | |
,[SiteId] ASC | |
,[AlertId] ASC | |
) | |
) | |
GO | |
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventAccumHourly_Client] ON [dbo].[EventAccumHourly] | |
( | |
[ClientId] ASC | |
,[SiteId] ASC | |
,[AlertId] ASC | |
,[SliceDate] ASC | |
) | |
INCLUDE ([Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
GO | |
CREATE TABLE [dbo].[EventAccumDaily]( | |
[SliceDate] DATE NOT NULL CONSTRAINT [CK_EventAccumDaily_SliceDate] CHECK ([SliceDate]=DATEADD(DAY,DATEDIFF(DAY,0,[SliceDate]),0)) | |
,[ClientId] BIGINT NOT NULL | |
,[SiteId] BIGINT NOT NULL | |
,[AlertId] BIGINT NOT NULL | |
,[Events_1] INT NOT NULL | |
,[Events_2] INT NOT NULL | |
,[Events_3] INT NOT NULL | |
,[Events_4] INT NOT NULL | |
,[Events_5] INT NOT NULL | |
,[Events_6] INT NOT NULL | |
,[Events_7] INT NOT NULL | |
,[Events_8] INT NOT NULL | |
,[Events_9] INT NOT NULL | |
CONSTRAINT [PK_EventAccumDaily] PRIMARY KEY CLUSTERED | |
( | |
[SliceDate] ASC | |
,[ClientId] ASC | |
,[SiteId] ASC | |
,[AlertId] ASC | |
) | |
) | |
GO | |
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventAccumDaily_Client] ON [dbo].[EventAccumDaily] | |
( | |
[ClientId] ASC | |
,[SiteId] ASC | |
,[AlertId] ASC | |
,[SliceDate] ASC | |
) | |
INCLUDE ([Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
GO | |
CREATE TABLE [dbo].[EventAccumMonthly]( | |
[SliceDate] DATE NOT NULL CONSTRAINT [CK_EventAccumMonthly_SliceDate] CHECK ([SliceDate]=DATEADD(MONTH,DATEDIFF(MONTH,0,[SliceDate]),0)) | |
,[ClientId] BIGINT NOT NULL | |
,[SiteId] BIGINT NOT NULL | |
,[AlertId] BIGINT NOT NULL | |
,[Events_1] BIGINT NOT NULL | |
,[Events_2] BIGINT NOT NULL | |
,[Events_3] BIGINT NOT NULL | |
,[Events_4] BIGINT NOT NULL | |
,[Events_5] BIGINT NOT NULL | |
,[Events_6] BIGINT NOT NULL | |
,[Events_7] BIGINT NOT NULL | |
,[Events_8] BIGINT NOT NULL | |
,[Events_9] BIGINT NOT NULL | |
CONSTRAINT [PK_EventAccumMonthly] PRIMARY KEY CLUSTERED | |
( | |
[SliceDate] ASC | |
,[ClientId] ASC | |
,[SiteId] ASC | |
,[AlertId] ASC | |
) | |
) | |
GO | |
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventAccumMonthly_Client] ON [dbo].[EventAccumMonthly] | |
( | |
[ClientId] ASC | |
,[SiteId] ASC | |
,[AlertId] ASC | |
,[SliceDate] ASC | |
) | |
INCLUDE ([Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
GO | |
CREATE TABLE [dbo].[EventAccumYearly]( | |
[SliceDate] DATE NOT NULL CONSTRAINT [CK_EventAccumYearly_SliceDate] CHECK ([SliceDate]=DATEADD(YEAR,DATEDIFF(YEAR,0,[SliceDate]),0)) | |
,[ClientId] BIGINT NOT NULL | |
,[SiteId] BIGINT NOT NULL | |
,[AlertId] BIGINT NOT NULL | |
,[Events_1] BIGINT NOT NULL | |
,[Events_2] BIGINT NOT NULL | |
,[Events_3] BIGINT NOT NULL | |
,[Events_4] BIGINT NOT NULL | |
,[Events_5] BIGINT NOT NULL | |
,[Events_6] BIGINT NOT NULL | |
,[Events_7] BIGINT NOT NULL | |
,[Events_8] BIGINT NOT NULL | |
,[Events_9] BIGINT NOT NULL | |
CONSTRAINT [PK_EventAccumYearly] PRIMARY KEY CLUSTERED | |
( | |
[SliceDate] ASC | |
,[ClientId] ASC | |
,[SiteId] ASC | |
,[AlertId] ASC | |
) | |
) | |
GO | |
CREATE UNIQUE NONCLUSTERED INDEX [IX_EventAccumYearly_Client] ON [dbo].[EventAccumYearly] | |
( | |
[ClientId] ASC | |
,[SiteId] ASC | |
,[AlertId] ASC | |
,[SliceDate] ASC | |
) | |
INCLUDE ([Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
GO | |
CREATE TRIGGER [dbo].[TRG_EventAccumHourly] | |
ON [dbo].[EventAccumHourly] | |
AFTER INSERT, DELETE, UPDATE | |
AS BEGIN | |
SET NOCOUNT ON; | |
DECLARE @changes TABLE( | |
[SliceDate] DATE NOT NULL | |
,[SliceMonth] AS DATEADD(MONTH, DATEDIFF(MONTH, 0, [SliceDate]), 0) | |
,[SliceYear] AS DATEADD(YEAR, DATEDIFF(YEAR, 0, [SliceDate]), 0) | |
,[ClientId] BIGINT NOT NULL | |
,[SiteId] BIGINT NOT NULL | |
,[AlertId] BIGINT NOT NULL | |
,[Events_1] INT NOT NULL | |
,[Events_2] INT NOT NULL | |
,[Events_3] INT NOT NULL | |
,[Events_4] INT NOT NULL | |
,[Events_5] INT NOT NULL | |
,[Events_6] INT NOT NULL | |
,[Events_7] INT NOT NULL | |
,[Events_8] INT NOT NULL | |
,[Events_9] INT NOT NULL | |
, PRIMARY KEY CLUSTERED([SliceDate],[ClientId],[SiteId],[AlertId])) | |
;WITH Hourlys AS | |
( | |
SELECT [SliceDate]=DATEADD(DAY, DATEDIFF(DAY, 0, I.[SliceDate]), 0) | |
, [ClientId] | |
, [SiteId] | |
, [AlertId] | |
, [Events_1] | |
, [Events_2] | |
, [Events_3] | |
, [Events_4] | |
, [Events_5] | |
, [Events_6] | |
, [Events_7] | |
, [Events_8] | |
, [Events_9] | |
FROM INSERTED AS I | |
UNION ALL | |
SELECT [SliceDate]=DATEADD(DAY, DATEDIFF(DAY, 0, D.[SliceDate]), 0) | |
, [ClientId] | |
, [SiteId] | |
, [AlertId] | |
, [Events_1]=-D.[Events_1] | |
, [Events_2]=-D.[Events_2] | |
, [Events_3]=-D.[Events_3] | |
, [Events_4]=-D.[Events_4] | |
, [Events_5]=-D.[Events_5] | |
, [Events_6]=-D.[Events_6] | |
, [Events_7]=-D.[Events_7] | |
, [Events_8]=-D.[Events_8] | |
, [Events_9]=-D.[Events_9] | |
FROM DELETED AS D | |
) | |
, SummedHourlys AS | |
( | |
SELECT [SliceDate] | |
, [ClientId] | |
, [SiteId] | |
, [AlertId] | |
, [Events_1]=SUM([Events_1]) | |
, [Events_2]=SUM([Events_2]) | |
, [Events_3]=SUM([Events_3]) | |
, [Events_4]=SUM([Events_4]) | |
, [Events_5]=SUM([Events_5]) | |
, [Events_6]=SUM([Events_6]) | |
, [Events_7]=SUM([Events_7]) | |
, [Events_8]=SUM([Events_8]) | |
, [Events_9]=SUM([Events_9]) | |
FROM Hourlys | |
GROUP BY [SliceDate], [ClientId], [SiteId], [AlertId] | |
) | |
INSERT INTO @changes([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
SELECT [SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9] | |
FROM SummedHourlys | |
WHERE ([Events_1] <> 0 OR [Events_2] <> 0 OR [Events_3] <> 0 OR [Events_4] <> 0 OR [Events_5] <> 0 OR [Events_6] <> 0 OR [Events_7] <> 0 OR [Events_8] <> 0 OR [Events_9] <> 0) | |
IF NOT EXISTS (SELECT * FROM @changes) RETURN | |
MERGE [dbo].[EventAccumDaily] AS t | |
USING (SELECT [SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9] | |
FROM @changes | |
) AS s([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
ON t.[SliceDate]=s.[SliceDate] AND t.[ClientId]=s.[ClientId] AND t.[SiteId]=s.[SiteId] AND t.[AlertId]=s.[AlertId] | |
WHEN NOT MATCHED BY TARGET --AND s.[SliceDate] >= DATEADD(DAY, -60, DATEADD(DAY,DATEDIFF(DAY,0,GETUTCDATE()),0)) | |
THEN INSERT([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
VALUES(s.[SliceDate],s.[ClientId],s.[SiteId],s.[AlertId],s.[Events_1],s.[Events_2],s.[Events_3],s.[Events_4],s.[Events_5],s.[Events_6],s.[Events_7],s.[Events_8],s.[Events_9]) | |
WHEN MATCHED THEN | |
UPDATE SET | |
t.[Events_1] += s.[Events_1] | |
, t.[Events_2] += s.[Events_2] | |
, t.[Events_3] += s.[Events_3] | |
, t.[Events_4] += s.[Events_4] | |
, t.[Events_5] += s.[Events_5] | |
, t.[Events_6] += s.[Events_6] | |
, t.[Events_7] += s.[Events_7] | |
, t.[Events_8] += s.[Events_8] | |
, t.[Events_9] += s.[Events_9] | |
; | |
;MERGE [dbo].[EventAccumMonthly] AS t | |
USING (SELECT | |
[SliceDate]=[SliceMonth] | |
, [ClientId] | |
, [SiteId] | |
, [AlertId] | |
, [Events_1]=SUM(CAST([Events_1] AS BIGINT)) | |
, [Events_2]=SUM(CAST([Events_2] AS BIGINT)) | |
, [Events_3]=SUM(CAST([Events_3] AS BIGINT)) | |
, [Events_4]=SUM(CAST([Events_4] AS BIGINT)) | |
, [Events_5]=SUM(CAST([Events_5] AS BIGINT)) | |
, [Events_6]=SUM(CAST([Events_6] AS BIGINT)) | |
, [Events_7]=SUM(CAST([Events_7] AS BIGINT)) | |
, [Events_8]=SUM(CAST([Events_8] AS BIGINT)) | |
, [Events_9]=SUM(CAST([Events_9] AS BIGINT)) | |
FROM @changes | |
GROUP BY [SliceMonth], [ClientId], [SiteId], [AlertId] | |
) AS s([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
ON t.[SliceDate]=s.[SliceDate] AND t.[ClientId]=s.[ClientId] AND t.[SiteId]=s.[SiteId] AND t.[AlertId]=s.[AlertId] | |
WHEN NOT MATCHED BY TARGET --AND s.SliceDate >= DATEADD(MONTH, -24, DATEADD(MONTH,DATEDIFF(MONTH,0,GETUTCDATE()),0)) | |
THEN INSERT([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
VALUES(s.[SliceDate],s.[ClientId],s.[SiteId],s.[AlertId],s.[Events_1],s.[Events_2],s.[Events_3],s.[Events_4],s.[Events_5],s.[Events_6],s.[Events_7],s.[Events_8],s.[Events_9]) | |
WHEN MATCHED THEN | |
UPDATE SET | |
t.[Events_1] += s.[Events_1] | |
, t.[Events_2] += s.[Events_2] | |
, t.[Events_3] += s.[Events_3] | |
, t.[Events_4] += s.[Events_4] | |
, t.[Events_5] += s.[Events_5] | |
, t.[Events_6] += s.[Events_6] | |
, t.[Events_7] += s.[Events_7] | |
, t.[Events_8] += s.[Events_8] | |
, t.[Events_9] += s.[Events_9] | |
; | |
;MERGE [dbo].[EventAccumYearly] AS t | |
USING (SELECT | |
[SliceDate]=[SliceYear] | |
, [ClientId] | |
, [SiteId] | |
, [AlertId] | |
, [Events_1]=SUM(CAST([Events_1] AS BIGINT)) | |
, [Events_2]=SUM(CAST([Events_2] AS BIGINT)) | |
, [Events_3]=SUM(CAST([Events_3] AS BIGINT)) | |
, [Events_4]=SUM(CAST([Events_4] AS BIGINT)) | |
, [Events_5]=SUM(CAST([Events_5] AS BIGINT)) | |
, [Events_6]=SUM(CAST([Events_6] AS BIGINT)) | |
, [Events_7]=SUM(CAST([Events_7] AS BIGINT)) | |
, [Events_8]=SUM(CAST([Events_8] AS BIGINT)) | |
, [Events_9]=SUM(CAST([Events_9] AS BIGINT)) | |
FROM @changes | |
GROUP BY [SliceYear], [ClientId], [SiteId], [AlertId] | |
) AS s([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
ON t.[SliceDate]=s.[SliceDate] AND t.[ClientId]=s.[ClientId] AND t.[SiteId]=s.[SiteId] AND t.[AlertId]=s.[AlertId] | |
WHEN NOT MATCHED BY TARGET --AND s.SliceDate >= DATEADD(MONTH, -24, DATEADD(MONTH,DATEDIFF(MONTH,0,GETUTCDATE()),0)) | |
THEN INSERT([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
VALUES(s.[SliceDate],s.[ClientId],s.[SiteId],s.[AlertId],s.[Events_1],s.[Events_2],s.[Events_3],s.[Events_4],s.[Events_5],s.[Events_6],s.[Events_7],s.[Events_8],s.[Events_9]) | |
WHEN MATCHED THEN | |
UPDATE SET | |
t.[Events_1] += s.[Events_1] | |
, t.[Events_2] += s.[Events_2] | |
, t.[Events_3] += s.[Events_3] | |
, t.[Events_4] += s.[Events_4] | |
, t.[Events_5] += s.[Events_5] | |
, t.[Events_6] += s.[Events_6] | |
, t.[Events_7] += s.[Events_7] | |
, t.[Events_8] += s.[Events_8] | |
, t.[Events_9] += s.[Events_9] | |
; | |
END | |
GO | |
CREATE TRIGGER [dbo].[TRG_ClientEvent] | |
ON [dbo].[ClientEvent] | |
AFTER INSERT,DELETE,UPDATE | |
AS | |
BEGIN | |
SET NOCOUNT ON; | |
-- if no work to be done, bail out early | |
IF NOT EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED) RETURN | |
-- now update all the hourly reporting slots based on the newly inserted rows. | |
; WITH deltas([ClientId],[SiteId],[AlertId],[Event],[SliceDate],[Delta]) AS | |
(SELECT I.[ClientId],I.[SiteId],[AlertId]=COALESCE(I.[AlertID],0),I.[Event],[SliceDate]=DATEADD(HOUR,DATEDIFF(HOUR,0,I.[TimestampUtc]),0),[Delta]=1 | |
FROM INSERTED AS I | |
UNION ALL | |
SELECT D.[ClientId],D.[SiteId],[AlertId]=COALESCE(D.[AlertID],0),D.[Event],[SliceDate]=DATEADD(HOUR,DATEDIFF(HOUR,0,D.[TimestampUtc]),0),[Delta]=-1 | |
FROM DELETED AS D) | |
, merged ([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) AS | |
(SELECT | |
D.[SliceDate] | |
,D.[ClientId] | |
,D.[SiteId] | |
,D.[AlertId] | |
,[Events_1]=SUM(CASE WHEN [Event]=1 THEN D.[Delta] ELSE 0 END) | |
,[Events_2]=SUM(CASE WHEN [Event]=2 THEN D.[Delta] ELSE 0 END) | |
,[Events_3]=SUM(CASE WHEN [Event]=3 THEN D.[Delta] ELSE 0 END) | |
,[Events_4]=SUM(CASE WHEN [Event]=4 THEN D.[Delta] ELSE 0 END) | |
,[Events_5]=SUM(CASE WHEN [Event]=5 THEN D.[Delta] ELSE 0 END) | |
,[Events_6]=SUM(CASE WHEN [Event]=6 THEN D.[Delta] ELSE 0 END) | |
,[Events_7]=SUM(CASE WHEN [Event]=7 THEN D.[Delta] ELSE 0 END) | |
,[Events_8]=SUM(CASE WHEN [Event]=8 THEN D.[Delta] ELSE 0 END) | |
,[Events_9]=SUM(CASE WHEN [Event]=9 THEN D.[Delta] ELSE 0 END) | |
FROM deltas AS D | |
WHERE [Event] IS NOT NULL | |
GROUP BY [SliceDate],[ClientId],[SiteId],[AlertId]) | |
MERGE [dbo].[EventAccumHourly] AS t | |
USING (SELECT [SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9] | |
FROM merged | |
WHERE ([Events_1] <> 0 OR [Events_2] <> 0 OR [Events_3] <> 0 OR [Events_4] <> 0 OR [Events_5] <> 0 OR [Events_6] <> 0 OR [Events_7] <> 0 OR [Events_8] <> 0 OR [Events_9] <> 0) | |
) AS s([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
ON t.[SliceDate]=s.[SliceDate] | |
AND t.[ClientId]=s.[ClientId] | |
AND t.[SiteId]=s.[SiteId] | |
AND t.[AlertId]=s.[AlertId] | |
WHEN NOT MATCHED BY TARGET THEN | |
INSERT([SliceDate],[ClientId],[SiteId],[AlertId],[Events_1],[Events_2],[Events_3],[Events_4],[Events_5],[Events_6],[Events_7],[Events_8],[Events_9]) | |
VALUES(s.[SliceDate],s.[ClientId],s.[SiteId],s.[AlertId],s.[Events_1],s.[Events_2],s.[Events_3],s.[Events_4],s.[Events_5],s.[Events_6],s.[Events_7],s.[Events_8],s.[Events_9]) | |
WHEN MATCHED THEN | |
UPDATE SET | |
t.[Events_1] += s.[Events_1] | |
, t.[Events_2] += s.[Events_2] | |
, t.[Events_3] += s.[Events_3] | |
, t.[Events_4] += s.[Events_4] | |
, t.[Events_5] += s.[Events_5] | |
, t.[Events_6] += s.[Events_6] | |
, t.[Events_7] += s.[Events_7] | |
, t.[Events_8] += s.[Events_8] | |
, t.[Events_9] += s.[Events_9] | |
; | |
END | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment