商品借货报表:
select a.DocNum as 单号,a.TaxDate as 单据日期,b.ItemCode as 物料编号,b.Dscription as 物料名称,
b.Quantity as 数量,c.WhsName as 仓库
from OIGE a join IGE1 b on a.DocEntry=b.DocEntry and b.AcctCode='410901'
join OWHS c on b.WhsCode=c.WhsCode商品归还报表:
select a.DocNum as 单号,a.TaxDate as 单据日期,b.ItemCode as 物料编号,b.Dscription as 物料名称,
b.Quantity as 数量,c.WhsName as 仓库
from OIGN a join IGN1 b on a.DocEntry=b.DocEntry and b.AcctCode='410901'
join OWHS c on b.WhsCode=c.WhsCode现在要查询借出了但还没归还的商品,查询结果不需要单号,日期,只要以下字段:物料编号,物料名称,总借出数量,总归还数量,总未归还数量,仓库。要按物料编号,仓库来汇总。
select a.DocNum as 单号,a.TaxDate as 单据日期,b.ItemCode as 物料编号,b.Dscription as 物料名称,
b.Quantity as 数量,c.WhsName as 仓库
from OIGE a join IGE1 b on a.DocEntry=b.DocEntry and b.AcctCode='410901'
join OWHS c on b.WhsCode=c.WhsCode商品归还报表:
select a.DocNum as 单号,a.TaxDate as 单据日期,b.ItemCode as 物料编号,b.Dscription as 物料名称,
b.Quantity as 数量,c.WhsName as 仓库
from OIGN a join IGN1 b on a.DocEntry=b.DocEntry and b.AcctCode='410901'
join OWHS c on b.WhsCode=c.WhsCode现在要查询借出了但还没归还的商品,查询结果不需要单号,日期,只要以下字段:物料编号,物料名称,总借出数量,总归还数量,总未归还数量,仓库。要按物料编号,仓库来汇总。
select a.物料编号,a.物料名称,sum(a.数量) as 总借出数量,sum(b.数量) as 总归还数量,
(sum(a.数量)-sum(b.数量)) as 总未归还数量,a.仓库
(select a.DocNum as 单号,a.TaxDate as 单据日期,b.ItemCode as 物料编号,b.Dscription as 物料名称,
b.Quantity as 数量,c.WhsName as 仓库
from OIGE a join IGE1 b on a.DocEntry=b.DocEntry and b.AcctCode='410901'
join OWHS c on b.WhsCode=c.WhsCode
) a
join
(select a.DocNum as 单号,a.TaxDate as 单据日期,b.ItemCode as 物料编号,b.Dscription as 物料名称,
b.Quantity as 数量,c.WhsName as 仓库
from OIGN a join IGN1 b on a.DocEntry=b.DocEntry and b.AcctCode='410901'
join OWHS c on b.WhsCode=c.WhsCode
)b on a.物料编号=b.物料编号 and a.仓库=b.仓库
group by a.物料编号,a.物料名称,a.仓库
另,你給的分也太少了,別人是不會回答你的問題的.
你試一下,我簡化了一點select a.物料编号,a.物料名称,a.仓库 ,sum(a.数量) as 总借出数量,sum(b.数量) as 总归还数量,
(sum(a.数量)-sum(b.数量)) as 总未归还数量
(select b.ItemCode as 物料编号,b.Dscription as 物料名称, b.Quantity as 数量,c.WhsName as 仓库
from IGE1 b join OWHS c on b.WhsCode=c.WhsCode
where b.AcctCode='410901'
) a
join
(select b.ItemCode as 物料编号,b.Dscription as 物料名称, b.Quantity as 数量,c.WhsName as 仓库
from IGN1 b join OWHS c on b.WhsCode=c.WhsCode
where b.AcctCode='410901'
)b on a.物料编号=b.物料编号 and a.仓库=b.仓库
group by a.物料编号,a.物料名称,a.仓库