How do I subtract values ​​from two select statements

Question

I would like to subtract one value from another value. The table layout is as follows:

tag, datetime,value ------------ tag1, 2010-1-1 10:10:00, 123 tag2, 2010-2-2 10:12:00. 321 select * from ( (Select Max(Value) as [Value1] from History WHERE Datetime ='2010-1-1 10:10' and tagname ='tag1') as v1 - ( (Select Max(Value) as [Value2] from History WHERE Datetime ='2010-1-1 10:12' and Tagname ='tag2') as v2)) 

Obviously, I lost ... how to do it.

thanks

MS-SQL

Answer 1

General hunch:

 select v1.Value1 - v2.Value2 from (Select Max(Value) as [Value1] from History WHERE Datetime ='2010-1-1 10:10' and tagname ='tag1') as v1 CROSS JOIN ( (Select Max(Value) as [Value2] from History WHERE Datetime ='2010-1-1 10:12' and Tagname ='tag2') as v2) 

Answer 2

Do you really need to make an expression selectable?

You can declare two variables @value1 and @value2 and fine-tune them.

 declare @value1 int, @value2 int select @value1 = Max(Value) as [Value1] from History WHERE Datetime ='2010-1-1 10:10' and tagname ='tag1' select @value2 = Max(Value) as [Value2] from History WHERE Datetime ='2010-1-1 10:12' and Tagname ='tag2' select @value1 - @value2 

Answer 3

What is the type of value column? If it is already an integer, just do:

 SELECT (Select Max(Value) as [Value1] from History WHERE Datetime ='2010-1-1 10:10' and tagname ='tag1') as v1 - (Select Max(Value) as [Value2] from History WHERE Datetime ='2010-1-1 10:12' and Tagname ='tag2') as v2 

else you will need to specify it as an integer or any number type you need

Answer 4

 select h1.value - h2.value from History h1 inner join History h2 on h1.Datetime = '2010-1-1 10:10' and Datetime ='2010-1-1 10:12'