T-SQL join same table
Posted on 2008-10-17
Hi, TableA has the fields: Name, Date, Colour, Volume
Ed, 13/12/2008, Blue, 22
Ed, 14/12/2008, Grey, 26
Tom, 13/12/2008, Red, 15
Tom, 14/12/2008, Blue, 18
I want to have a query that does this:
Select Name, Colour, Volume(today) - Volume(today-1) as 'Current Volume'
So I would get this (joining on Name) for the date 14/12/2008:
Ed, Grey, 4
Tom, Blue, 3
So basically, for the volume field, it is to be the difference between the volume today and volume yesterday. I will be turning this into a stored procedure, so you can assume variables to hold dates.