/**********************************************
查詢歷史銷售記錄
************************************************/
declare @odate char(6) 
declare @date  char(6)--日期段參數,就是要統計的日期段
declare @year  datetime

Declare @TMps2 Table


(
fid  char(5),
fsjxh char(50),
fjh  int,
fkt int,
fsg int,
fcf int,
fwxtk int,
falsales int,
fkc int
)
set @year='2001-01-07'
set @date=Convert(char(6),@year,112)
set @odate=cast(cast(@date as int)-1 as char(6))
if cast(substring(@date,5,2)as int)=1
set @odate=cast(cast(substring(@date,1,4)as int)-1 as char(4))+'12'
--得到上一個月的號
select @date,@odate
insert into @TMps2 
select a.*,isnull(b.fkc,0) as fkc
from(  
	select a.*,isnull(b.fjh,0) as fjh,isnull(b.fkt,0)as fkt,isnull(b.fsg,0)as fsg,isnull(b.fcf,0)as fcf,isnull(b.ftc,0)as ftc,isnull(b.fxc,0)as fxc from vAllsjxh a left outer join
(
select c.* from tsjpd c where substring(c.fid,3,6)=@date
)b on a.fid=b.fsjxh

)a left outer join 
(select fsjxh as fid,(fjh+fkt+fsg+fcf-ftc-fxc)as fkc from tsjpd where substring(fid,3,6)=@odate) b
on b.fid=a.fid

delete from @TMps2 where fjh=0 and fkt=0 and fsg=0 and fcf=0 and fwxtk=0 and falsales=0 and fkc=0
select fsjxh,fjh,fkt,fsg,fcf,fwxtk,falsales,fkc,(fjh+fkt+fsg+fcf-fwxtk-falsales)as ftotal from @TMps2