?? 保價機查詢.sql
字號:
declare @fdate datetime--開始日期
declare @edate datetime-- 結(jié)束日期
declare @sjxh char(5)
declare @tempid char(12)
declare @temp char(20)
declare @count int
set @fdate='2000-01-01'
set @edate='2005-03-07'
set @sjxh='XH005'
declare @tmp table
(hrxx char(12),
m char(20)
)
delete tpztemp
insert into tpztemp(fhrxx,fmon,fdate,fcolor,flx,fpz,fname)
--找手機串號流水號沒有被銷售的記錄
select b.fid,b.fmon,b.fjhdate,b.fsjno,b.fhylx,b.fjbpz,b.fcolor from
(
--以串號找HRXX 以防有些手機銷售后,被退貨而重新入庫
select a.*,thrxx.fid,thrc.fid as fhrcid ,thylx.fname as fhylx,tcolor.fcolor as fcolor,thrxx.fmon,tjbpz.fname as fjbpz from (
--得到所有的手機串號和串號流水號一段時間內(nèi)的
select thrxx.fno as fsjno,thrc.fdate as fjhdate
from thrxx,thrc
where thrxx.fhrcid=thrc.fid and (thrc.fhylx='JH' or thrc.fhylx='CF')--and thrc.fdate>=@fdate and thrc.fdate<=@edate and thrc.fsjxh=@sjxh
--添加時間限制和型號限制
) a ,thrxx,thrc,thylx,tcolor,tjbpz
where thrxx.fno=a.fsjno and thrc.fid=thrxx.fhrcid and thylx.fid=thrc.fhylx and tcolor.fid=thrxx.fcolor and tjbpz.fid=thrc.fjbpz
)b
where (b.fid not in(select fcode from thxc) and b.fid not in (select fhrxx from tbs))or (b.fid in (select fhrxx from tbs where tbs.fid not in (select fbs from tbsfh)))
--or(b.fid in(select fcode from thxc) and b.fid in(select fhrxx from tbs where fid not in(select fbs from tbsfh)))
--select * from tpztemp
DECLARE tpz SCROLL CURSOR FOR
SELECT fhrxx FROM tpztemp
OPEN tpz
FETCH first FROM tpz
into @tempid
WHILE @@FETCH_STATUS = 0
BEGIN
set @count=(select count(*)from thrxx where thrxx.fid =@tempid and thrxx.fid not in (select fhrxx from tgtlh )and thrxx.fid not in(select fhrxx from tbs ))
if @count>0
begin
insert into @tmp(hrxx,m)values(@tempid,'本公司存貨')
end
set @count=(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid in(select fhrxx from tgtlh where tgtlh.fid not in(select fgl from tgtth)))
if @count>0
--update tpztemp set fyz=(select tgt.fname from tgt,tgtlh where tgtlh.fgt=tgt.fid and tgtlh.fhrxx=@tempid)where fhrxx=@tempid and exists(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid in(select fhrxx from tgtlh where tgtlh.fid not in(select fgl from tgtth)))
begin
set @temp='在代理商'+(select tgt.fna from tgt,tgtlh where tgtlh.fgt=tgt.fid and tgtlh.fhrxx=@tempid)
insert into @tmp(hrxx,m)values(@tempid,@temp)
end
set @count=(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid in(select fhrxx from tbs where tbs.fid not in(select fbs from tbsfh)))
if @count>0
--update tpztemp set fyz='在報損的路上' where fhrxx=@tempid and exists(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid in(select fhrxx from tbs where tbs.fid not in(select fbs from tbsfh)))
begin
insert into @tmp(hrxx,m)values(@tempid,'在報損的路上')
end
set @count=(select count(*) from thrxx where thrxx.fid=@tempid and thrxx.fid in(select fhrxx from tgtlh where fid in(select fgl from tgtth)) and thrxx.fid not in(select fhrxx from tbs))
if @count>0
begin
insert into @tmp(hrxx,m)values(@tempid,'代理退領(lǐng)在本公司')
end
FETCH NEXT FROM tpz
into @tempid
END
CLOSE tpz
DEALLOCATE tpz
select hrxx,count(*) from @tmp
group by hrxx
order by hrxx
--update tpztemp set fyz=(select m from @tmp where hrxx=tpztemp.fhrxx)
--select a.fhrxx as fid,a.fcolor as fno,a.fmon as fjhmon,a.flx as fhylx,a.fpz as fjbpz,a.fyz as fwhere,a.fdate as fjhdate ,a.fname as fcolor from tpztemp a
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -