--WWRESOLUTION (milliseconds)

--Example 1
	--wwResolution used in SELECT statement to find out the current resolution.

	SELECT	DateTime, TagName, Value, wwResolution
	FROM	History
	WHERE	TagName = 'M100.LT.PV'
	AND		wwRetrievalMode = 'cyclic'
	AND		DateTime > Dateadd(hh, -2, Getdate())
	AND		DateTime <= Getdate()

	--1. How many records are retrieved?______
	--2. The records retrieved are evenly distributed every __________ms during the last 2 hours.

--Example 2
	--Defining wwResolution.

	SELECT	DateTime, TagName, Value
	FROM	History
	WHERE	TagName ='M100.LT.PV'
	AND		wwRetrievalMode = 'cyclic'
	AND		DateTime > Dateadd(hh, -2, Getdate())
	AND		DateTime <= Getdate()
	AND		wwResolution=30000

	--3. How many records are retrieved now by adding wwResolution?_______
	--4. Change the  value of wwResolution to 3600000ms, how many records do you get?________
	--5. Find the value of wwResolution that allows retrieving one record per minute________

--WWCYCLECOUNT

--Example 3
	--wwCycleCount and Cyclic retrieval.
	
	SELECT	DateTime, TagName, Value, wwResolution
	FROM	History
	WHERE	TagName = 'M100.LT.PV'
	AND		wwRetrievalMode = 'cyclic'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()
	AND		wwCycleCount = 25

	--6. How many records were retrieved?_____________
	--7. Records were retrieved one every _____________ ms
	--8. Which value of wwCyclecount will retrieve one record per minute?___________

--Example 4
	--wwCycleCount and BestFit retrieval.

	SELECT	DateTime, TagName, Value, wwResolution
	FROM	History
	WHERE	TagName = 'M100.LT.PV'
	AND		wwRetrievalMode = 'BestFit'
	AND		DateTime > Dateadd(mi, -30, Getdate())
	AND  	DateTime <= Getdate()
	AND		wwCycleCount=3

	--9. How many records are retrieved with this query?_____________
	--10. How many records are retrieved if you change the  value of wwCycleCount to 6?_____________
	
--Example 5
	--wwCycleCount and wwResolution in an open query.

	SELECT * FROM OpenQuery(InSQL, '
		SELECT	DateTime, [M100.LT.PV], [Agitator_001.Malfunction], [Agitator_001.Malfunction.msg]
			FROM	WideHistory
			WHERE	DateTime > Dateadd(mi, -30, Getdate())
			AND		DateTime <= Getdate()
			AND		wwRetrievalMode = "Cyclic"
			AND		wwResolution = 60000
	')

	--11. How many records are retrieved with this query?_____________  
	--12. How many records are retrieved if you replace wwResolution with wwCyclecount = 30?____

--Example 6
	--In this query you will retrieve in a wide table the behavior of a malfunction flag in two agitators. 
	--Every row corresponds to a different combination of the malfunction flag values, in other words, every time 
	--the flag value changes in one of the agitators a new row is added.

	SELECT *
	FROM   OPENQUERY(INSQL, '
		SELECT  DateTime, [Agitator_001.Malfunction], [Agitator_002.Malfunction], wwResolution
			FROM	WideHistory
			WHERE 	DateTime > Dateadd(hh, -1, Getdate())
			AND  	DateTime <= Getdate()
	')

	--13. How many records are retrieved in this query?__________

--Example 7
	--Based on the results above, the following query will retrieve the total time both agitators had
	--the malfunction flag ON at the same time by using the  SUM function in the wwResolution column.
	
	SELECT SUM(WWResolution)
	FROM   OPENQUERY(INSQL, '
		SELECT  DateTime, [Agitator_001.Malfunction], [Agitator_002.Malfunction], wwResolution
			FROM	WideHistory
			WHERE	DateTime > Dateadd(hh, -1, Getdate())
			AND		DateTime <= Getdate()
			AND		[Agitator_001.Malfunction]=1 
			AND		[Agitator_002.Malfunction]=1
	')

	--14. For how many milliseconds was the malfunction flag ON in both agitators at the same time?__________

--WWEDGEDETECTION 

--Example 8
	--The temperature in your mixer cannot go over 400DegF to avoid downtime, in this query you will detect
	--temperatures going up over 390DegF to trigger a safety procedure.

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

	--15. How many ocurrences were retrieved?________
	--16. Change the wwEdgeDetection value to detect both Trailing and leading events, how many
	--records do you get?___________


--WWFILTER

--Example 9
	--ANALOG VALUE FILTERING - TODISCRETE()
	--You need to generate a query that will translate to one whenever the Mixer level is equal or 
	--greater than 78%:
	
	SELECT	DateTime, TagName, Value
	FROM	History
	WHERE	TagName = 'M100.LT.PV'
	AND		DateTime > Dateadd(mi, -10, Getdate()) 
	AND		DateTime <= Getdate()
	AND		wwFilter = 'ToDiscrete(78, >=)'

	--17. Change the wwFilter line in this query so any analog value under 15% will be considered a logical 
	--one and verify your results: wwFilter = 'ToDiscrete(_____, _____)'.

--Example 10 
	--ANALOG VALUE FILTERING - SNAPTO()
	--You are generating some reports and the peak temperature of your agitators seem to oscilate
	--a lot around 97DegC making the data in your reports look 'noisy':

	SELECT	DateTime, TagName, Value, QualityDetail 
	FROM	History
	WHERE	TagName = 'Agitator_001.Temp.PV'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()

--Example 11
	--The small oscillation of the temperature is not really important, so in this query you will
	--eliminate any variation of less than 1 degree and make your reports look cleaner:

	SELECT	DateTime, TagName, Value, QualityDetail 
	FROM	History
	WHERE	TagName = 'Agitator_001.Temp.PV'
	AND		DateTime > Dateadd(mi, -10, Getdate())
	AND		DateTime <= Getdate()
	AND		wwFilter = 'SnapTo(1,97)'

	--18. How many records are retrieved in this query?__________
	--19. Change the tolerance from 1 Deg to 0.95 Deg, and report how many times the temperature was getting 
	--"too close" to 98 Deg?_______
	
--Example 12
	--ANALOG VALUE FILTERING - SIGMALIMIT()
	--The mean weight for your final product is 455 mg.  You need to do some reporting, however some product 
	--samples have a significant deviation in the maximum weight. 
	--First retrieve the total number of records you have for the last hour:

	SELECT	COUNT(Value)
	FROM	History
	WHERE	TagName = 'HistTest.FinalWeight'
	AND		DateTime > Dateadd(hh, -1, Getdate())
	AND		DateTime <= Getdate()

	--20. What is the record count for the last hour?____ 
	
--Example 13
	--Now you will apply a statistical function to remove the outliers:

	SELECT	COUNT(Value)
	FROM	History
	WHERE	TagName = 'HistTest.FinalWeight'
	AND		DateTime > Dateadd(hh, -1, Getdate())
	AND		DateTime <= Getdate()
	AND		wwFilter = 'Sigmalimit(3)'

	--Change the value of the sigma limit and report how many records are retrieved each time.
	--21. What is the count result using a sigma limit=3?________
	--22. What is the count result using a sigma limit=2?________
	--23. What is the count result using a sigma limit=1?________