· 7 years ago · Jan 25, 2019, 11:06 AM
1CREATE TABLE [dbo].[AUDIT](
2[UNIQUE_ID] [int] IDENTITY(1,1) NOT NULL,
3[TABLE_NAME] [nvarchar](50) NOT NULL,
4[TABLE_UNIQUE_ID] [int] NOT NULL,
5[TABLE_USER_ID] [int] NOT NULL,
6[XML] [nvarchar](max) NULL,
7[DESCRIPTION] [nvarchar](max) NULL,
8[TIMESTAMP] [datetime] NOT NULL,
9[EXPLANATION] [nvarchar](max) NULL,
10[DATE_INDEX] [datetime] NOT NULL CONSTRAINT [DF_SYS_ADUIT_DATE_INDEX] DEFAULT ('01/01/1900')
11PRIMARY KEY CLUSTERED
12(
13 [UNIQUE_ID] ASC
14)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
15) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
16
17RECORD>
18 <AUDITID>914588</AUDITID>
19 <UID>316</UID>
20 <UNIQUEID>541</UNIQUEID>
21 <CURRENTDATA>
22 <Record>
23 <Data>Current record data and other xml goes here, not important right now</Data>
24 </Record>
25 </CURRENTDATA>
26 <CURRENTAUDITID>914588</CURRENTAUDITID>
27 <HISTORY>
28 <TIMESTAMP>8/8/11 9:45</TIMESTAMP>
29 <DESCRIPTION>Record was created.</DESCRIPTION>
30 <EXPLANATION></EXPLANATION>
31 </HISTORY>
32 <HISTORY>
33 <TIMESTAMP>8/8/11 10:01</TIMESTAMP>
34 <DESCRIPTION>Record locked</DESCRIPTION>
35 <EXPLANATION></EXPLANATION>
36 </HISTORY>
37 <HISTORY>
38 <TIMESTAMP></TIMESTAMP>
39 <DESCRIPTION>Record unlocked</DESCRIPTION>
40 <EXPLANATION></EXPLANATION>
41 </HISTORY>
42 <HISTORY>
43 <TIMESTAMP>8/15/11 13:11</TIMESTAMP>
44 <DESCRIPTION>Record changes : </DESCRIPTION>
45 <EXPLANATION></EXPLANATION>
46 </HISTORY>
47 <HISTORY>
48 <TIMESTAMP>8/15/11 14:13</TIMESTAMP>
49 <DESCRIPTION>Record was exported.</DESCRIPTION>
50 </HISTORY>
51 <ORIGINAL>
52 <DATA>
53 <Record>
54 <Data>Orignal record data and other xml goes here, not important right now</Data>
55 </Record>
56 </DATA>
57 </ORIGINAL>
58</RECORD>
59
60declare @from nvarchar(20) = '8/8/2011', @through nvarchar(20) = '8/8/2011', @currentRecord int
61
62--rownum = 1 represents the latest record in a set
63;with CTERow as (
64 SELECT UNIQUE_ID,
65 TABLE_USER_ID,
66 TABLE_UNIQUE_ID,
67 TIMESTAMP,
68 DATE_INDEX,
69 XML,
70 DESCRIPTION,
71 ROW_NUMBER() OVER(PARTITION BY TABLE_USER_ID, TABLE_UNIQUE_id ORDER BY UNIQUE_ID DESC) AS RowNum
72 FROM [AUDIT]
73 where (DATE_INDEX >= @from AND DATE_INDEX <= @through))
74
75--This query selects current records that have a date (AUDIT.DATE_INDEX) that falls between the from/through dates
76select 1 AS [TAG], NULL AS PARENT,
77 --LEVEL 1
78 A.UNIQUE_ID AS [RECORD!1!AUDITID!ELEMENT],
79 A.TABLE_USER_ID AS [RECORD!1!UID!ELEMENT],
80 A.TABLE_UNIQUE_ID AS [RECORD!1!UNIQUEID!ELEMENT],
81 (SELECT XML from CTERow where CTErow.Unique_ID = A.UNIQUE_ID AND ROWNUM = 1) AS [RECORD!1!CURRENTDATA!XML],
82 A.UNIQUE_ID AS [RECORD!1!CURRENTAUDITID!XML],
83 --LEVEL 2
84 NULL as [ORIGINAL!2!DATA!XML],
85 NULL as [ORIGINAL!2!ORIGUID!HIDE],
86
87 --LEVEL 3
88 NULL AS [HISTORY!3!TIMESTAMP!ELEMENT],
89 NULL AS [HISTORY!3!DESCRIPTION!ELEMENT],
90 NULL AS [HISTORY!3!EXPLANATION!ELEMENT]
91
92from AUDIT A
93WHERE
94 (SELECT XML from CTERow where CTErow.Unique_ID = A.UNIQUE_ID) IS NOT NULL -- make sure that we're pulling the latest record of a set
95 AND (A.DATE_INDEX >= @from AND A.DATE_INDEX <= @through)
96
97
98UNION ALL
99
100-- This query pulls the original record
101SELECT 2 AS [TAG], 1 AS [PARENT],
102 --LEVEL 1
103 C.UNIQUE_ID,
104 C.TABLE_USER_ID,
105 C.TABLE_UNIQUE_ID,
106 NULL,
107 NULL,
108 --LEVEL 2
109 C.[XML],
110 null,
111 --LEVEL 3
112 NULL,
113 NULL,
114 NULL
115from AUDIT C
116where (C.DATE_INDEX >= @from AND C.DATE_INDEX <= @through) --or
117and C.DESCRIPTION like '%record was created%'
118
119
120UNION ALL
121
122--This query pulls the history of the record
123select 3 AS [TAG], 1 AS [PARENT],
124 --LEVEL 1
125 UNIQUE_ID,
126 TABLE_USER_ID,
127 TABLE_UNIQUE_ID,
128 NULL,
129 NULL,
130 --LEVEL 3
131 NULL,
132 NULL,
133 --LEVEL 2
134 B.[TIMESTAMP],
135 B.[DESCRIPTION],
136 B.[EXPLANATION]
137from AUDIT B
138where (B.DATE_INDEX >= @from AND B.DATE_INDEX <= @through)
139
140ORDER BY [RECORD!1!UID!ELEMENT], [RECORD!1!UNIQUEID!ELEMENT], [ORIGINAL!2!DATA!XML], [HISTORY!3!TIMESTAMP!ELEMENT]
141FOR XML EXPLICIT
142
143USE tempdb
144GO
145
146IF OBJECT_ID('[dbo].[AUDIT]') IS NOT NULL DROP TABLE [dbo].[AUDIT]
147GO
148
149CREATE TABLE [dbo].[AUDIT](
150
151 [UNIQUE_ID] [int], --IDENTITY(1,1) NOT NULL,
152 [TABLE_NAME] [nvarchar](50) NOT NULL,
153 [TABLE_UNIQUE_ID] [int] NOT NULL,
154 [TABLE_USER_ID] [int] NOT NULL,
155 [XML] [nvarchar](MAX) NULL,
156 [DESCRIPTION] [nvarchar](MAX) NULL,
157 [TIMESTAMP] [datetime] NOT NULL,
158 [EXPLANATION] [nvarchar](MAX) NULL,
159 [DATE_INDEX] [datetime] NOT NULL CONSTRAINT [DF_SYS_ADUIT_DATE_INDEX] DEFAULT ('01/01/1900')
160
161PRIMARY KEY CLUSTERED
162(
163 [UNIQUE_ID] ASC
164)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
165) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
166GO
167
168
169
170INSERT INTO [dbo].[AUDIT] ( UNIQUE_ID, TABLE_NAME, TABLE_UNIQUE_ID, TABLE_USER_ID, XML, DESCRIPTION, TIMESTAMP, EXPLANATION, DATE_INDEX )
171VALUES
172 ( 848098, 'MAIN', 500187, 317, '<Record><Data>Original record data and other xml goes here, not important right now</Data></Record>', 'Record was created', '8 Aug 2011 9:45', NULL, '1 Jan 1900' ),
173 ( 849901, 'MAIN', 500187, 317, '<Record/>', 'Record locked', '8 Aug 2011 10:01', NULL, '1 Jan 1900' ),
174 ( 854269, 'MAIN', 500187, 317, '<Record/>', 'Record unlocked', '8 Aug 2011 10:51', NULL, '3 Aug 2011' ),
175 ( 911382, 'MAIN', 500187, 317, '<Record><Data>Change record data and other xml goes here, not important right now</Data></Record>', 'Record changes:', '8 Aug 2011 10:52', NULL, '8 Aug 2011' ),
176 ( 927795, 'MAIN', 500187, 317, '<Record/>', 'Record locked', '15 Aug 2011 13:11', NULL, '1 Jan 1900' ),
177 ( 931013, 'MAIN', 500187, 317, '<Record><Data>Current record data and other xml goes here, not important right now</Data></Record>', 'Record was exported', '15 Aug 2011 14:13', NULL, '1 Jan 1900' )
178GO
179
180
181IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
182
183DECLARE @from DATETIME = '8 Aug 2011 00:00', @through DATETIME = '8 Aug 2011 23:59:59:999', @currentRecord int
184
185-- Get all records for that id and date range into a temp table
186SELECT
187 UNIQUE_ID,
188 TABLE_USER_ID,
189 TABLE_UNIQUE_ID,
190 [TIMESTAMP],
191 DATE_INDEX,
192 [XML],
193 [DESCRIPTION]
194INTO #tmp
195FROM dbo.[AUDIT]
196WHERE DATE_INDEX >= @from
197AND DATE_INDEX <= @through
198--AND presumably some other criteria to limit the records ...
199AND TABLE_UNIQUE_ID = 500187
200AND TABLE_USER_ID = 317
201
202-- Add unique index and RowNum column
203ALTER TABLE #tmp ADD PRIMARY KEY ( UNIQUE_ID )
204ALTER TABLE #tmp ADD RowNum INT
205
206-- Get any associated records (date criteria not used), which don't already exist
207INSERT INTO #tmp ( UNIQUE_ID, TABLE_USER_ID, TABLE_UNIQUE_ID, TIMESTAMP, DATE_INDEX, XML, DESCRIPTION )
208SELECT
209 UNIQUE_ID,
210 TABLE_USER_ID,
211 TABLE_UNIQUE_ID,
212 [TIMESTAMP],
213 DATE_INDEX,
214 [XML],
215 [DESCRIPTION]
216FROM dbo.[AUDIT] a
217WHERE EXISTS ( SELECT * FROM #tmp t WHERE a.TABLE_UNIQUE_ID = t.TABLE_UNIQUE_ID AND a.TABLE_USER_ID = t.TABLE_USER_ID )
218 AND NOT EXISTS ( SELECT * FROM #tmp u WHERE a.UNIQUE_ID = u.UNIQUE_ID )
219
220
221-- Add the row number
222;WITH cte AS
223(
224SELECT ROW_NUMBER() OVER(PARTITION BY TABLE_USER_ID, TABLE_UNIQUE_ID ORDER BY UNIQUE_ID ) x, *
225FROM #tmp
226)
227UPDATE cte
228SET RowNum = x
229
230
231-- Have a look at the data before we create the xml
232SELECT 't' s, * FROM #tmp
233GO
234
235
236SELECT
237 (
238 SELECT
239 UNIQUE_ID AS AUDITID,
240 TABLE_USER_ID AS [UID],
241 TABLE_UNIQUE_ID AS UNIQUEID,
242 CAST( [XML] AS XML ) AS "CURRENTDATA/*"
243 FROM #tmp
244 WHERE RowNum = ( SELECT MAX(RowNum) FROM #tmp )
245 FOR XML PATH(''), TYPE
246 ),
247 (
248 SELECT
249 [TIMESTAMP] AS [TIMESTAMP],
250 [DESCRIPTION] AS [DESCRIPTION],
251 '' AS EXPLANATION
252 FROM #tmp
253 WHERE RowNum > 1
254 --AND RowNum = ( SELECT MAX(RowNum) FROM #tmp ) -- ??
255 FOR XML PATH('HISTORY'), TYPE
256 ),
257 (
258 SELECT
259 CAST( [XML] AS XML ) AS "DATA/*"
260 FROM #tmp
261 WHERE RowNum = 1
262 FOR XML PATH('ORIGINAL'), TYPE
263 )
264
265FOR XML PATH('RECORD'), TYPE
266GO
267
268declare @from nvarchar(20) = '8/8/2015', @through nvarchar(20) = '8/8/2016', @currentRecord int
269--This CTE is simply a subquery to allow us to find all sets of a particular user record
270--rownum = 1 represents the latest record in a set
271--This query represents all of our "candidate" records to be selected from in this query.
272;with CTERow as (
273 SELECT UNIQUE_ID,
274 TABLE_USER_ID,
275 TABLE_UNIQUE_ID,
276 TIMESTAMP,
277 DATE_INDEX,
278 XML,
279 DESCRIPTION,
280 ROW_NUMBER() OVER(PARTITION BY TABLE_USER_ID, TABLE_UNIQUE_id ORDER BY UNIQUE_ID DESC) AS RowNum
281 FROM [AUDIT]
282 where (DATE_INDEX >= @from AND DATE_INDEX <= @through))
283
284--This query selects current records that have a worked date (AUDIT.DATE_INDEX) that falls between the from/through dates
285select 1 AS [TAG], NULL AS PARENT,
286 --LEVEL 1
287 A.UNIQUE_ID AS [RECORD!1!AUDITID!ELEMENT],
288 A.TABLE_USER_ID AS [RECORD!1!UID!ELEMENT],
289 A.TABLE_UNIQUE_ID AS [RECORD!1!UNIQUEID!ELEMENT],
290 (SELECT XML from CTERow where CTErow.Unique_ID = A.UNIQUE_ID AND ROWNUM = 1) AS [RECORD!1!CURRENTDATA!XML],
291 A.UNIQUE_ID AS [RECORD!1!CURRENTAUDITID!XML],
292 --LEVEL 2
293 NULL as [ORIGINAL!2!DATA!XML],
294 NULL as [ORIGINAL!2!ORIGUID!HIDE],
295
296 --LEVEL 3
297 NULL AS [HISTORY!3!TIMESTAMP!ELEMENT],
298 NULL AS [HISTORY!3!DESCRIPTION!ELEMENT],
299 NULL AS [HISTORY!3!EXPLANATION!ELEMENT]
300
301from AUDIT A
302WHERE UNIQUE_ID in
303 (select max(unique_id) FROM CTERow C
304 WHERE A.TABLE_UNIQUE_ID = C.TABLE_UNIQUE_ID and A.TABLE_USER_ID = C.TABLE_USER_ID)
305
306
307UNION ALL
308
309-- This query pulls the original record
310SELECT 2 AS [TAG], 1 AS [PARENT],
311 --LEVEL 1
312 B.UNIQUE_ID,
313 B.TABLE_USER_ID,
314 B.TABLE_UNIQUE_ID,
315 NULL,
316 NULL,
317 --LEVEL 2
318 B.[XML],
319 null,
320 --LEVEL 3
321 NULL,
322 NULL,
323 NULL
324from AUDIT B
325where UNIQUE_ID in
326 (select min(unique_id) FROM CTERow C
327 WHERE B.TABLE_UNIQUE_ID = C.TABLE_UNIQUE_ID and B.TABLE_USER_ID = C.TABLE_USER_ID)
328
329UNION ALL
330
331--This query pulls the history of the record
332select 3 AS [TAG], 1 AS [PARENT],
333 --LEVEL 1
334 UNIQUE_ID,
335 TABLE_USER_ID,
336 TABLE_UNIQUE_ID,
337 NULL,
338 NULL,
339 --LEVEL 2
340 NULL,
341 NULL,
342 --LEVEL 3
343 B.[TIMESTAMP],
344 B.[DESCRIPTION],
345 B.[EXPLANATION]
346from AUDIT B
347where
348 convert(nvarchar(20), B.TABLE_UNIQUE_ID) + convert(nvarchar(20), B.TABLE_USER_ID) in
349 (select convert(nvarchar(20),TABLE_UNIQUE_ID) + convert(nvarchar(20),TABLE_USER_ID) FROM CTERow c)
350
351ORDER BY [RECORD!1!UID!ELEMENT], [RECORD!1!UNIQUEID!ELEMENT], [ORIGINAL!2!DATA!XML], [HISTORY!3!TIMESTAMP!ELEMENT], [HISTORY!3!DESCRIPTION!ELEMENT], [HISTORY!3!EXPLANATION!ELEMENT]
352FOR XML EXPLICIT