CREATE PROCEDURE queryjxchuizong @bianhao varchar(20) , @storeno int ,@supplyman varchar(30), @instorebegin varchar(20), @instoreend varchar(20) , @outstorebegin varchar(20) ,@outstoreend varchar(20)
AS
set nocount on
if(@storeno = "all" )
begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #DDD
from t_stock_detail where (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney from t_stock_detail where (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
AS
set nocount on
if(@storeno = "all" )
begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #DDD
from t_stock_detail where (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney from t_stock_detail where (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
AS
set nocount on
if(@storeno = 'all' )
begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #DDD
from t_stock_detail where (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney from t_stock_detail where (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
END
AS
set nocount on
if(@storeno = 'all' )
begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #DDD
from t_stock_detail where (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
INSERT #DDD
select fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney from t_stock_detail where (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
END
AS
set nocount on
if(@storeno = "all" )
begin
select fgoods_id,sum(fnum+salenum) as innum,sum(fnum*inprice+salenum*inprice) AS INMONEY,0 AS OUTNUM,0 AS OUTMONEY,0 AS mixnumber,0 AS mixmoney into #DDD
from t_stock_detail where (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
select
into #DDD --追加
fgoods_id,0 as innum,0 AS INMONEY,sum(salenum) AS OUTNUM,sum(salenum*saleprice) AS OUTMONEY ,0 AS mixnumber,0 AS mixmoney
from t_stock_detail
where (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
into #DDD
from t_stock_detail where (convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id
sum(fnum+salenum) as innum,
sum(fnum*inprice+salenum*inprice) AS INMONEY,
0 AS OUTNUM,
0 AS OUTMONEY,
0 AS mixnumber,
0 AS mixmoney
from t_stock_detail
where (convert(VARCHAR,indate,121) BETWEEN '2008-11-01' AND '2008-11-20')
group by fgoods_id
union all
select fgoods_id,
0 as innum,
0 AS INMONEY,
sum(salenum) AS OUTNUM,
sum(salenum*saleprice) AS OUTMONEY ,
0 AS mixnumber,
0 AS mixmoney into #DDD
from t_stock_detail where
(convert(VARCHAR,SALEDATE,121) BETWEEN '2008-11-01' AND '2008-11-20') group by fgoods_id