网友问：如何按月进行统计公司采购进货、销售出库信息？
答：采购进货、采购退货；销售出库、销售退货按月统计；其实对于生产方面也就很容易扩展了。
select docYear, docMon, cast(sum(iQty) as numeric(22,2)) iQty, cast(sum(iAmt) as numeric(22,2)) iAmt, cast(sum(oQty) as numeric(22,2)) oQty, cast(sum(oAmt) as numeric(22,2)) oAmt
from
(
select datepart(yy,a.docdate) docYear, datepart(mm,a.docdate) docMon, sum(b.quantity) iQty, sum(b.quantity*price) iAmt, 0 oQty, 0.00 oAmt
from OPDN a inner join PDN1 b on a.docentry=b.docentry
group by datepart(yy,a.docdate), datepart(mm,a.docdate)
union all
select datepart(yy,a.docdate) docYear, datepart(mm,a.docdate) docMon, -sum(b.quantity) iQty, -sum(b.quantity*price) iAmt, 0 oQty, 0.00 oAmt
from ORPD a inner join RPD1 b on a.docentry=b.docentry
group by datepart(yy,a.docdate), datepart(mm,a.docdate)
union all
select datepart(yy,a.docdate), datepart(mm,a.docdate), 0, 0.00, sum(b.quantity), sum(b.quantity*price)
from ODLN a inner join DLN1 b on a.docentry=b.docentry
group by datepart(yy,a.docdate), datepart(mm,a.docdate)
union all
select datepart(yy,a.docdate), datepart(mm,a.docdate), 0, 0.00, -sum(b.quantity), -sum(b.quantity*price)
from ORDN a inner join RDN1 b on a.docentry=b.docentry
group by datepart(yy,a.docdate), datepart(mm,a.docdate)) tmp
答：采购进货、采购退货；销售出库、销售退货按月统计；其实对于生产方面也就很容易扩展了。
select docYear, docMon, cast(sum(iQty) as numeric(22,2)) iQty, cast(sum(iAmt) as numeric(22,2)) iAmt, cast(sum(oQty) as numeric(22,2)) oQty, cast(sum(oAmt) as numeric(22,2)) oAmt
from
(
select datepart(yy,a.docdate) docYear, datepart(mm,a.docdate) docMon, sum(b.quantity) iQty, sum(b.quantity*price) iAmt, 0 oQty, 0.00 oAmt
from OPDN a inner join PDN1 b on a.docentry=b.docentry
group by datepart(yy,a.docdate), datepart(mm,a.docdate)
union all
select datepart(yy,a.docdate) docYear, datepart(mm,a.docdate) docMon, -sum(b.quantity) iQty, -sum(b.quantity*price) iAmt, 0 oQty, 0.00 oAmt
from ORPD a inner join RPD1 b on a.docentry=b.docentry
group by datepart(yy,a.docdate), datepart(mm,a.docdate)
union all
select datepart(yy,a.docdate), datepart(mm,a.docdate), 0, 0.00, sum(b.quantity), sum(b.quantity*price)
from ODLN a inner join DLN1 b on a.docentry=b.docentry
group by datepart(yy,a.docdate), datepart(mm,a.docdate)
union all
select datepart(yy,a.docdate), datepart(mm,a.docdate), 0, 0.00, -sum(b.quantity), -sum(b.quantity*price)
from ORDN a inner join RDN1 b on a.docentry=b.docentry
group by datepart(yy,a.docdate), datepart(mm,a.docdate)) tmp
group by docYear, docMon
本文转自foresun 51CTO博客，原文链接：http://blog.51cto.com/foresun/42112，如需转载请自行联系原作者
