select a.ProductID,a.Name as 名称,a.Size as 规格型号,a.Unit as 计量单位,
(a.Amount+isnull(t2.Amount,0)-isnull(t1.Amount,0)) as 上月底库存数量,
(a.Money+isnull(t2.Money,0)-isnull(t1.Money,0)) as 上月底库存金额,
isnull(t1.Amount,0) as 入库物资数量,
isnull(t1.Money,0) as 入库物资金额,
isnull(t2.Amount,0) as 出库物资数量,
isnull(t2.Money,0) as 出库物资金额,
a.Amount as 本月底库存数量,a.Money as 本月底库存金额
from tblProduct as a left join
( select ProductID,sum(isnull(Amount,0)) as Amount,sum(isnull(Money,0)) as Money from tblIn
where InDate between ? and ?
group by ProductID) t1 on a.ProductID=t1.ProductID left Join
( select ProductID,sum(isnull(Amount,0)) as Amount,sum(isnull(Money,0)) as Money from tblOut
where OutDate between ? and ? group by ProductID) t2 on a.ProductID=t2.ProductID
group by a.ProductID, a.Name, a.Size, a.Unit, a.Amount, a.Money
(a.Amount+isnull(t2.Amount,0)-isnull(t1.Amount,0)) as 上月底库存数量,
(a.Money+isnull(t2.Money,0)-isnull(t1.Money,0)) as 上月底库存金额,
isnull(t1.Amount,0) as 入库物资数量,
isnull(t1.Money,0) as 入库物资金额,
isnull(t2.Amount,0) as 出库物资数量,
isnull(t2.Money,0) as 出库物资金额,
a.Amount as 本月底库存数量,a.Money as 本月底库存金额
from tblProduct as a left join
( select ProductID,sum(isnull(Amount,0)) as Amount,sum(isnull(Money,0)) as Money from tblIn
where InDate between ? and ?
group by ProductID) t1 on a.ProductID=t1.ProductID left Join
( select ProductID,sum(isnull(Amount,0)) as Amount,sum(isnull(Money,0)) as Money from tblOut
where OutDate between ? and ? group by ProductID) t2 on a.ProductID=t2.ProductID
group by a.ProductID, a.Name, a.Size, a.Unit, a.Amount, a.Money
(a.Money+sum(isnull(t2.Money,0))-sum(isnull(t1.Money,0))) as 上月底库存金额,sum(isnull(t1.Amount,0)) as 入库物资数量,sum(isnull(t1.Money,0)) as 入库物资金额,sum(isnull(t2.Amount,0)) as 出库物资数量,sum(isnull(t2.Money,0)) as 出库物资金额,
a.Amount as 本月底库存数量,a.Money as 本月底库存金额
from tblProduct as a
left join
(select ProductID,sum(isnull(Amount,0)) as Amount,sum(isnull(Money,0)) as Money from tblIn
where InDate between ? and ?
group by ProductID) t1
on a.ProductID=t1.ProductID
left join
(select ProductID,sum(isnull(Amount,0)) as Amount,sum(isnull(Money,0)) as Money from tblOut
where OutDate between ? and ?
group by ProductID) t2
on a.ProductID=t2.ProductID
group by a.ProductID,a.Name,a.Size,a.Unit,a.Amount,a.Money
sum(isnull(c.Amount,0))-sum(isnull(c.Amount,0)) as 上月底库存数量,
(a.Money+sum(isnull(c.Money,0))-sum(isnull(b.Money,0))) as 上月底库存金,
sum(isnull(b.Amount,0)) as 入库物资数量,sum(isnull(b.Money,0)) as 入库物资金额,sum(isnull(c.Amount,0)) as 出库物资数量,sum(isnull(c.Money,0)) as 出库物资金额,
a.Amount as 本月底库存数量,a.Money as 本月底库存金额
from tblProduct as a
left join tblIn as b on a.productid=b.productid
left join tblOut as c on a.productid=c.productid
where
c.OutDate between ? and ?
and
b.between ? and ?
group by a.ProductID,a.Name,a.Size,a.Unit,a.Amount,a.Money
,a.Amount+isnull(b.Amount,0) as 上月底库存数量
,a.[Money]+isnull(b.[Money],0) as 上月底库存金额
,isnull(b.in_Amount,0) as 入库物资数量
,isnull(b.in_Money,0) as 入库物资金额
,isnull(b.out_Amount,0) as 出库物资数量
,isnull(b.out_Money,0) as 出库物资金额
,a.Amount as 本月底库存数量
,a.[Money] as 本月底库存金额
from tblProduct as a
left join(
select ProductID
,in_Amount=sum(in_Amount),in_Money=sum(in_Money)
,out_Amount=sum(out_Amount),out_Money=sum(out_Money)
,Amount=sum(Amount),[Money]=sum([Money])
from(
select ProductID
,in_Amount=Amount,in_Money=[Money]
,out_Amount=0,out_Money=0
,Amount,[Money]
from tblIn
where InDate between ? and ?
union all
select ProductID
,in_Amount=0,in_Money=0
,out_Amount=Amount,out_Money=[Money]
,-Amount,-[Money]
from tblOut
where OutDate between ? and ?
) a group by ProductID
)b on a.ProductID=b.ProductID