CODEWITHSUNDEEP
sql-server
fishhead
fishhead

Reputation: 6109

how do I subtract values from two select statements

I would like to subtract one value from another value. The schema of the table 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 am lost...how do I do this.

thanks

ms-sql

Upvotes: 5

Views: 21672

Answers (4)

AaronLS
AaronLS

Reputation: 38367

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'

Upvotes: 0

David Espart
David Espart

Reputation: 11780

What is the type of the 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 have to cast it as an integer or whatever numeric type you want

Upvotes: 0

Andrew Bezzub
Andrew Bezzub

Reputation: 16032

Do you really need wrapping select statement?

You can declare two variables @value1 and @value2 and substruct 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

Upvotes: 1

Matthew Jones
Matthew Jones

Reputation: 26190

Total guess:

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)

Upvotes: 9

Related Questions