CODEWITHSUNDEEP
sql-server
Rick
Rick

Reputation: 407

Subtract data from the same column

I am having difficulties subtracting data from the same column. My data structure looks like this: 

TimeStamp  ID  state  Gallons
1/01/2012   1    NY     100
1/05/2012   1    NY      90
1/10/2012   1    NY      70
1/15/2012   1    NY      40

So I would like to get the result of (100-40) = 60 gallons used for that time period. I tried using the following query: 

SELECT T1.Gallons, T1.Gallons -T1.Gallons AS 'Gallons used'
FROM Table 1 AS T1 
where Date = '2012-07-01'
ORDER BY Gallons

It seems like a simple way of doing this. However, I can not find an efficient way of including the timestamp as a way of retrieving the right values (ex. gallon value on 1/1/2012 - gallon value on 1/30/2012).

ps. The value of "Gallons" will always decrease

Upvotes: 0

Views: 2353

Answers (4)

Rab
Rab

Reputation: 35582

select max(gallons) - min(gallons)
from table1 
group by Date
having Date = '2012-07-01'

Upvotes: 5

Konrad Viltersten
Konrad Viltersten

Reputation: 39268

Will you always be comparing the maximum and minimum amount? If not, maybe something like the following will suffice (generic sample)? The first time you'll run the script you'll get an error but you can disregard it.

drop table TableName
create table TableName (id int, value int)

insert into TableName values (3, 20)
insert into TableName values (4, 17)
insert into TableName values (5, 16)
insert into TableName values (6, 12)
insert into TableName values (7, 10)

select t1.value - t2.value
from TableName as t1 inner join TableName as t2 
  on t1.id = 5 and t2.id = 6

The thing you're asking for is the last line. After we've inner joined the table onto itself, we simply request to match the two lines with certain ids. Or, as in your case, the different dates.

Upvotes: 0

Johnny B
Johnny B

Reputation: 460

SELECT MAX(gallons) - MIN(gallons) AS 'GallonsUsed'
FROM table;

would work in this instance, if you are doing a monthly report this could work. How does the value of gallons never increase? what happens when you run out?

Upvotes: 0

juergen d
juergen d

Reputation: 204904

try

select max(gallons) - min(gallons)
from table1 t1
where date between '2012-01-01' and '2012-01-31'

Upvotes: 2

Related Questions