--TIER-1 REPLICATION

--QUERYING THE ANALOGSUMMARYHISTORY VIEW

--Example 1
	--By default retrieves aggregations for the last hour.

	SELECT	*
	FROM	AnalogSummaryHistory
	WHERE	TagName LIKE 'M%.5M'

	--1. The Average value for M200.LT.PV.5M is_______

--Example 2
	--Now you will retrieve aggregates for the last 30 minutes.

	SELECT	*
	FROM	AnalogSummaryHistory
	WHERE	TagName='M200.LT.PV.5M'
	AND		StartDateTime >= Dateadd(mi, -30, Getdate())
	AND		EndDateTime <= Getdate()

	--2. The maximum value for M200.LT.PV.5M is__________
	--Modify the query above to retrieve the aggregates for 3 periods covering the last 30 minutes.
	--3. M200.LT.PV.5M average for first period______
	--4. M200.LT.PV.5M average for second period______
	--5. M200.LT.PV.5M average for third period______


--QUERYING THE STATESUMMARYHISTORY TABLE

--Example 3

	SELECT	*
	FROM	StateSummaryHistory
	WHERE	TagName = 'Inlet1_001.PV.IsActive1.S3M'

	--6. Why did the query retrieve more than one row?________________________
	--7. What is the StateTimeTotalContained for Value=1?_____________ ms

--Example 4
	--Querying summarized values for a specific state of the attribute.

	SELECT	*
	FROM	StateSummaryHistory
	WHERE	TagName = 'Inlet2_002.PV.IsActive1.S3M'
	AND		Value=1
	AND		wwCycleCount=2

	--8. How many times did the valve switch to the value 1 during the last 30 minutes?__________ 
	--9. Modify the wwCycleCount option and report the StateTimeMin duration for the last 20 minutes:___________ms

--TIER-2 REPLICATION

	--To be able to execute the following queries, connect the SQL Server Management Studio to your Tier-2 Historian 
	--and select  the Runtime database.  Check your lab steps for further details.
	
--Example 5
	--In this query you will retrieve historized data from your Tier-2 Historian Server for the simple replication tags. 
	--Replace the XXX string with the name of your Tier-1 Historian Server 
        --Remember the replicated tag scheme includes the name
	--of the Historian Server where the original tag resides, that is, your Tier-1.

	SELECT  DateTime, TagName, Value
	FROM	History
	WHERE	TagName='XXX.M100.Agitator.Speed.PV'
	AND		DateTime > Dateadd(mi, -30, Getdate())
	AND		DateTime <= Getdate()

--Example 6
	--Wide table query.
	--Replace the XXX string with the name of your Tier-1 Historian Server.

	SELECT * FROM OpenQuery(InSQL, '
		SELECT DateTime, [XXX.M200.Agitator.Temp.PV], [XXX.M100.Agitator.Temp.PV]
			FROM   WideHistory
			WHERE  DateTime > Dateadd(mi, -15, Getdate())
			AND    DateTime <= Getdate()
	')