📄 (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.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
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 + -