SET @date1:= '2006-01-01';
SET @date2:= '2006-03-31';
DROP TABLE IF EXISTS tmp_opitem;
CREATE TABLE tmp_opitem(id INT AUTO_INCREMENT,hn CHAR(7),an CHAR(9),vn CHAR(13),icode CHAR(7),qty mediumint, unitprice DOUBLE(10,2), income CHAR(2),rxdate DATE,PRIMARY KEY(id));
DROP TABLE IF EXISTS tmp_drug;
CREATE TABLE tmp_drug(icode CHAR(7),name CHAR(100),unitcost DOUBLE(10,2),PRIMARY KEY(icode));
INSERT INTO tmp_opitem(hn,an,vn,icode,qty,unitprice,income,rxdate) SELECT hn,an,vn,icode,qty,unitprice,income,rxdate FROM hos.opitemrece WHERE rxdate BETWEEN @date1 AND @date2;
INSERT INTO tmp_drug SELECT icode,name,unitcost FROM hos.drugitems;
INSERT INTO tmp_drug SELECT icode,name,unitcost FROM hos.nondrugitems;
SELECT LEFT(rxdate,7) month1,a.income,i.name,SUM(qty*b.unitcost),SUM(a.unitprice*qty)
FROM tmp_opitem a LEFT OUTER JOIN tmp_drug b ON b.icode=a.icode
LEFT OUTER JOIN income i ON i.income =a.income
WHERE i.income_group =12 AND an IS NULL
GROUP BY month1,income ORDER BY month1