Does anyone have much experience writing WW InSQL queries.
I have done a bit of it.
There are two different ways to get data from it (it is an OLE DB provider to SQL Server).
1. Narrow Format using a query such as ...
SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(mi,-5,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
SELECT TagName, DateTime = convert(nvarchar, DateTime, 21), Value, vValue, Quality, QualityDetail = History.QualityDetail, QualityString, wwEdgeDetection
FROM History
LEFT JOIN QualityMap ON QualityMap.QualityDetail = History.QualityDetail
WHERE TagName IN ('IPS_Fwd_Energy.PV', 'IPS_Fwd_VAr_Total.PV')
AND Quality = 0
AND wwRetrievalMode = 'Cyclic'
AND wwCycleCount = 100
AND wwVersion = 'Latest'
AND wwEdgeDetection = 'NONE'
AND DateTime >= @StartDate
AND DateTime <= @EndDate
2) Or Wide Format such as ...
SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime = convert(nvarchar, DateTime, 21), [IPS_Fwd_Energy.PV], [IPS_Fwd_VAr_Total.PV], wwEdgeDetection
FROM WideHistory
WHERE wwRetrievalMode = 'Cyclic'
AND wwCycleCount = 100
AND wwVersion = 'Latest'
AND DateTime >= DateAdd(mi,-5,GetDate())
AND DateTime <= GetDate()")
The wide format is very convenient in a lot of situation since you get one date-time stamp and adding more data gives you a wider table.
The one problem I am having with the wide format is that you don't seem to be able to filter out bad quality data using the wide format. I asked the WW guys and they were no help.
Has anyone else encountered this or got another way to get out a wide format of only GOOD quality data?
I have done a bit of it.
There are two different ways to get data from it (it is an OLE DB provider to SQL Server).
1. Narrow Format using a query such as ...
SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(mi,-5,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
SELECT TagName, DateTime = convert(nvarchar, DateTime, 21), Value, vValue, Quality, QualityDetail = History.QualityDetail, QualityString, wwEdgeDetection
FROM History
LEFT JOIN QualityMap ON QualityMap.QualityDetail = History.QualityDetail
WHERE TagName IN ('IPS_Fwd_Energy.PV', 'IPS_Fwd_VAr_Total.PV')
AND Quality = 0
AND wwRetrievalMode = 'Cyclic'
AND wwCycleCount = 100
AND wwVersion = 'Latest'
AND wwEdgeDetection = 'NONE'
AND DateTime >= @StartDate
AND DateTime <= @EndDate
2) Or Wide Format such as ...
SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime = convert(nvarchar, DateTime, 21), [IPS_Fwd_Energy.PV], [IPS_Fwd_VAr_Total.PV], wwEdgeDetection
FROM WideHistory
WHERE wwRetrievalMode = 'Cyclic'
AND wwCycleCount = 100
AND wwVersion = 'Latest'
AND DateTime >= DateAdd(mi,-5,GetDate())
AND DateTime <= GetDate()")
The wide format is very convenient in a lot of situation since you get one date-time stamp and adding more data gives you a wider table.
The one problem I am having with the wide format is that you don't seem to be able to filter out bad quality data using the wide format. I asked the WW guys and they were no help.
Has anyone else encountered this or got another way to get out a wide format of only GOOD quality data?