Select di.BillNumberID,di.BillDate,di.BillCode,bs.Btypeid1,bs.PtypeID from
-- 已销售序列号即已销售商品
(select B.Serial,B.PtypeID,s.BillNumberID,B.btypeid1,S.Billtype from
(select * from gp_inoutptypeserial where OutKtyeID<>'') as S -- S 出库的商品
inner join
(select * from gp_inoutptypeserial where OutktyeID=''/*billtype=34*/) as B -- B 包括了所有商品(也不包括销退的序列号)
on S.Serial=B.Serial
where B.ifcheck='t' and S.billtype='11' and B.billtype<>'45' and B.btypeID1=@szBtypeID and B.Ptypeid=@szPtypeID) as BS
Inner Join
----销售开单中商品的数量以及单据信息
(select i.BillNumberID,i.BillDate,i.BillCode from
(select BillnumberID from salebill where Ptypeid=@szPtypeID group by BillnumberID) as d
inner join
(select BillNumberID,BillDate,BillCode from billindex where BillDate between @szStartDate and @szEndDate and ifcheck='t') as i
on d.BillNumberID=i.BillNumberID) as DI
On di.BillNumberID=bs.BillNumberID where bs.Serial not in
(select g.serial from gp_inoutptypeserial as g --not in 过滤已销售出但又销退的序列号商品
inner join billindex as b on g.billnumberid=b.billnumberid
where g.ptypeid=@szPtypeID and g.billtype='45' and g.IfCheck='t' and b.ifCheck='t')
Order by di.BillNumberID