?? 生成某一時期，各個代理商的領(lǐng)貨，退貨銷售的總表(改寫).sql
字號:
/******************************************
功能：生成某一時期，所有的代理商的領(lǐng)貨，退貨銷售的總表
說明：@fgt參數(shù)利用模糊查找模式,
1。當(dāng)@fGT的值為空串時，
就執(zhí)行查找所有的代理商資料
2。當(dāng)@fgt值不為空是，就直接查找相匹配的代理商資料
3.沒有用到匹配安符，
@flx類型,查找三種的類型，LH領(lǐng)貨，LX銷售，TH退貨,WX未退 ALL（所有類型）（存貨）
@sear 查詢的報表類型1.總表，２。分類型，３。詳細(xì)報表
@act 1,查詢某一段時期的代理商的領(lǐng)貨退貨，銷售，庫存2查詢某一時期內(nèi)代理商所有的領(lǐng)貨的去向
*********************************************/
declare @temp char(20)
declare @out char(8000)
declare @Fdate datetime
declare @fedate datetime
declare @fgt char(4)
declare @lx char(3)
declare @sjxh char(5)
declare @sear int
declare @act int
declare @tmp table
(
fna char(2),
fgt char(4),
fhrxx char(12),
fjbpz char(4),
fdate datetime,
fsjxh char(5),
fcolor char(4),
fno char(16)
)
declare @tmp2 table
(
fgl char(12)
)
declare @tmp3 table
(
fid char(2),
fna char(20),
fname char(20),
fdate datetime,
fcolor char(20),
fjbpz char(20),
fno char(16)
)
set @fdate='2001-01-01'
set @fedate='2005-01-01'
set @fgt=''
SET @LX='ALL'
SET @SEAR=2
set @sjxh='ALL'
set @act=2
set @lx='ALL'
if @fgt=''
set @fgt='%GT%'
if @sjxh='ALL'
SET @sjxh='%XH%'
if @lx='ALL'
set @lx='%%'
--得到數(shù)據(jù)源
if @act=1
begin
insert @tmp
select 'LH',tgtlh.fgt,tgtlh.fhrxx,thrc.fjbpz,thrc.fdate,thrc.fsjxh,thrxx.fcolor,thrxx.fno
from tgtlh,thrxx,thrc
where tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and tgtlh.fgt like @fgt
and thrxx.fid=tgtlh.fhrxx and thrc.fid=thrxx.fhrcid
insert @tmp
select 'TH',tgtlh.fgt,tgtlh.fhrxx,thrc.fjbpz,thrc.fdate,thrc.fsjxh,thrxx.fcolor,thrxx.fno
from tgtth,tgtlh,thrxx,thrc
where tgtth.fgl=tgtlh.fid and tgtth.fdate>=@fdate and tgtth.fdate<=@fedate and tgtlh.fgt like @fgt
and thrxx.fid=tgtlh.fhrxx and thrc.fid=thrxx.fhrcid
insert @tmp
select 'LX',tgtlh.fgt,tgtlh.fhrxx,thrc.fjbpz,thrc.fdate,thrc.fsjxh,thrxx.fcolor,thrxx.fno
from thxc,tgtlh,thrxx,thrc
where tgtlh.fhrxx=thxc.fcode and tgtlh.fid not in(select fgl from tgtth ) and thxc.fdate>=@fdate and thxc.fdate<=@fedate and tgtlh.fgt like @fgt
and thrxx.fid=tgtlh.fhrxx and thrc.fid=thrxx.fhrcid
insert @tmp
select 'WX',tgtlh.fgt,tgtlh.fhrxx,thrc.fjbpz,thrc.fdate,thrc.fsjxh,thrxx.fcolor,thrxx.fno
from tgtlh,thrxx,thrc
where tgtlh.fhrxx not in(select fcode from thxc) and tgtlh.fid not in(select fgl from tgtth ) and tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and tgtlh.fgt like @fgt
and thrxx.fid=tgtlh.fhrxx and thrc.fid=thrxx.fhrcid
end
if @act=2
begin
insert @tmp2
select tgtlh.fid
from tgtlh
where tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and tgtlh.fgt like @fgt
insert @tmp
select 'LH',tgtlh.fgt,tgtlh.fhrxx,thrc.fjbpz,thrc.fdate,thrc.fsjxh,thrxx.fcolor,thrxx.fno
from tgtlh,thrxx,thrc,@tmp2 b
where thrxx.fid=tgtlh.fhrxx and thrc.fid=thrxx.fhrcid and b.fgl=tgtlh.fid
insert @tmp
select 'TH',tgtlh.fgt,tgtlh.fhrxx,thrc.fjbpz,thrc.fdate,thrc.fsjxh,thrxx.fcolor,thrxx.fno
from tgtth,tgtlh,thrxx,thrc,@tmp2 b
where tgtth.fgl=tgtlh.fid
and thrxx.fid=tgtlh.fhrxx and thrc.fid=thrxx.fhrcid and b.fgl=tgtlh.fid
insert @tmp
select 'LX',tgtlh.fgt,tgtlh.fhrxx,thrc.fjbpz,thrc.fdate,thrc.fsjxh,thrxx.fcolor,thrxx.fno
from thxc,tgtlh,thrxx,thrc,@tmp2 b
where tgtlh.fhrxx=thxc.fcode and tgtlh.fid not in(select fgl from tgtth )
and thrxx.fid=tgtlh.fhrxx and thrc.fid=thrxx.fhrcid and b.fgl=tgtlh.fid
insert @tmp
select 'WX',tgtlh.fgt,tgtlh.fhrxx,thrc.fjbpz,thrc.fdate,thrc.fsjxh,thrxx.fcolor,thrxx.fno
from tgtlh,thrxx,thrc,@tmp2 b
where tgtlh.fhrxx not in(select fcode from thxc) and tgtlh.fid not in(select fgl from tgtth )
and thrxx.fid=tgtlh.fhrxx and thrc.fid=thrxx.fhrcid and b.fgl=tgtlh.fid
end
if @sear=1
select b.fna as fgtname,
(
case a.fna
when 'LH' then '領(lǐng)貨'
when 'TH' then '退貨'
when 'LX' then '銷售'
when 'WX' then '庫存'
end
) as flx,a.fcount,a.fna as flx
from ( select top 10000 fna,fgt,count(*) as fcount from @tmp
group by fna,fgt
order by fna,fgt) a,tgt b
where b.fid=ltrim(rtrim(a.fgt)) and a.fna like ltrim(rtrim(@lx))
order by b.fid,a.fna
IF @SEAR=2
select tgt.fna,
(
case a.fna
when 'LH' then '領(lǐng)貨'
when 'TH' then '退貨'
when 'LX' then '銷售'
when 'WX' then '庫存'
end
) as flx,a.fna as flxid,a.fallcount as ffcount,TSJCS.FNA AS FSJCS,TSJXH.FMODEL AS FSJXH,a.fsjxhcount as fsjxhcount
from (
select b.fna,b.fgt,b.fcount as fallcount,a.fsjxh,a.fsjxhcount
from(
select top 1000 fna,fgt,fsjxh,count(*) as fsjxhcount from @tmp
group by fna,fgt,fsjxh
)a,(
select top 10000 fna,fgt,count(*) as fcount
from @tmp
group by fna,fgt
order by fna,fgt
)b
where a.fna=b.fna and a.fgt=b.fgt)a,tgt,TSJXH,TSJCS
where tgt.fid=ltrim(rtrim(a.fgt)) and TSJXH.FID=a.fsjxh AND TSJCS.FID=TSJXH.FSJCS and a.fna like ltrim(rtrim(@lx))
order by tgt.fna,a.fna,TSJCS.FID,TSJXH.FID
if @sear=3
select tgt.fna,
(
case a.fna
when 'LH' then '領(lǐng)貨'
when 'TH' then '退貨'
when 'LX' then '銷售'
when 'WX' then '庫存'
end
) as flx,a.fallcount as ffcount,TSJCS.FNA AS FSJCS,TSJXH.FMODEL AS FSJXH,a.fsjxhcount as fsjxhcount,tcolor.fcolor as fcolor,tjbpz.fname as fjbpz,a.fno,a.fdate
from (
select top 1000 a.fna,a.fgt,a.fallcount,a.fsjxh,a.fsjxhcount,b.fno,b.fjbpz,b.fcolor,b.fdate,b.fhrxx
from (
select top 1000 b.fna,b.fgt,b.fcount as fallcount,a.fsjxh,a.fsjxhcount
from(
select fna,fgt,fsjxh,count(*) as fsjxhcount from @tmp
group by fna,fgt,fsjxh
)a,(
select top 10000 fna,fgt,count(*) as fcount
from @tmp
group by fna,fgt
order by fna,fgt
)b
where a.fna=b.fna and a.fgt=b.fgt
)a,@tmp b
where a.fna=b.fna and a.fgt=b.fgt and a.fsjxh=b.fsjxh
order by a.fgt,a.fna,a.fsjxh) a,tgt,tcolor,tjbpz,TSJCS,TSJXH
where tgt.fid=ltrim(rtrim(a.fgt)) and TSJXH.fid=a.fsjxh and tcolor.fid= a.fcolor and tjbpz.fid=a.fjbpz and TSJCS.FID=TSJXH.FSJCS and a.fna like ltrim(rtrim(@lx))
order by tgt.fna,a.fna,TSJCS.FID,TSJXH.FID
/*
--select * from tgt
--領(lǐng)貨
if (@LX='LH') OR (@LX='ALL')
BEGIN
set @temp='LH'--領(lǐng)貨（定領(lǐng)貨時間）
insert tmp
select @temp,fgt,count(*) from tgtlh
where tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and tgtlh.fgt like @fgt
group by fgt
END
if (@LX='TH') OR (@LX='ALL')
BEGIN
set @temp='TH'--退貨(定退貨時間)
insert tmp
select @temp,tgtlh.fgt,count(*) from tgtth,tgtlh
where tgtth.fgl=tgtlh.fid and tgtth.fdate>=@fdate and tgtth.fdate<=@fedate and tgtlh.fgt like @fgt
group by tgtlh.fgt
END
if (@LX='LX') OR (@LX='ALL')
BEGIN
set @temp='LX'--（定銷售時間）
insert tmp
select @temp,tgtlh.fgt,count(*) from thxc,tgtlh
where tgtlh.fhrxx=thxc.fcode and tgtlh.fid not in(select fgl from tgtth ) and thxc.fdate>=@fdate and thxc.fdate<=@fedate and tgtlh.fgt like @fgt
group by tgtlh.fgt
END
if (@LX='WX') OR (@LX='ALL')
BEGIN
set @temp='WX'--(定領(lǐng)貨時間)
insert tmp
select @temp,tgtlh.fgt,count(*) from tgtlh
where tgtlh.fhrxx not in(select fcode from thxc) and tgtlh.fid not in(select fgl from tgtth ) and tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and tgtlh.fgt like @fgt
group by tgtlh.fgt
END
IF @SEAR=1
select b.fna as fgtname,
(
case a.fna
when 'LH' then '領(lǐng)貨'
when 'TH' then '退貨'
when 'LX' then '銷售'
when 'WX' then '庫存'
end
) as flx,a.fcount
from tmp a,tgt b
where b.fid=ltrim(rtrim(a.fname))
order by b.fid,a.fna
--exec pCrossTable tmp,'fname','fcount','sum','fna',''
--分類型報表
if @sear=2 or @sear=3
BEGIN
if (@lx='LH') OR (@LX='ALL')
BEGIN
insert tmp2
--領(lǐng)貨（設(shè)定領(lǐng)貨的時間）
select 'LH', thrc.fsjxh,tgtlh.fgt,count(*) as fcount from tgtlh,thrxx,thrc
where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and tgtlh.fgt like @fgt AND thrc.fsjxh like LTRIM(RTRIM(@SJXH))
group by tgtlh.fgt,thrc.fsjxh
order by tgtlh.fgt
END
--exec pCrossTable tmp,'fname','fcount','sum','fna',''
--select * from tmp2
if (@lx='LX') OR (@LX='ALL')
BEGIN
--銷售（設(shè)定銷售時間表）
insert tmp2
select 'LX', thrc.fsjxh,tgtlh.fgt,count(*) as fcount from thrc,thrxx,thxc,tgtlh
where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and thxc.fcode=thrxx.fid and tgtlh.fid not in(select fgl from tgtth) and thxc.fdate>=@fdate and thxc.fdate<=@fedate and tgtlh.fgt like @fgt AND thrc.fsjxh like LTRIM(RTRIM(@SJXH))
group by tgtlh.fgt,thrc.fsjxh
order by tgtlh.fgt
END
if (@lx='TH') OR (@LX='ALL')
BEGIN
--退貨(設(shè)定退貨時間)
insert tmp2
select 'TH', thrc.fsjxh,tgtlh.fgt,count(*) as fcount from tgtlh,thrxx,thrc,tgtth
where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and tgtlh.fid =tgtth.fgl and tgtth.fdate>=@fdate and tgtth.fdate<=@fedate and tgtlh.fgt like @fgt
group by tgtlh.fgt,thrc.fsjxh
order by tgtlh.fgt
END
if (@lx='WX') OR (@LX='ALL')
BEGIN
--庫存(設(shè)定領(lǐng)貨時間)
insert tmp2
select 'WX', thrc.fsjxh,tgtlh.fgt,count(*) as fcount from thrc,thrxx,tgtlh
where tgtlh.fhrxx not in(select fcode from thxc) and tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and tgtlh.fid not in(select fgl from tgtth) and tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and tgtlh.fgt like @fgt AND thrc.fsjxh like LTRIM(RTRIM(@SJXH))
group by tgtlh.fgt,thrc.fsjxh
order by tgtlh.fgt
END
--select * from tmp2
IF @SEAR=2
select tgt.fna,
(
case tmp.fna
when 'LH' then '領(lǐng)貨'
when 'TH' then '退貨'
when 'LX' then '銷售'
when 'WX' then '庫存'
end
) as flx,tmp.fcount as ffcount,TSJCS.FNA AS FSJCS,TSJXH.FMODEL AS FSJXH,tmp2.fcount as fsjxhcount
from tmp2,tmp,tgt,TSJXH,TSJCS
where tmp2.fid=tmp.fna and tmp.fname=tmp2.fname and tgt.fid=ltrim(rtrim(tmp.fname)) and TSJXH.FID=tmp2.fna AND TSJCS.FID=TSJXH.FSJCS
order by tgt.fna,tmp.fna,TSJCS.FID,TSJXH.FID
--所有的詳細(xì)報表
IF @SEAR=3
BEGIN
if (@lx='LH') OR (@LX='ALL')
BEGIN
insert tmp3
--領(lǐng)貨（設(shè)定領(lǐng)貨的時間）
select 'LH', thrc.fsjxh,tgtlh.fgt,tgtlh.fdate,tcolor.fid,tjbpz.fid,thrxx.fno from tgtlh,thrxx,thrc,tcolor,tjbpz
where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and thrxx.fcolor=tcolor.fid and tjbpz.fid=thrc.fjbpz and tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and tgtlh.fgt like @fgt AND thrc.fsjxh like LTRIM(RTRIM(@SJXH))
order by tgtlh.fgt
END
if (@lx='LX') OR (@LX='ALL')
BEGIN
--銷售（設(shè)定銷售時間表）
insert tmp3
select 'LX', thrc.fsjxh,tgtlh.fgt,thxc.fdate,tcolor.fid,tjbpz.fid ,thrxx.fno from thrc,thrxx,thxc,tgtlh,tcolor,tjbpz
where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and thxc.fcode=thrxx.fid and tgtlh.fid not in(select fgl from tgtth) and thrxx.fcolor=tcolor.fid and tjbpz.fid=thrc.fjbpz and thxc.fdate>=@fdate and thxc.fdate<=@fedate and tgtlh.fgt like @fgt AND thrc.fsjxh like LTRIM(RTRIM(@SJXH))
order by tgtlh.fgt
END
if (@lx='TH') OR (@LX='ALL')
BEGIN
--退貨(設(shè)定退貨時間)
insert tmp3
select 'TH', thrc.fsjxh,tgtlh.fgt,tgtth.fdate,tcolor.fid,tjbpz.fid,thrxx.fno from tgtlh,thrxx,thrc,tgtth,tcolor,tjbpz
where tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and tgtlh.fid =tgtth.fgl and thrxx.fcolor=tcolor.fid and tjbpz.fid=thrc.fjbpz and tgtth.fdate>=@fdate and tgtth.fdate<=@fedate and tgtlh.fgt like @fgt
order by tgtlh.fgt
--select * from tmp3
END
if (@lx='WX') OR (@LX='ALL')
BEGIN
--庫存(設(shè)定領(lǐng)貨時間)
insert tmp3
select 'WX', thrc.fsjxh,tgtlh.fgt,tgtlh.fdate,tcolor.fid,tjbpz.fid ,thrxx.fno from thrc,thrxx,tgtlh,tcolor,tjbpz
where tgtlh.fhrxx not in(select fcode from thxc) and tgtlh.fhrxx=thrxx.fid and thrc.fid=thrxx.fhrcid and tgtlh.fid not in(select fgl from tgtth) and thrxx.fcolor=tcolor.fid and tjbpz.fid=thrc.fjbpz and tgtlh.fdate>=@fdate and tgtlh.fdate<=@fedate and tgtlh.fgt like @fgt AND thrc.fsjxh like LTRIM(RTRIM(@SJXH))
order by tgtlh.fgt
END
IF @SEAR=3
select tgt.fna,
(
case tmp.fna
when 'LH' then '領(lǐng)貨'
when 'TH' then '退貨'
when 'LX' then '銷售'
when 'WX' then '庫存'
end
) as flx,tmp.fcount as ffcount,TSJCS.FNA AS FSJCS,TSJXH.FMODEL AS FSJXH,tmp2.fcount as fsjxhcount,tcolor.fcolor as fcolor,tjbpz.fname as fjbpz,tmp3.fno,tmp3.fdate
from tmp2,tmp,tgt,tcolor,tjbpz,tmp3,TSJCS,TSJXH
where tmp2.fid=tmp.fna and tmp.fname=tmp2.fname and tgt.fid=ltrim(rtrim(tmp.fname)) and TSJXH.fid=tmp2.fna and tcolor.fid= tmp3.fcolor and tjbpz.fid=tmp3.fjbpz and tmp3.fid=tmp2.fid and tmp3.fname=tmp.fname and tmp3.fna=tmp2.fna AND TSJCS.FID=TSJXH.FSJCS
order by tgt.fna,tmp.fna,TSJCS.FID,TSJXH.FID
END
END
if exists (select * from sysobjects where name='tmp')
drop table tmp
if exists (select * from sysobjects where name='tmp2')
drop table tmp2
*/
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -