question

Clint,

The statement

select h.tag, max(value) from v_history as h where tag in ('Tagname1') or tag in ('tagname2')

does not appear to be correct. First, there is no need to write in ('Tagname1') or tag in ('tagname2'), this part should be restated like this

tag in ('Tagname1', 'Tagname2')

Second, if your v_history has one or more records per tag then you should group by the tag field in order to see the max(value). In other words, your statement should look like this:

select h.tag, max(value) from v_history as h 
where tag in ('Tagname1', 'Tagname2')
group by h.tag

Finally, if you would like to get the list of tags from AM shift along withh their max(value) from v-history table then you should join those 2 tables by tag. There is no need to write cte for it, but if you need one then your statement should look like this (I follow your naming conventions):

with am (am_tag) as 
(select Tag as am_tag From Tags where shift = 'Am')
select h.tag, max(h.value) 
from v_history as h inner join am
on am.am_tag = h.Tag
group by h.tag;

If your history table has has zero or more records per tag rather than has one or more per tag and you would like to include the tags which have no history records then you can change the inner join to left join. This will yield the max(value) of null for every Tag which has no history.

Oleg

Here is the updated solution which will work. It is based on some assumptions based on the conversation in the comments below. Please let me know if these are correct. The assumptions are as follows:

1. There is a view named v_history. It is designed as though it does not need any parameters yet it fails to run if the where clause specifying at least one tag in the actual SQL statement querying the data from v_history is present. When statement fails to run to due its lacking the where clause with at least one tag, the view throws error stating the lack of a tag in the where clause as the reason for view failure. This is a very strange design, but lets live with it.
2. I will assume that the view can return more than one record per tag and therefore, I will use the group by tag if I need to figure the max(value).

The solution below uses dynamic SQL. It is not the most efficient way of getting the data you need, but then again, I do not know the history behind the v_history design. With properly designed view, Kev's solution would work just fine.

declare @sql nvarchar(max);
declare @tags varchar(max);

-- populate the list of tags dynamically in one statement
select @tags = isnull(@tags + ', ', '') + '''' + Tag + '''' from Tags where shift = 'AM';

-- feed the list of tags to the final sql statement
select @sql = 'select Tag, max(value) from v_history where Tag in (' + @tags + ') group by Tag;';

-- execute the sql
exec sp_executesql @sql;

Oleg