asked on

Calculating Daily Price

I have a table that holds price change data and I need to be able to calculate what the price was on a given date. Example What was the price on 2014-08-05.

The ultimate goal is to get the starting and ending price for a date range. BETWEEN 2014-08-05 AND 2014-08-20

DATE        PRICE ITEM
2014-08-02      4.46 1
2014-08-09      4.89 1
2014-08-13      4.69 1
2014-08-25      4.99 1
2014-08-30      4.89 1

Jim Horn

Assuming there can be only one row per id / date, give this a whirl..

SELECT id, Price, Max(Date) as current_price_date
FROM YourTable
WHERE Date <= '2014-08-05'
GROUP BY id, Price

SELECT id, Price, Max(Date) as current_price_date
FROM YourTable
WHERE Date <= '2014-08-20'
GROUP BY id, Price

Open in new window

bushido121

ASKER

I don't think I explained it very well. I'm trying to get the price for that item on 8/5 and the price on 8/20 so I can calculate the difference.

On 8/5 the item was 4.46 (I needed to go back to 8/2 to get the last price change)
On 8/20 the item was 4.69 (I needed to go back to 8/13 to get the last price change)

Ultimately I'm looking for the impact of Item 1 from 8/2 to 8/20. (4.69 - 4.46 = .23)

Obviously there are thousands of records in the table.

DATE PRICE ITEM
2014-08-02 4.46 1
2014-08-09 4.89 1
2014-08-13 4.69 1
2014-08-25 4.99 1
2014-08-30 4.89 1

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bushido121

ASKER

Awesome! Thank You