Union Query?

If anyone could help with this i would greatly appreciate it.

i have a database that has 5000 tags and i need to have a query return the
following result.

Tag Time Value
A1 16:34 7.23
A1 23:51 1.32
A2 13:12 0.67
A2 22:14 2.89

The following query is for only one tag to give max/min and thier timestamp,
how do i code for the above result?

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(dd,-1,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
(SELECT Top 1 TagName, CONVERT(varchar(15), DateTime, 108) as [Time], value
= convert(decimal(38, 3), Value)
FROM v_AnalogHistory
WHERE TagName IN ('A1')
AND wwVersion = 'Latest'
AND wwRetrievalMode = 'Cyclic'
AND wwRowCount = 1000
AND DateTime >= @StartDate
AND DateTime <= @EndDate
and (value = (SELECT min(value) as value
FROM v_AnalogHistory
WHERE TagName IN ('A1')
AND wwVersion = 'Latest'
AND wwRetrievalMode = 'Cyclic'
AND wwRowCount = 1000
AND DateTime >= @StartDate
AND DateTime <= @EndDate)))
union
(SELECT Top 1 TagName, CONVERT(varchar(15), DateTime, 108) as [Time], value
= convert(decimal(38, 3), Value)
FROM v_AnalogHistory
WHERE TagName IN ('A1')
AND wwVersion = 'Latest'
AND wwRetrievalMode = 'Cyclic'
AND wwRowCount = 1000
AND DateTime >= @StartDate
AND DateTime <= @EndDate
and (value = (SELECT max(value) as value
FROM v_AnalogHistory
WHERE TagName IN ('A1')
AND wwVersion = 'Latest'
AND wwRetrievalMode = 'Cyclic'
AND wwRowCount = 1000
AND DateTime >= @StartDate
AND DateTime <= @EndDate)))
.

Relevant Pages

  • Re: Passing DateDiff rather than Actual Dates as Parameters
    ... So for instance you could pass a date in international format. ... Declare @real_date datetime ... > SELECT @StartDate = DATEDIFF ... > SELECT @EndDate = DATEDIFF ...
    (comp.databases.ms-sqlserver)
  • Re: Using Function Table ? using Sql Server 2008
    ... which is basically a datetime version of your original. ... Special attention to execution plan detail is a good practice in such cases to ensure the query and indexes are as optimal as possible. ... @EndDate datetime ... SELECT DATEADD(day, N-1, @StartDate) ...
    (microsoft.public.sqlserver.programming)
  • Re: Using Function Table ? using Sql Server 2008
    ... As I have no sound knowledge about performance and tuning of queries, however I have heard that performance can be evaluated using Execution Plan, is it true? ... @StartDate datetime, ... SELECT DATEADD(day, N, @StartDate) ... @Start BIGINT, ...
    (microsoft.public.sqlserver.programming)
  • Re: parameterized datetime query
    ... This converts the date to a varchar in the format mm/dd/yyyy, and back to datetime. ... Where column Between @startdate and @enddate, where you would build startdate and enddate in your C# code to be the desired date at 00:00:00 and the desired date at 23:59:59, respectively. ... DateTime dateWithoutTime = dateToSearch.Date; ...
    (microsoft.public.dotnet.languages.csharp)