-
Notifications
You must be signed in to change notification settings - Fork 2
Expand file tree
/
Copy pathConsolidateVersions.sql
More file actions
169 lines (154 loc) · 6.69 KB
/
ConsolidateVersions.sql
File metadata and controls
169 lines (154 loc) · 6.69 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
--------------------------------------------------------------------------------------------------------------------------------
-- CONSOLIDATE VERSIONS
--------------------------------------------------------------------------------------------------------------------------------
-- Given a start date and an end date, will consolidate all versions within that range into a single, new version. This has the
-- benefit of reducing the number of versions in the database, reducing the database size, and making some database queries
-- faster. If the end date parameter is not specified, it defaults to 2000-01-01, in which case ALL historical data will be
-- collapsed prior to the start date.
--------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [Utilities].[ConsolidateVersions]
@StartDate DATETIME2(7) = NULL,
@EndDate DATETIME2(7) = NULL
AS
--------------------------------------------------------------------------------------------------------------------------------
-- DECLARE AND SET VARIABLES
--------------------------------------------------------------------------------------------------------------------------------
DECLARE @IsNestedTransaction BIT;
BEGIN TRY
--------------------------------------------------------------------------------------------------------------------------------
-- BEGIN TRANSACTION
--------------------------------------------------------------------------------------------------------------------------------
IF (@@TRANCOUNT = 0)
BEGIN
SET @IsNestedTransaction = 0;
BEGIN TRANSACTION;
END
ELSE
BEGIN
SET @IsNestedTransaction = 1;
END
--------------------------------------------------------------------------------------------------------------------------------
-- ESTABLISH IMPLICIT DEFAULT FOR START DATE
--------------------------------------------------------------------------------------------------------------------------------
IF (@StartDate IS NULL)
BEGIN
SET @StartDate = CONVERT(DATETIME2(7), '2000-01-01')
END
--------------------------------------------------------------------------------------------------------------------------------
-- CREATE CONSOLIDATED TOPIC ATTRIBUTE RECORD
--------------------------------------------------------------------------------------------------------------------------------
;WITH TopicAttributes
AS (
SELECT Version,
RowNumber = ROW_NUMBER() OVER (
PARTITION BY TopicID,
AttributeKey
ORDER BY Version DESC
)
FROM Attributes
WHERE Version > @StartDate
AND Version <= @EndDate
)
UPDATE TopicAttributes
SET Version = @EndDate
WHERE RowNumber = 1
--------------------------------------------------------------------------------------------------------------------------------
-- DELETE CONSOLIDATED ATTRIBUTES
--------------------------------------------------------------------------------------------------------------------------------
DELETE
FROM Attributes
WHERE Version > @StartDate
AND Version < @EndDate
--------------------------------------------------------------------------------------------------------------------------------
-- CREATE CONSOLIDATED EXTENDED ATTRIBUTES RECORD
--------------------------------------------------------------------------------------------------------------------------------
;WITH TopicExtendedAttributes
AS (
SELECT Version,
RowNumber = ROW_NUMBER() OVER (
PARTITION BY TopicID
ORDER BY Version DESC
)
FROM ExtendedAttributes
WHERE Version > @StartDate
AND Version <= @EndDate
)
UPDATE TopicExtendedAttributes
SET Version = @EndDate
WHERE RowNumber = 1
--------------------------------------------------------------------------------------------------------------------------------
-- DELETE CONSOLIDATED EXTENDED ATTRIBUTES
--------------------------------------------------------------------------------------------------------------------------------
DELETE
FROM ExtendedAttributes
WHERE Version > @StartDate
AND Version < @EndDate
--------------------------------------------------------------------------------------------------------------------------------
-- CREATE CONSOLIDATED RELATIONSHIPS RECORD
--------------------------------------------------------------------------------------------------------------------------------
;WITH TopicRelationships
AS (
SELECT Version,
RowNumber = ROW_NUMBER() OVER (
PARTITION BY Source_TopicID,
RelationshipKey,
Target_TopicID
ORDER BY Version DESC
)
FROM Relationships
WHERE Version > @StartDate
AND Version <= @EndDate
)
UPDATE TopicRelationships
SET Version = @EndDate
WHERE RowNumber = 1
--------------------------------------------------------------------------------------------------------------------------------
-- DELETE CONSOLIDATED RELATIONSHIPS
--------------------------------------------------------------------------------------------------------------------------------
DELETE
FROM Relationships
WHERE Version > @StartDate
AND Version < @EndDate
--------------------------------------------------------------------------------------------------------------------------------
-- CREATE CONSOLIDATED TOPIC REFERENCES RECORD
--------------------------------------------------------------------------------------------------------------------------------
;WITH TopicReferenceVersions
AS (
SELECT Version,
RowNumber = ROW_NUMBER() OVER (
PARTITION BY Source_TopicID
ORDER BY Version DESC
)
FROM TopicReferences
WHERE Version > @StartDate
AND Version <= @EndDate
)
UPDATE TopicReferenceVersions
SET Version = @EndDate
WHERE RowNumber = 1
--------------------------------------------------------------------------------------------------------------------------------
-- DELETE CONSOLIDATED TOPIC REFERENCES
--------------------------------------------------------------------------------------------------------------------------------
DELETE
FROM TopicReferences
WHERE Version > @StartDate
AND Version < @EndDate
--------------------------------------------------------------------------------------------------------------------------------
-- COMMIT TRANSACTION
--------------------------------------------------------------------------------------------------------------------------------
IF (@@TRANCOUNT > 0 AND @IsNestedTransaction = 0)
BEGIN
COMMIT
END
END TRY
--------------------------------------------------------------------------------------------------------------------------------
-- HANDLE ERRORS
--------------------------------------------------------------------------------------------------------------------------------
BEGIN CATCH
IF (@@TRANCOUNT > 0 AND @IsNestedTransaction = 0)
BEGIN
ROLLBACK;
END;
THROW
RETURN;
END CATCH