A common table expression (CTE) is used in this example to create a temporary result set with a sequential number that allows us to do the arithmetic required to calculate percent change.
Let’s set up a test table:
CREATE TABLE gas_price_data ( station_id int NOT NULL, data_date date NOT NULL, price decimal(4,3) NULL, PRIMARY KEY (station_id, data_date) ) |
Give it some data:
INSERT INTO gas_price_data (station_id, data_date, price) VALUES (1, '2012-06-18', '3.999'), (1, '2012-06-19', '3.849'), (1, '2012-06-20', '3.789'), (2, '2012-06-18', '4.019'), (2, '2012-06-19', '3.879'), (2, '2012-06-20', '3.829') |
Let’s calculate the percent change between the two most recent entries for station 1 using a CTE:
WITH t1 AS ( SELECT TOP 2 price, ROW_NUMBER() OVER(ORDER BY data_date DESC) AS row_id FROM gas_price_data WHERE station_id = 1 ) SELECT ((SELECT price FROM t1 WHERE row_id = 1) - (SELECT price FROM t1 WHERE row_id = 2)) / (SELECT price FROM t1 WHERE row_id = 2) AS 'change' |
If you wanted to calculate the percent change for each station, you could create a function to fetch the percent change by station like so:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE FUNCTION get_percent_change (@station_id int) RETURNS decimal(6,5) AS BEGIN DECLARE @change decimal(6,5) ;WITH t1 AS ( SELECT TOP 2 price, ROW_NUMBER() OVER(ORDER BY data_date DESC) AS row_id FROM gas_price_data WHERE station_id = @station_id ) SELECT @change = ((SELECT price FROM t1 WHERE row_id = 1) - (SELECT price FROM t1 WHERE row_id = 2)) / (SELECT price FROM t1 WHERE row_id = 2) RETURN @change END |
Add your function into your query like so:
SELECT DISTINCT a.station_id, (SELECT get_percent_change (a.station_id)) AS 'change' FROM gas_price_data |
The function does not do any error checking.
