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