查询历史销售记录.sql

手机进销存系统Delphi源码,管理手机的进货还有销售方面的功能

/**********************************************
查询历史销售记录
************************************************/
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