I'm trying to correct a situation I've created after
combining separate date/time columns into a datetime column.
Here are skeletons of new table definitions.
CREATE TABLE products
(id mediumint unsigned primary key not null auto_increment,
item varchar(40) not null,
priceA decimal(6,3),
priceB decimal(6,3),
UNIQUE KEY `item_name` (item))
CREATE TABLE sales
(id int unsigned primary key not null auto_increment,
date date not null,
time time not null,
person varchar(25),
item varchar(40),
units decimal(7,4) unsigned not null,
INDEX `person_date_time` (person, date, time))
I have a union much like the one below that seems to work well,
but I'm hoping someone has time to advise me what the optimizer
will (or won't) do given the proposed index and GROUP BY/
ORDER BY clauses, before I start changing my tables.
(SELECT date AS date,
time_format(time,'%H:%i') AS time,
substr(sales.item,1,18) AS item,
round(priceA * units,2),
round(priceB * units,2)
FROM sales
LEFT JOIN products
ON sales.item = products.item
WHERE sales.person = 'george'
AND sales.date BETWEEN '2005-08-01' AND '2005-08-14')
union
(SELECT date AS date,
time_format(time,'%H:%i') AS time,
'ZZ' AS item,
round(sum(priceA * units),2),
round(sum(priceB * units),2)
FROM sales
LEFT JOIN products
ON sales.item = products.item
WHERE sales.person = 'george'
AND date BETWEEN '2005-08-01' AND '2005-08-14'
GROUP BY date, time)
union
(SELECT date AS date,
'23:59' AS time,
'ZZZZ' AS item,
round(sum(priceA * units),2),
round(sum(priceB * units),2)
FROM sales
LEFT JOIN products
ON sales.item = products.item
WHERE sales.person = 'george'
AND date BETWEEN '2005-08-01' AND '2005-08-14'
GROUP BY date)
ORDER BY date, time, item
The result itemizes a sale, totals a sale, and totals the
day's sales, by person. Something like this:
2005-08-01 07:30 Apples 39.29 42.40
2005-08-01 07:30 Oranges 9.29 10.02
2005-08-01 07:30 Zucchini .65 .69
2005-08-01 07:30 ZZ 49.23 53.11
2005-08-01 08:01 Bears 9.99 10.20
2005-08-01 08:01 Lions 7.28 7.49
2005-08-01 08:01 Tigers .65 .69
2005-08-01 08:01 ZZ 17.92 18.38
2005-08-01 23:59 ZZZZ 67.15 71.49
2005-08-02 <and so on>
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
