Home -> Usenet -> comp.databases.mysql -> Select rows based on aproximate value

Select rows based on aproximate value

From: Jan Stozek <conus-spm_at_chello.pl>
Date: Mon, 09 Feb 2015 20:26:19 +0100
Message-ID: <2829423.AZb7WE1xBJ_at_pc-jasio.stozek.waw.pl> 





Hi,



        Sorry, if this is a lame question, but:



        I have a table with prices of some goods registered for some 
dates, such as:


	2014-01-10	$120
	2014-01-11	$121
	2014-01-13	$119
	2014-01-15	$118

	I would like to create a view which would fill the missing 


prices, ie. quote the existing prices where available, and return the 
last known price if the price for a particular day is unknown, perhaps 
with a reasonable limit in case a product disappears from the market:


	2014-01-10	$120
	2014-01-11	$121
	2014-01-12	$121
	2014-01-13	$119
	2014-01-14	$119
	2014-01-15	$118
	2014-01-16	$118
	2014-01-17	$118
	2014-01-18	$118
	..........
	2014-02-01	NULL

	If it was a mere matter of calculating the price one time, I 


would probably use a function for that, but I need later to combine 
the figures with the data from other tables with a date being an index 
field.


	2014-01-10	$120	data a
	2014-01-11	$121	data b
	2014-01-12	$121	data c
	2014-01-13	$119	data d
	2014-01-14	$119	data e
	2014-01-15	$118	NULL
	2014-01-16	$118	NULL
	2014-01-17	$118	data f
	2014-01-18	$118	data g

	Without it on 12th, 14th, and after 15th, the price field would 


return NULL, which I want to avoid, as it could ruin calculations 
using the data.



        If you believe that a view construction is not the best 
solution here, I'd be grateful for pointing me in the right direction 
- as you can see, the databases are not a field of my deepest 
expertise. ;)



        Thank you very much for your hints.



PS. The engine behind it is actually MariaDB, but I believe that it's 
fairly compatible with mysql, isn't it?


-- 
Best regards,

(js).

PS. When responding directly, please remove dash with all subsequent 
letters from the email address.


Received on Mon Feb 09 2015 - 20:26:19 CET












Original text of this message