--INSERTING ORIGINAL DATA

--Example 1
--First you will do a quick review of the last 10 minutes of data to verify the maximum 
	--temperature historized is around 400DegF.  The query will retrieve by default LATEST
	--data.
	
	SELECT	DateTime, Value, wwVersion
	FROM	History
	WHERE	TagName= 'M100.TT.PV'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode='Maximum'
	AND		wwCycleCount=1
	
--Example 2
	--You will correct one minute of data stored for the M100.TT.PV 5 minutes ago. 
	--The values will change based on a conversion formula from Fahrenheit to Celsius.
	--By default the values will be saved as Original data.
	--A successful INSERT will give you a message "XX row(s) affected"

	INSERT INTO	History (DateTime, TagName, Value, QualityDetail)
		SELECT	DateTime, 'M100.TT.PV', Round ((Value*9/5)+32,0), 192
		FROM	History
		WHERE	TagName= 'M100.TT.PV'
		AND		DateTime >= Dateadd(mi, -6, Getdate())
		AND		DateTime <= Dateadd(mi, -5, Getdate())

--Example 3
	--This query is the same query you ran in example 1.  Now the maximum temperature will be around 730Deg
	--confirming the INSERT was successful.  Take note of the approximate time stamp of this value to facilitate
	--the next query

	SELECT	DateTime, Value, wwVersion
	FROM	History
	WHERE	TagName= 'M100.TT.PV'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode='Maximum'
	AND		wwCycleCount=1

--Example 4
	--Now you will retrieve the original version of the data. Scroll down to locate the minute of corrected data.
	--You will see two values for the same time stamp, this is the initial value and the inserted value
	--which both stored as originals since no versioning was applied.

	SELECT	DateTime, Value, wwVersion
	FROM	History
	WHERE	TagName= 'M100.TT.PV'
	AND		DateTime >= Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()
	AND		wwVersion = 'Original'

--INSERTING LATEST DATA

--Example 5
	--Now  you want to be able to audit the correction, so you want to keep the original data and add 
	--the inserted data as a Latest version:

	INSERT INTO	History (DateTime, TagName, Value, QualityDetail, wwVersion)
		SELECT	DateTime, 'M100.TT.PV', Round ((value*9/5)+32,0), 192, 'Latest'
		FROM	History
		WHERE	TagName= 'M100.TT.PV'
		AND		DateTime > Dateadd(mi, -4, Getdate())
		AND		DateTime <=Dateadd(mi, -3, Getdate())

--Example 6

	--Run this query to find out the time stamp of the maximum latest value to facilitate the next query.  
	--By default the query will retrieve latest values.

	SELECT	DateTime, Value, wwVersion
	FROM	History
	WHERE	TagName= 'M100.TT.PV'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode='Maximum'
	AND		wwCycleCount=1

--Example 7
	--Now you will verify the insert of data was successful and both versions latest and original are different.
	--For each time stamp now you will find 2 values, the original and the latest clearly identified.

	SELECT	DateTime, Value, wwVersion
	FROM	History
	WHERE	TagName= 'M100.TT.PV'
	AND		DateTime >= Dateadd(mi, -10, Getdate())
	AND		DateTime <  Getdate()
	AND		wwVersion = 'Original'
	UNION
	SELECT	DateTime, Value, wwVersion
	FROM	History
	WHERE	TagName= 'M100.TT.PV'
	AND		DateTime >= Dateadd(mi, -10, Getdate())
	AND		DateTime <  Getdate()
	AND		wwVersion = 'Latest'
	

--Example 8
	--There was a problem with your temperature sensor and the values stored during a minute need to be changed 
	--to a constant value of 500. Data inserted via UPDATE statements will be saved as LATEST by default. 
	--OPENQUERY() only supports literal strings for the query so we need to enter the specific DateTime below.
	--Modify the dates to few minutes ago covering a time window of 1 minute.
	---A successful INSERT will give you a message "The Update request was submitted to the Historian storage system"

	SELECT * FROM OpenQuery(InSQL, '
		UPDATE History SET Value = 500, QualityDetail=192
			WHERE	TagName = "M100.TT.PV"
			AND		DateTime>="1999-01-01 00:00:00"
			AND		DateTime<="1999-01-01 00:01:00"
		')

--Example 9

	--Now you will verify the Update of data was successful and both version latest and original are different.  
	--Find the 500 data value in the results retrieved below (it will be only one row)
	SELECT	DateTime, Value, wwVersion
	FROM	History
	WHERE	TagName= 'M100.TT.PV'
	AND		DateTime >= Dateadd(mi, -1, '1999-01-01 00:00:00')
	AND		DateTime <  Dateadd(mi, +2, '1999-01-01 00:00:00')
	AND		wwVersion = 'Original'
	--AND		wwRetrievalMode = 'Cyclic'
	--AND		wwResolution=1000	
	UNION
	SELECT	DateTime, Value, wwVersion
	FROM	History
	WHERE	TagName= 'M100.TT.PV'
	AND		DateTime >= Dateadd(mi, -1, '1999-01-01 00:00:00')
	AND		DateTime <  Dateadd(mi, +2, '1999-01-01 00:00:00')
	AND		wwVersion = 'Latest'
	--AND		wwRetrievalMode = 'Cyclic'
	--AND		wwResolution=1000


	--Change both queries above to enable the lines wwRetrievalmode='cyclic' and  wwResolution=1000
	--discuss with your instructor why the results are different.