select *,'-', glan_cnt-sanix_cnt, glan_quantity-sanix_quantity from 
	(select 
		b.id, 
		hospital_id, 
		dealer_id, 
		lot_number , 
		count(*) as glan_cnt, 
		sum(shipping_quantity) as glan_quantity  
	from shipping_details as a left join shippings as b on a.shipping_id=b.id where b.shipping_status_id=3 and input_date > "2011-02-28" 
	group by hospital_id, dealer_id, lot_number) as c left join 
	(select 
		e.id, 
		warehouse_id, 
		customer_id,
		lot_number,
		count(*) as sanix_cnt,
		sum(quantity) as sanix_quantity
	from sanix_production.shipping_details as d left join sanix_production.shippings as e on d.shipping_id=e.id where shipping_state_code=1 and invalid_flag_code=0 and input_date>"2011-02-28"
	group by warehouse_id, customer_id, lot_number) as f on c.hospital_id=f.warehouse_id and c.dealer_id=f.customer_id and c.lot_number=f.lot_number
	INTO OUTFILE "/tmp/shipping_match.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

select *,'-', glan_cnt-sanix_cnt, glan_quantity-sanix_quantity from
        (select
                b.id,
                hospital_id,
                dealer_id,
                lot_number ,
                count(*) as glan_cnt,
                sum(shipping_quantity) as glan_quantity
        from shipping_details as a left join shippings as b on a.shipping_id=b.id where b.shipping_status_id=3 and input_date > "2011-02-28"
        group by hospital_id, dealer_id, lot_number) as c right join
        (select
                e.id,
                warehouse_id,
                customer_id,
                lot_number,
                count(*) as sanix_cnt,
                sum(quantity) as sanix_quantity
        from sanix_production.shipping_details as d left join sanix_production.shippings as e on d.shipping_id=e.id where shipping_state_code=1 and invalid_flag_code=0 and input_date>"2011-02-28"
        group by warehouse_id, customer_id, lot_number) as f on c.hospital_id=f.warehouse_id and c.dealer_id=f.customer_id and c.lot_number=f.lot_number
        INTO OUTFILE "/tmp/shipping_match2.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

