Query to get most recent price between dates

[aetedford]

I marked a previous thread because I made it way too complicated for what I am looking for.



I have a table below that I need to get a start price and end price for each PART for dates between 1/1/18 and 3/31/18.

For example, PART 1000 would have a start price of $3.85 and an end price of $4.12 for a % change of 7.01%
I'd like to have the resulting query show PART, 1/1/18 PRICE, 3/31/18 PRICE, and % CHANGE

If there is no price for 1/1/18 (part 2000) it wouldn't calculate % CHANGE.

PART	DATE	PRICE
1000	1/1/2014	$ 4.00
1000	4/1/2017	$ 3.85
1000	3/12/2018	$ 4.12
1003	1/1/2018	$ 0.50
1003	1/2/2018	$ 0.75
1003	3/31/2018	$ 0.88
2000	2/12/2018	$ 4.12
2050	12/31/2017	$ 3.33
2050	4/1/2018	$ 3.00

Any assistance is greatly appreciated. Thanks

[isladogs]

Just to clarify the start and end prices for each part are
1000: $3.85, $4.12
1003: $0.5, $0.88
2000: N/A, $4.12
2050: $3.33, $3.00

In each case, it uses the last price on or before the specified dates????

[aetedford]

Item 2050 would be $3.33 and $3.33 because the new price kicks in after 3/31/18. But, yes, you are correct with each case.

Thank you

[isladogs]

Ah yes. I live in the UK so misread the last date.
I think the will either need several queries or the use of subqueries.

Suggest you upload a cut down db with some more sample data to make it easier for someone to assist

Is this something you will need to do every quarter year.
If so, it may be easier to create a new denormalised table with all the required fields and populate that each quarter