SELECT tta.流水号,tta.货号,tta.物料代号,ttb.物料名称,tta.最后领料日期,tta.计划数量,tta.领用数量,tta.节料数量,
tta.超领数量,ROUND(tta.超领数量 * ttb.计划单价,2) AS 超料金额
FROM
(
SELECT t1.流水号,t1.货号,t1.物料代号,
ROUND(SUM(ISNULL(t1.数量,0)),2) AS 计划数量,
ROUND(SUM(ISNULL(t3.领用数量,0)),2) AS 领用数量,ROUND(SUM(ISNULL(t4.节料数量,0)),2) AS 节料数量,
ROUND(SUM(ISNULL(t3.领用数量,0)) - SUM(ISNULL(t1.数量,0))-SUM(ISNULL(t4.节料数量,0)),2) AS 超领数量,t3.最后领料日期 
FROM 

(
SELECT t4.流水号,t4.货号,ISNULL(t4.实际采购代号,t4.物料代号) AS 物料代号,SUM(ISNULL(t4.数量,0)) AS 数量
FROM  Table_订单表 t3 
     --inner join Table_采购_采购单_物料 t2  on t2.流水号 = t3.流水号
     INNER JOIN  Table_采购_采购计划表 t4 ON t4.流水号 = t3.流水号
WHERE t3.批准时间 > '2012-05-01'
GROUP BY t4.流水号,t4.货号,ISNULL(t4.实际采购代号,t4.物料代号)
) t1 LEFT JOIN Table_生产_超料处理 t2 
ON t1.流水号 = t2.流水号 AND t1.货号 = t2.货号 AND t1.物料代号 = t2.物料代号
INNER JOIN 
(
SELECT t2.订单流水号 AS 流水号,t2.货号,t2.物料代号,SUM(CASE WHEN t1.红冲 = '否' THEN t2.实发数量 ELSE -t2.实发数量 END) AS 领用数量,MAX(t1.日期) AS 最后领料日期
FROM Table_仓库_领料单 t1 INNER JOIN Table_仓库_领料单_物料 t2 ON t1.领料单编号 = t2.领料单编号
WHERE t1.FlagColumn > 0 AND t2.订单流水号 NOT IN ('FREEITEM','CTRLITEM')
GROUP BY t2.订单流水号,t2.货号,t2.物料代号
) t3 ON t1.流水号 = t3.流水号 AND t1.货号 = t3.货号 AND t1.物料代号 = t3.物料代号
LEFT JOIN 
(
SELECT t2.流水号,t2.货号,t2.物料代号,SUM(实收数量) AS 节料数量 FROM  Table_仓库_节料退库 t1 INNER JOIN  Table_仓库_节料退库_物料 t2 ON t1.节料单号 = t2.节料单号
WHERE ISNULL(t2.流水号,'') <> '' AND t1.FlagColumn = 2
GROUP BY t2.流水号,t2.货号,t2.物料代号
) t4 ON t1.流水号 = t4.流水号 AND t1.货号 = t4.货号 AND t1.物料代号 = t4.物料代号
WHERE t2.流水号 IS NULL
GROUP BY t1.流水号,t1.货号,t1.物料代号,t3.最后领料日期
HAVING SUM(ISNULL(t1.数量,0)) <> SUM(ISNULL(t3.领用数量,0))
) tta

LEFT JOIN Table_工程_物料档案 ttb ON tta.物料代号 = ttb.物料代号
WHERE ROUND(tta.超领数量 * ttb.计划单价,2) > 50