Using the example of stock prices, suppose I have daily prices for a bunch of companys for the past year. Assuming I have name, date & price columns in my table, how would I get the change in price between any 2 dates for every company? So, for example, I would like to get the price change from yesterday to today (positive or negative) for each unique company. Thanks...
nbrege, this is the first thing that comes to mind. There are probably other ways that will work, too.
, START_DATA.PRICE_DATE AS START_DATE
, START_DATA.CLOSING_PRICE AS START_PRICE
, END_DATA.PRICE_DATE AS END_DATE
, END_DATA.CLOSING_PRICE AS END_PRICE
, END_DATA.CLOSING_PRICE - START_DATA.CLOSING_PRICE AS CHANGE_AMOUNT
FROM (SELECT COMP_NAME
WHERE PRICE_DATE = 'your starting date value'
) AS START_DATA
WHERE PRICE_DATE = 'your ending date value'
) AS END_DATA
ON START_DATA.COMP_NAME = END_DATA.COMP_NAME