Pasted

SELECT consignor, bol, auction_pallets, lots_sold, FORMAT(Revenue,2) Revenue, FORMAT(GREATEST(0, Total_Cost - Revenue),2) cash_in_play, FORMAT(Total_Cost,2) Total_Cost, FORMAT(Recovered,2) Cost_Of_Sold, ROUND(Total_Cost-Recovered,2) Cost_Unrecovered
 FROM
 (SELECT
 COUNT(li.id) lots_sold,
 'All' consignor,
 'All' bol,
 '-' auction_pallets,
 (
SELECT SUM(li2.hammer_price * 1.2 + 7.5) FROM lot_item li2
INNER JOIN lot_item_cust_data bol2 ON (bol2.lot_item_id = li2.id and bol2.lot_item_cust_field_id = 71)
WHERE bol2.text=bol3.text AND li2.hammer_price IS NOT NULL
) Revenue,
 (
SELECT SUM(cost2.numeric/pallets2.numeric) FROM lot_item li2
INNER JOIN lot_item_cust_data bol2 ON (bol2.lot_item_id = li2.id and bol2.lot_item_cust_field_id = 71)
INNER JOIN lot_item_cust_data pallets2 ON (pallets2.lot_item_id = li2.id and pallets2.lot_item_cust_field_id = 74)
INNER JOIN lot_item_cust_data cost2 ON (cost2.lot_item_id = li2.id and cost2.lot_item_cust_field_id = 76)
WHERE bol2.text=bol3.text AND li2.hammer_price IS NOT NULL
) Recovered,
 '-' Total_Cost
 FROM lot_item li3
 LEFT JOIN user u3 ON (u3.id=li3.consignor_id)
 INNER JOIN lot_item_cust_data bol3 ON (bol3.lot_item_id = li3.id and bol3.lot_item_cust_field_id = 71)
 INNER JOIN lot_item_cust_data pallets3 ON (pallets3.lot_item_id = li3.id and pallets3.lot_item_cust_field_id = 74)
 INNER JOIN lot_item_cust_data cost3 ON (cost3.lot_item_id = li3.id and cost3.lot_item_cust_field_id = 76)
 WHERE li3.date_sold {DateRange} AND cost3.numeric IS NOT NULL AND u3.username NOT IN ('home depot','Rock and Republic','Acks Liquidation','Clean Bay Area','Qualitycloseouts')
 ) tmp2