· 9 years ago · Jan 21, 2017, 11:14 AM
1USE [StruxureWareReportsDB]
2GO
3
4/****** Object: StoredProcedure [dbo].[CORAL_getMeterUsage_Vanilla] ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11-- =============================================
12-- Author: Kamil Milewski
13-- Create date: 2016
14
15-- Description: Default proocedure intended to extract periodical meter medium usage
16-- along with additional meter info like serial number or description
17-- =============================================
18CREATE PROCEDURE [dbo].[CORAL_getMeterUsage_Vanilla]
19 -- Parameters for the stored procedure here with default walues
20 @endTime DATETIME = '2018-04-06 21:00:00',
21 @startTime DATETIME = '2015-01-01 01:00:00',
22 @requestedMedium nvarchar(50) = 'Energia_elektryczna',
23 @requestedSiteName nvarchar(50) = 'Strefa_domyslna',
24
25 @trickyTime DATETIME = '2016-01-01 01:00:00'
26AS
27BEGIN
28 -- SET NOCOUNT ON added to prevent extra result sets from
29 -- interfering with SELECT statements.
30 SET NOCOUNT ON;
31
32 /*
33TABLE DROPS
34-------------------------------------
35If there already exists temporary tables with those names in database cache we have
36to drop them (clean them from database cache).
37 */
38BEGIN
39 -- Table for final results of this whole query
40 IF OBJECT_ID('tempdb.dbo.#Final_Table', 'U') IS NOT NULL DROP TABLE #Final_Table
41
42 -- further columns will be added to this table durin query. At the end it will be join into #Fianl_Table
43 IF OBJECT_ID('tempdb.dbo.#Table', 'U') IS NOT NULL DROP TABLE #Table
44
45 -- Table for given time range. Here are all logs for given perion(all meters) mixed in
46 IF OBJECT_ID('tempdb.dbo.#LogTimeValues', 'U') IS NOT NULL DROP TABLE #LogTimeValues
47
48 -- Table for given time range. Here are records with minVal, maxVal, difVal, Entity id
49 IF OBJECT_ID('tempdb.dbo.#TimeValues', 'U') IS NOT NULL DROP TABLE #TimeValues
50
51 -- Table for distinct ID's
52 IF OBJECT_ID('tempdb.dbo.#TempIDTable', 'U') IS NOT NULL DROP TABLE #TempIDTable
53
54 -- General info table
55 IF OBJECT_ID('tempdb.dbo.#General_Info_Table', 'U') IS NOT NULL DROP TABLE #General_Info_Table
56
57 -- Meter tags table
58 IF OBJECT_ID('tempdb.dbo.#Tags_Table', 'U') IS NOT NULL DROP TABLE #Tags_Table
59END
60
61
62/*
63Selecting all logs for given perion
64-------------------------------------
65 */
66 /*
67 Without this if statement meter usage values from adjecent time periods won't overlap
68 */
69 IF @requestedMedium = 'Energia Elektryczna' SET @trickyTime = DATEADD(MINUTE,-70,@startTime);
70 ELSE SET @trickyTime = DATEADD(HOUR,-23,@startTime);
71
72 SELECT * INTO #LogTimeValues FROM tbLogTimeValues
73 WHERE (DateTimeStamp <= @endTime AND DateTimeStamp >= @trickyTime)
74
75 /*
76 SELECT * INTO #LogTimeValues FROM tbLogTimeValues
77 WHERE (DateTimeStamp <= @endTime AND DateTimeStamp >= @startTime)
78 */
79
80/*
81LOOP
82-------------------------------------
83Here #TimeValues is populated with values.
84 */
85CREATE TABLE #TimeValues (EntityID INT, startVal FLOAT, endVal FLOAT, diffVal FLOAT)
86BEGIN
87 SELECT DISTINCT ParentID INTO #TempIDTable
88 FROM #LogTimeValues;
89
90 DECLARE @Id INT --body of the loop
91 WHILE (SELECT Count(*) FROM #TempIDTable) > 0 --body of the loop
92 BEGIN --body of the loop
93 SELECT TOP 1 @Id = ParentID FROM #TempIDTable --body of the loop
94 -- 1 ID in #TempIDTable - 1 iteration of the loop
95 -- #OneMeterLogs - table for loogs for given meter
96 IF OBJECT_ID('tempdb.dbo.#OneMeterLogs', 'U') IS NOT NULL
97 DROP TABLE #OneMeterLogs
98 SELECT OdometerValue, DateTimeStamp INTO #OneMeterLogs FROM #LogTimeValues
99 WHERE
100 ParentID = @Id
101
102 --min val
103 DECLARE @minValue FLOAT
104 SELECT TOP 1 @minValue = OdometerValue FROM #OneMeterLogs
105 ORDER BY DateTimeStamp
106 SET @minValue = ROUND(@minValue,2)
107
108 --max val
109 DECLARE @maxValue FLOAT
110 SELECT TOP 1 @maxValue = OdometerValue FROM #OneMeterLogs
111 ORDER BY DateTimeStamp DESC
112 SET @maxValue = ROUND(@maxValue,2)
113
114 --difference val
115 DECLARE @diffValue FLOAT
116 SET @diffValue = @maxValue - @minValue
117 SET @diffValue = ROUND(@diffValue,2)
118
119 INSERT INTO #TimeValues (EntityID, startVal, endVal, diffVal)
120 VALUES (@Id, @minValue, @maxValue, @diffValue)
121
122 DELETE #TempIDTable WHERE ParentID = @Id --body of the loop
123 END --body of the loop
124
125END
126
127
128/*
129Joining tbMeter and tbLoggedEntities into #General_Info_Table.
130-------------------------------------
131tbMeter is for Meter files
132tbLoggedEntities is for ext log files
133 */
134SELECT * INTO #General_Info_Table FROM
135(
136 SELECT
137 LoggedEntityGUID, -- GUID of the ext trend log
138 GUID AS MeterGUID, -- GUID of the meter
139 MPAN, -- MPAN (here used as a meter description)
140 MeterSerialNumber, -- Meter serial numer
141 Name, -- Name of the meter file
142 Path -- Path of the meter file
143 FROM tbMeter
144) tab1
145INNER JOIN
146(
147 SELECT
148 ID, -- ID for the ext trend log
149 GUID -- GUID for external trend log
150 --UNITPREFIX, -- Recorded value unit prefix
151 --Unit -- Recorded value unit
152 FROM tbLoggedEntities
153) tab2
154ON
155tab1.LoggedEntityGUID = tab2.GUID
156
157
158/*
159Joining #TimeValues and #General_Info_Table into #Table.
160-------------------------------------
161tbMeter is for Meter files
162tbLoggedEntities is for ext log files
163 */
164BEGIN
165 SELECT * INTO #Table FROM
166 #TimeValues
167 tab1
168 INNER JOIN
169 #General_Info_Table
170 tab2
171 ON
172 tab1.EntityID = tab2.ID
173 ALTER TABLE #Table DROP COLUMN ID, GUID; -- Those are the same with EntityID and LoggedEntityGUID
174END
175
176/*
177Adding two columns to #Table
178-------------------------------------
179SiteName - for meter site name
180Medium - for meter medium type
181 */
182BEGIN
183 DECLARE @DynamicSQL nvarchar(250)
184
185 -- Adding SiteName column
186 ALTER TABLE #Table ADD SiteName nvarchar(100)
187 DECLARE @SiteName nvarchar(100)
188 SET @DynamicSQL = 'ALTER TABLE #Table ADD ['+ CAST(@SiteName AS NVARCHAR(100)) +'] NVARCHAR(100) NULL'
189 EXEC(@DynamicSQL)
190
191 -- Adding Medium column
192 ALTER TABLE #Table ADD Medium nvarchar(100)
193 DECLARE @Medium nvarchar(100)
194 SET @DynamicSQL = 'ALTER TABLE #Table ADD ['+ CAST(@Medium AS NVARCHAR(100)) +'] NVARCHAR(100) NULL'
195 EXEC(@DynamicSQL)
196END
197
198/*
199Extracting site name from Path column
200-------------------------------------
201into newly created SiteName column
202 */
203BEGIN
204 UPDATE #Table SET SiteName = REPLACE(Path, '/MCK_ES/Energy/MCK_ES/', '')
205 UPDATE #Table SET SiteName = REPLACE(SiteName, '/' + RIGHT(SiteName, CHARINDEX('/', REVERSE(SiteName)) -1), '')
206 UPDATE #Table SET SiteName = LEFT(SiteName, CHARINDEX('/', SiteName) - 1)
207 WHERE CHARINDEX('/', SiteName) > 0
208END
209
210/*
211Extracting medium of the meter from Path column
212-------------------------------------
213into newly created Medium column
214 */
215BEGIN
216 UPDATE #Table SET Medium = REPLACE(Path, '/MCK_ES/Energy/MCK_ES/', '')
217 UPDATE #Table SET Medium = REPLACE(Medium, '/' + RIGHT(Medium, CHARINDEX('/', REVERSE(Medium)) -1), '')
218 UPDATE #Table SET Medium = REPLACE(Medium, SiteName + '/', '')
219END
220
221/*
222Joining tbTagMappings and tbTags
223-------------------------------------
224into newly created #Tags_Table
225 */
226BEGIN
227 SELECT * INTO #Tags_Table FROM
228 (
229 SELECT
230 *
231 FROM tbTagMappings
232 ) tab1
233 INNER JOIN
234 (
235 SELECT
236 *
237 FROM tbTags
238 ) tab2
239 ON
240 tab1.TagID = tab2.ID
241
242 --We don't need those columns
243 ALTER TABLE #Tags_Table DROP COLUMN GUID, ID, TagID
244END
245
246/*
247Joining #Table and #Tags_Table
248-------------------------------------
249into newly created, final #Final_Table
250 */
251BEGIN
252 SELECT * INTO #Final_Table FROM
253 (
254 SELECT
255 *
256 FROM #Table
257 ) tab1
258 INNER JOIN
259 (
260 SELECT
261 *
262 FROM #Tags_Table
263 ) tab2
264 ON
265 tab1.MeterGUID = tab2.TaggedObjectGUID
266
267 --We don't need those columns
268 ALTER TABLE #Final_Table DROP COLUMN TaggedObjectGUID, Path, LoggedEntityGUID, MeterGUID
269END
270
271SELECT * FROM #Final_Table WHERE Medium = @requestedMedium AND SiteName = @requestedSiteName order by Tag
272--select * from #Final_Table
273
274--SELECT * FROM #Final_Table WHERE MPAN LIKE '%TL-%' order by Tag
275END