核对销售发票时用的语句

dannis

--汇总报表（核对总数用）
SELECT 年月,SUM(A.销售总数量) AS 销售总数量,SUM(A.外购总数量) AS 外购总数量,
SUM(A.销售总不含税金额) AS 销售总不含税金额,SUM(A.外购不含税金额) AS 外购不含税金额,
SUM(A.销售总含税金额) AS 销售总含税金额,SUM(A.外购含税金额) AS 外购含税金额 FROM
(
--销售发票总数量、不含税金额、含税金额报表（所有）
SELECT LEFT(CONVERT(CHAR,B.ACCDAT_0,112),6) AS 年月,SUM(A.QTY_0*B.SNS_0) AS 销售总数量,0 AS 外购总数量,
ROUND(SUM(A.AMTNOTLIN_0*B.SNS_0*B.RATRPT_0),2) AS 销售总不含税金额,0 AS 外购不含税金额,
ROUND(SUM(A.AMTATILIN_0*B.SNS_0*B.RATRPT_0),2) AS 销售总含税金额,0 AS 外购含税金额
FROM SINVOICED A
INNER JOIN SINVOICE B ON A.NUM_0=B.NUM_0
LEFT JOIN ITMMASTER C ON A.ITMREF_0=C.ITMREF_0
WHERE B.ACCDAT_0 BETWEEN '2010-01-01' AND '2010-10-31'
GROUP BY LEFT(CONVERT(CHAR,B.ACCDAT_0,112),6)
UNION ALL
--销售发票总数量、不含税金额、含税金额报表（外购）
SELECT LEFT(CONVERT(CHAR,B.ACCDAT_0,112),6) AS 年月,0 AS 销售总数量,SUM(A.QTY_0*B.SNS_0) AS 外购总数量,
0 AS 销售总不含税金额,ROUND(SUM(A.AMTNOTLIN_0*B.SNS_0*B.RATRPT_0),2) AS 外购不含税金额,
0 AS 销售总含税金额,ROUND(SUM(A.AMTATILIN_0*B.SNS_0*B.RATRPT_0),2) AS 外购含税金额
FROM SINVOICED A
INNER JOIN SINVOICE B ON A.NUM_0=B.NUM_0
LEFT JOIN ITMMASTER C ON A.ITMREF_0=C.ITMREF_0
WHERE B.ACCDAT_0 BETWEEN '2010-01-01' AND '2010-10-31' AND A.YCPLX_0=2
GROUP BY LEFT(CONVERT(CHAR,B.ACCDAT_0,112),6)
)A
GROUP BY A.年月
ORDER BY A.年月

 

这个供大家参考一下