27,579
社区成员
发帖
与我相关
我的任务
分享
select c.MaterielName
,sum(case c.Action when '入库' then b.number else -b.number end)
,b.price
,sum(case c.Action when '入库' then b.number*b.price else -b.number*b.price end) as totalAmount
from Materiel_Process a join Materiel_Process_Detail b on a.ProcessID = b.ProcessID
join Materiel_Info c on b.MaterielID = c.MaterielID
where a.MakeTime < '2011-07-01'
group by c.MaterielName
,b.price
--Action=1表示入库,-1表示出库
select b.MaterielID,c.MaterielName,
sum(b.Number*a.Action) as 数量,
sum(b.Number*b.Price*a.Action) as 金额
from Materiel_Process a,Materiel_Process_Detail b,Materiel_Info c
where a.ProcessID=b.ProcessID and b.MaterielID=c.MaterielID
group by b.MaterielID,b.Price,c.MaterielName
select c.MaterielName,b.number,b.price,b.number*b.price as totalAmount
from Materiel_Process a join Materiel_Process_Detail b on a.ProcessID = b.ProcessID
join Materiel_Info c on b.MaterielID = c.MaterielID
where convert(varchar(7),a.MakeTime,120) = '2011-07'
select c.MaterielName,b.number,b.price,b.number*b.price as totalAmount
from Materiel_Process a join Materiel_Process_Detail b on a.ProcessID = b.ProcessID
join Materiel_Info c on b.MaterielID = c.MaterielID