--INTERPOLATED RETRIEVAL 

--Example 1
	--The following query will return the values for a tank level as reported by floating level 
	--switches located at 25%, 50%, 75%, 95% and 100%.  The values read are discrete, so the 
	--following query will retrieve all the values with the default stair step interpolation.

	SELECT	DateTime, TagName, Value, wwResolution
	FROM	History
	WHERE	TagName = 'HistTest.FloatLevelSwitch'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()

--Example 2
	--If you require a value when the tank level is somewhere between 25% and 50%,
	--you can use an interpolated retrieval to get an approximated value.
 
	SELECT DateTime, TagName, Value
	FROM 	History
	WHERE 	TagName = 'HistTest.FloatLevelSwitch'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND  	DateTime <= Getdate()
	AND		wwRetrievalMode = 'Interpolated'
	AND		wwInterpolationType = 'Linear'
	AND		wwResolution =2000

	--1. What is the most current time stamp when the level was approximately at 35%?__________ 
	--(To get more accurate results change the value of wwResolution)


--AVERAGE RETRIEVAL

--Example 3
	--The following query will retrieve the total liters of water added to a production line during
	--a certain period of time. The outlet valve has two positions, delivering a constant flow of 5 or 10
	--liters per second (LPS).

	SELECT	DateTime, TagName, Value, wwResolution
	FROM	History
	WHERE	TagName = 'HistTest.WaterFlow'
	AND		DateTime > Dateadd(mi, -1, Getdate())
	AND		DateTime <= Getdate()

	--2. Notice on the results that the wwResolution column displays the duration of the valve in each
	-- position. In which position was the valve open the longest, 5 or 10 LPS?____________

--Example 4
	--The next query will give you a statistical average calculation
 
	SELECT	AVG(Value)
	FROM	History
	WHERE	TagName= 'HistTest.WaterFlow'
	AND		DateTime > Dateadd(mi, -1, Getdate())
	AND		DateTime <= Getdate()

	--3. The average amount of water delivered is ______ liters
	--The query above did not account for the length of time the valve was opened on each setting, 
	--so now the next query will retrieve the Time Weighted Average (TWA) for the same period, 
	--using one cycle count to do the calculation:

--Example 5

	SELECT	DateTime, TagName, Value
	FROM	History
	WHERE	TagName= 'HistTest.WaterFlow'
	AND		DateTime > Dateadd(mi, -1, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'Average'
	AND		wwCycleCount = 1

	--4. The Time Weighted Average amount of water delivered is _____liters.
	--5. Change wwCycleCount value to 2 to get the TWA for two periods of _____seconds each.


--INTEGRAL RETRIEVAL 

--Example 6
	--For the same case scenario above, you will find the total amount of water delivered 
	--during the last 5 minutes.

	SELECT	DateTime, TagName, Value
	FROM	History
	WHERE	TagName= 'HistTest.WaterFlow'
	AND		DateTime > Dateadd(mi, -5, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'Integral'
	AND		wwCycleCount = 1

	--6. During the last five minutes a total of ________ liters of water were delivered.
	--7. Modify the query to find the total amount of water delivered during the last 10 minutes: _____
	--8. How many records are retrieved if you remove the wwCycleCount line? _____

--MINIMUM AND MAXIMUM RETRIEVAL

--Example 7
	--You are monitoring the weight of your final product, which acceptable range is between 450 
	--and 470 grams.   In this query you will find the minimum and maximum values during the last 3 hours.

	SELECT	DateTime, TagName, vValue
	FROM	History
	WHERE	TagName = 'HistTest.UnitWeight'
	AND		DateTime > Dateadd(hh, -2, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'Maximum'
	AND		wwCycleCount = 1

	--9. The maximum weight found was_______
	--Change the wwCycleCount value in the last query so we retrieve the maximum weight found each 
	--hour for the last 2 hours.
	--10. First hour maximum	______
	--11. Second hour maximum	______

--Example 8

	SELECT	DateTime, TagName, vValue
	FROM	History
	WHERE	TagName = 'HistTest.UnitWeight'
	AND		DateTime > Dateadd(hh, -3, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'Minimum'
	AND		wwCycleCount = 1

	--12. The minimum weight found was_______
	--Change the wwCycleCount value in the last query so we retrieve the  minimum weight found each 
	--hour for the last 3 hours.
	--13. First hour minimum	______
	--14. Second hour minimum	______
	--15. Third hour minimum	______


--SLOPE RETRIEVAL

--Example 9
	--The temperature in a baking oven is captured and historized once per minute as it changes
	--slowly between 393DegC and 407DegC 

	SELECT	DateTime, TagName, Value
	FROM	History
	WHERE	TagName = 'HistTest.OvenTemp'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()

	--16. How many records are retrieved with this query?___________

--Example 10
	--To keep high quality standards, the baking temperature cannot change up or down more than
	--four DegC per minute, this is,  +/- 0.066 per second (4/60=0.066).
	--The following query will calculate the rate of change for the data points stored
	--during the last 10 minutes:

	SELECT	Datetime, TagName, Value
	FROM	History
	WHERE	TagName = 'HistTest.OvenTemp'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'Slope'

	--17. How many times did the value change more than 0.066 DegC or less than -0.066 DegC per second? ____

--Example 11
	--In this query you will list only the occurrences that compromised the quality of the product:
	
	SELECT	DateTime, TagName, Value
	FROM	History
	WHERE	TagName = 'HistTest.OvenTemp' 
	OR		Value < '-0.066' 
	OR		Value > '0.066'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'Slope'
	
	--18. How many records are retrieved with this query?____________

--VALUESTATE RETRIEVAL 

--Example 12
	--For maintenance purposes you need to generate a report on when the Agitators have malfunctioned.  
	--The following query gives you the amount of milliseconds the malfunction flag of the agitators was 
	--on or off during the last hour.

	SELECT	DateTime, TagName, Value, StateTime
	FROM	History
	WHERE	TagName LIKE 'Agitator%.Malfunction'
	AND		DateTime > Dateadd(hh, -1, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'ValueState'
	AND		wwStateCalc= 'Total'
	AND		wwCycleCount=1

	--19. Which agitator was in the failed state for the longest time?  ________

--Example 13
	--In this query you will look specifically for the amount of time in percentage the malfunction 
	--flag was on:

	SELECT	DateTime, TagName, Value, StateTime
	FROM	History
	WHERE	TagName LIKE 'Agitator%.Malfunction'
	AND		DateTime > Dateadd(hh, -1, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'ValueState'
	AND		wwStateCalc= 'Percent'
	AND		wwCycleCount=1
	AND		Value=1

	--20. Agitator_001 malfunction flag was ON _____ % of the last hour.
	
--COUNTER RETRIEVAL 

--Example 14
	--In this query you will find how many gallons of product were released during the last hour of the
	--last shift.  There is a flow meter collecting data, which rolls over to zero after every 100 gallons 
	--are counted.  Notice that Qualitydetail =212 indicates there was a rollover on that cycle.

	SELECT	DateTime, TagName, Value, QualityDetail
	FROM	History
	WHERE	TagName = 'HistTest.M100_Totalizer3'
	AND		DateTime > Dateadd(hh, -6, Getdate())
	AND		DateTime <= Dateadd(hh, -5, Getdate())
	AND		wwRetrievalMode = 'counter'
	AND		wwCycleCount= 1

	--21. The total number of gallons produced is _____


--ROUNDTRIP RETRIEVAL 

--Example 15
	--The following query will retrieve the  average duration of a batch during the last 15 minutes.
	--It will give you two lengths, one for the longest batch starting with zero and one for the 
	--longest batch starting with one. 

	SELECT	DateTime, TagName, Value, StateTime
	FROM	History
	WHERE	TagName LIKE 'HistTest.BatchCount'
	AND		DateTime > Dateadd(mi, -15, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'RoundTrip'
	AND		wwStateCalc= 'AvgContained'
	AND		wwCycleCount=1

	--22.  In your process batches are marked with the BatchCount flag set to 1, so the average batch
	--duration during the last 15 minutes is ___________milliseconds. 

--Example 16
	--The following query will retrieve the length of the longest completed batch during the last 15 minutes.

	SELECT	DateTime, TagName, Value, StateTime
	FROM	History
	WHERE	TagName LIKE 'HistTest.BatchCount'
	AND		DateTime > Dateadd(mi, -15, Getdate())
	AND		DateTime <= Getdate()
	AND		wwRetrievalMode = 'RoundTrip'
	AND		wwStateCalc= 'MaxContained'
	AND		wwCycleCount=1
	AND		Value=1

	--23. The maximum length of a completed batch during the last 15 minutes was __________ milliseconds
	--24. Change the wwStateCalc parameter and find the minimum length of a completed batch:______milliseconds