?? 生成某一時期，各個代理商的領(lǐng)貨，退貨銷售的總表.sql
字號:
/******************************************
功能：生成某一時期，所有的代理商的領(lǐng)貨，退貨銷售的總表
說明：@fgt參數(shù)利用模糊查找模式,
1。當@fGT的值為空串時，
就執(zhí)行查找所有的代理商資料
2。當@fgt值不為空是，就直接查找相匹配的代理商資料
3.沒有用到匹配安符，
@flx類型,查找三種的類型，LH領(lǐng)貨，LX銷售，TH退貨,WX未退 ALL（所有類型）（存貨）
@sear 查詢的報表類型1.總表，２。分類型，３。詳細報表
*********************************************/
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 @cross table
(
fname char(20),
fval char(20)
)
if exists (select * from sysobjects where name='tmp')
drop table tmp
if exists (select * from sysobjects where name='tmp2')
drop table tmp2
if exists (select * from sysobjects where name='tmp3')
drop table tmp3
create table tmp
(
fna char(20),
fname char(20),
fcount int
)
create table tmp2
(
fid char(2),
fna char(20),
fname char(20),
fcount int
)
create table tmp3
(
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=3
set @sjxh='ALL'
if @fgt=''
set @fgt='%GT%'
if @sjxh='ALL'
SET @sjxh='%XH%'
--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
--所有的詳細報表
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
*/
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -