I have three tables that work together.
"s_product" is a list of farm products with an autoincrementing ID.
"s_product_market_prices" is a list of market pricings, obtained from
various sources. Each one is dated and refers to exactly one s_product
record via its ID.
"s_product_harvest" is a list of harvests, including s_product.ID,
amount, and date/time.
Now I want to generate a report showing the harvest sums and their
values, based upon an appropriate market pricing. It was all happy
when I only had one pricing per product, but then I added new dated
pricings, and got unexpected results.
I'd be happy if the pricings used were simply in the same year as the
harvest, but it seems like it picks a random one when I do a LEFT JOIN
on these tables. When I put additional AND clauses on the join to get
it to pick a price within the desired date range, it seems to affect
the number of harvests summed, and they are reduced somehow.
(Apologies for not fully qualifying the unexpected results; I'm hoping
someone can look at this and quickly show me something stupid I've
done! :-)
Here's the report:
http://www.EcoReality.org/wiki/2009_harvest
which is generated by the following SQL:
----------------
SELECT
product AS ID,
MAX(s_product.name) AS Name,
SUM(quantity) AS Quantity,
MIN(harvest.units) AS Units,
CONCAT('$', ROUND((SUM(quantity) * prices.price), 2)) AS Value,
prices.market_type AS `R-W`,
COUNT(*) AS Harvests,
MIN(date) AS Begin,
MAX(date) AS End
FROM s_product_harvest harvest
INNER JOIN s_product on s_product.ID = harvest.product
LEFT OUTER JOIN s_product_market_prices prices ON ID = prices.product_ID
WHERE date >= '{{{1}}}-01-01' AND date <= '{{{1}}}-12-31 23:59:59'
GROUP BY s_product.name
----------------
(Note that the token "{{{1}}}" is replaced with a four-digit year,
like "2009".)
My first impulse was to change the LEFT OUTER JOIN to:
s_product_market_prices prices ON ID = prices.product_ID AND
prices.price_date >= '{{{1}}}-01-10' AND prices.price_date <=
'{{{1}}}-12-31 23:59:59'
So that the prices table would only join for the desired year.
What am I doing wrong here?
Following are schemas of the three tables:
CREATE TABLE IF NOT EXISTS `s_product` (
`ID` int(10) unsigned NOT NULL auto_increment,
`super` int(11) default NULL COMMENT 'generalization',
`name` varchar(31) character set utf8 NOT NULL,
`units`
enum
('kilograms
','grams','pounds','ounces','liters','each','cords','bales') character
set utf8 NOT NULL default 'kilograms' COMMENT 'preferred unit',
`description` varchar(255) character set utf8 NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `Name` (`name`),
KEY `Description` (`description`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='list of
EcoReality farm products' AUTO_INCREMENT=86 ;
CREATE TABLE IF NOT EXISTS `s_product_harvest` (
`date` datetime NOT NULL COMMENT 'Date and time of harvest.',
`product` int(11) NOT NULL default '53',
`resource` varchar(255) character set utf8 NOT NULL COMMENT
'Particular animal or tree, etc.',
`quantity` decimal(10,2) NOT NULL default '0.80',
`units`
enum
('kilograms
','grams','pounds','ounces','liters','each','cords','bales') character
set utf8 NOT NULL default 'kilograms',
`who1` smallint(5) unsigned NOT NULL default '2' COMMENT 'Who
harvested this resource?',
`who2` smallint(5) unsigned NOT NULL default '4' COMMENT 'Who helped
harvest this resource?',
`notes` varchar(255) character set utf8 NOT NULL,
KEY `product` (`product`),
KEY `date` (`date`),
KEY `who1` (`who1`,`who2`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
COMMENT='historical list of EcoReality farm products harvested';
CREATE TABLE IF NOT EXISTS `s_product_market_prices` (
`product_ID` int(11) NOT NULL,
`price_date` date NOT NULL,
`price_source` varchar(255) character set utf8 NOT NULL,
`market_type` enum('retail','wholesale') character set utf8 NOT NULL
default 'wholesale',
`price` float NOT NULL,
`units` enum('kilograms','grams','pounds','ounces','liters','each')
character set utf8 NOT NULL default 'kilograms' COMMENT 'change in
sync with s_product_harvest.units',
PRIMARY KEY (`product_ID`,`price_date`) )
ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='market
pricing information for EcoReality products';
Thanks for whatever help you can offer!
----------------
The Apocalypse has Four Horsemen: climate change, habitat destruction,
industrial agriculture, and poverty. Each Horseman holds a whip called
Growth in his hand. None can be stopped unless all are stopped. --
David Foley
:::: Jan Steinman, EcoReality Co-op ::::
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
