?? (sp)生成某一時期，各個代理商的領貨，退貨銷售的總表.sql
字號:
/******************************************
功能：生成某一時期，所有的代理商的領貨，退貨銷售的總表
說明：@fgt參數利用模糊查找模式,
1。當@fGT的值為空串時，
就執行查找所有的代理商資料
2。當@fgt值不為空是，就直接查找相匹配的代理商資料
3.沒有用到匹配安符，
@flx類型,查找三種的類型，LH領貨，LX銷售，TH退貨,WX未退 ALL（所有類型）（存貨）
@sear 查詢的報表類型1.總表，２。分類型，３。詳細報表
@sjxh需要查找的手機型號
*********************************************/
declare @temp char(20)
declare @out char(8000)
declare @Fdate datetime
declare @fedate datetime
declare @fgt char(4)
declare @lx char(3)
declare @sear int
declare @sjxh char(5)
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='ALL'
SET @LX='ALL'
SET @SEAR=3
set @sjxh='ALL'
if @fgt='ALL'
set @fgt='%GT%'
if @sjxh='ALL'
SET @sjxh='%XH%'
--select * from tgt
--領貨
if (@LX='LH') OR (@LX='ALL')
BEGIN
set @temp='LH'--領貨（定領貨時間）
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'--(定領貨時間)
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
begin
select b.fna as fgtname,
(
case a.fna
when 'LH' then '領貨'
when 'TH' then '退貨'
when 'LX' then '銷售'
when 'WX' then '庫存'
end
) as flx,a.fcount
from tmp a,tgt b
where b.fid=a.fname
order by b.fid,a.fna
--update tmp set fname=(select fna from tgt where tgt.fid=tmp.fname)
--exec pCrossTable tmp,'fname','fcount','sum','fna',''
end
--分類型報表
if @sear=2 or @sear=3
BEGIN
if (@lx='LH') OR (@LX='ALL')
BEGIN
insert tmp2
--領貨（設定領貨的時間）
SELECT 'LH', thrc.fsjxh, tgtlh.fgt, COUNT(*)
AS fcount
FROM dbo.tgtlh INNER JOIN
dbo.thrxx ON dbo.tgtlh.fhrxx = dbo.thrxx.fid INNER JOIN
dbo.thrc ON dbo.thrxx.fhrcid = dbo.thrc.fid
WHERE
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
--銷售（設定銷售時間表）
insert tmp2
SELECT 'LX', thrc.fsjxh, tgtlh.fgt, COUNT(*)
AS fcount
FROM dbo.tgtlh INNER JOIN
dbo.thrxx ON dbo.tgtlh.fhrxx = dbo.thrxx.fid INNER JOIN
dbo.thrc ON dbo.thrxx.fhrcid = dbo.thrc.fid INNER JOIN
dbo.thxc ON dbo.thrxx.fid = dbo.thxc.fcode
WHERE (dbo.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
--退貨(設定退貨時間)
insert tmp2
SELECT 'TH', thrc.fsjxh, tgtlh.fgt, COUNT(*)
AS fcount
FROM dbo.tgtlh INNER JOIN
dbo.thrxx ON dbo.tgtlh.fhrxx = dbo.thrxx.fid INNER JOIN
dbo.thrc ON dbo.thrxx.fhrcid = dbo.thrc.fid INNER JOIN
dbo.tgtth ON dbo.tgtlh.fid = dbo.tgtth.fgl
WHERE
tgtth.fdate >= @fdate AND
tgtth.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='WX') OR (@LX='ALL')
BEGIN
--庫存(設定領貨時間)
insert tmp2
SELECT 'WX', thrc.fsjxh, tgtlh.fgt, COUNT(*)
AS fcount
FROM dbo.tgtlh INNER JOIN
dbo.thrxx ON dbo.tgtlh.fhrxx = dbo.thrxx.fid INNER JOIN
dbo.thrc ON dbo.thrxx.fhrcid = dbo.thrc.fid
WHERE (dbo.tgtlh.fhrxx NOT IN
(SELECT fcode
FROM thxc)) AND (dbo.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 '領貨'
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 dbo.tmp2 INNER JOIN
dbo.tmp ON dbo.tmp2.fid = dbo.tmp.fna AND
dbo.tmp2.fname = dbo.tmp.fname INNER JOIN
dbo.tgt ON dbo.tmp.fname = dbo.tgt.fid INNER JOIN
dbo.tsjxh ON dbo.tmp2.fna = dbo.tsjxh.fid INNER JOIN
dbo.tsjcs ON dbo.tsjxh.fsjcs = dbo.tsjcs.fid
order by tgt.fna,tmp.fna,TSJCS.FID,TSJXH.FID
--所有的詳細報表
IF @SEAR=3
BEGIN
if (@lx='LH') OR (@LX='ALL')
BEGIN
insert tmp3
--領貨（設定領貨的時間）
select 'LH', thrc.fsjxh,tgtlh.fgt,tgtlh.fdate,tcolor.fid,tjbpz.fid,thrxx.fno
FROM dbo.tgtlh INNER JOIN
dbo.thrxx ON dbo.tgtlh.fhrxx = dbo.thrxx.fid INNER JOIN
dbo.tcolor ON dbo.thrxx.fcolor = dbo.tcolor.fid INNER JOIN
dbo.tjbpz ON dbo.thrc.fjbpz = dbo.tjbpz.fid
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
--銷售（設定銷售時間表）
insert tmp3
select 'LX', thrc.fsjxh,tgtlh.fgt,thxc.fdate,tcolor.fid,tjbpz.fid ,thrxx.fno
FROM dbo.tgtlh INNER JOIN
dbo.thrxx ON dbo.tgtlh.fhrxx = dbo.thrxx.fid INNER JOIN
dbo.thrc ON dbo.thrxx.fhrcid = dbo.thrc.fid INNER JOIN
dbo.thxc ON dbo.thrxx.fid = dbo.thxc.fcode INNER JOIN
dbo.tcolor ON dbo.thrxx.fcolor = dbo.tcolor.fid INNER JOIN
dbo.tjbpz ON dbo.thrc.fjbpz = dbo.tjbpz.fid
WHERE (dbo.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))
order by tgtlh.fgt
END
if (@lx='TH') OR (@LX='ALL')
BEGIN
--退貨(設定退貨時間)
insert tmp3
select 'TH', thrc.fsjxh,tgtlh.fgt,tgtth.fdate,tcolor.fid,tjbpz.fid,thrxx.fno
FROM dbo.tgtlh INNER JOIN
dbo.thrxx ON dbo.tgtlh.fhrxx = dbo.thrxx.fid INNER JOIN
dbo.thrc ON dbo.thrxx.fhrcid = dbo.thrc.fid INNER JOIN
dbo.tgtth ON dbo.tgtlh.fid = dbo.tgtth.fgl INNER JOIN
dbo.tcolor ON dbo.thrxx.fcolor = dbo.tcolor.fid INNER JOIN
dbo.tjbpz ON dbo.thrc.fjbpz = dbo.tjbpz.fid and tgtth.fdate>=@fdate and tgtth.fdate<=@fedate and tgtlh.fgt like @fgt and thrc.fsjxh like LTRIM(RTRIM(@SJXH))
order by tgtlh.fgt
--select * from tmp3
END
if (@lx='WX') OR (@LX='ALL')
BEGIN
--庫存(設定領貨時間)
insert tmp3
select 'WX', thrc.fsjxh,tgtlh.fgt,tgtlh.fdate,tcolor.fid,tjbpz.fid ,thrxx.fno
FROM dbo.tgtlh INNER JOIN
dbo.thrxx ON dbo.tgtlh.fhrxx = dbo.thrxx.fid INNER JOIN
dbo.thrc ON dbo.thrxx.fhrcid = dbo.thrc.fid INNER JOIN
dbo.tcolor ON dbo.thrxx.fcolor = dbo.tcolor.fid INNER JOIN
dbo.tjbpz ON dbo.thrc.fjbpz = dbo.tjbpz.fid
WHERE (dbo.tgtlh.fhrxx NOT IN
(SELECT fcode
FROM thxc)) AND (dbo.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))
order by tgtlh.fgt
END
IF @SEAR=3
select tgt.fna,
(
case tmp.fna
when 'LH' then '領貨'
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 dbo.tsjcs INNER JOIN
dbo.tmp2 INNER JOIN
dbo.tmp ON dbo.tmp2.fid = dbo.tmp.fna AND
dbo.tmp2.fname = dbo.tmp.fname INNER JOIN
dbo.tgt ON LTRIM(RTRIM(dbo.tmp.fname)) = dbo.tgt.fid INNER JOIN
dbo.tsjxh ON dbo.tmp2.fna = dbo.tsjxh.fid INNER JOIN
dbo.tcolor INNER JOIN
dbo.tmp3 ON dbo.tcolor.fid = dbo.tmp3.fcolor INNER JOIN
dbo.tjbpz ON dbo.tmp3.fjbpz = dbo.tjbpz.fid ON dbo.tmp2.fid = dbo.tmp3.fid AND
dbo.tmp.fname = dbo.tmp3.fname AND dbo.tmp2.fna = dbo.tmp3.fna ON
dbo.tsjcs.fid = dbo.tsjxh.fsjcs
ORDER BY dbo.tgt.fna, dbo.tmp.fna, dbo.tsjcs.fid, dbo.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 + -