Very new to SQL and could use some advice/ideas.
I'm using a SQL query to retreive and format data from an existing table (Wonderware/Runtime/AnalogSummaryHistory) into Excel 2010. My query works for getting the result format that I want, but only for a single/common aggregate function. I can't figure-out how to associate separate aggregate functions to each tag of interest.
I would like to enhance it for the following functionality.
I would like to define a list(s), (hardcoded is fine, dynamic list pulled from an Excel cell range would be even more trick),
Tags --> (Tag1, Tag2,...)
Func---------> (AggType1, AggType2,...)
where AggTypeX would be the type of aggregate function I want for the corresponding TagX (MIN/MAX/AVG/LAST, etc., available as column data in existing source table)(AggTypes in defined list could repeat) I would then like to have the query run through this list(s) to create a result of TagX columns with corresponding aggregate function type data values. I would prefer to have the result columns ordered L-R in the sequence defined in the initial Tags list.
Additionally, I would like to be able to run this query in an Excel 2010 connection definition. *I have attempted to build the original query using temp tables, but can't get it to run within Excel. Appears to bomb on insert to fill temp table. Could be my inexperience, could be a limitation of Excel, DK.
Result I am looking for:
ShortDate | Tag1 | Tag2
-------------------------------------------------------------------
Date1 | Tag1Date1Agg1Val | Tag2Date1Agg2Val
Date2 | Tag1Date2Agg1Val | Tag2Date2Agg2Val
Greatly appreciate any feedback.
My current query/setup:
Existing table (AnalogSummaryHistory):
StartDateTime | TagName | Aggregate (ex. LAST)
--------------------------------------------------------------
Date1 | Tag1 | Tag1Date1AggVal
Date2 | Tag1 | Tag1Date2AggVal
Date1 | Tag2 | Tag2Date1AggVal
Date2 | Tag2 | Tag2Date2AggVal
My query:
select * from
(
SELECT top 100 percent convert(varchar(10), StartDateTime, 101) as ShortDate, TagName, Last
FROM AnalogSummaryHistory
WHERE TagName IN ('Tag1','Tag2')
AND wwVersion = 'Latest'
AND wwRetrievalMode = 'Cyclic'
AND wwResolution=24*60*60*1000
AND StartDateTime >= '20171001 00:00:00.000'
AND EndDateTime <= '20171101 00:00:00.000'
)x
pivot
(
max(Last)
for Tagname in ([Tag1],[Tag2])
) pvt
Result:
ShortDate | Tag1 | Tag2
--------------------------------------------------------------
Date1 | Tag1Date1AggVal | Tag2Date1AggVal
Date2 | Tag1Date2AggVal | Tag2Date2AggVal
