存储过程--虚拟表

 一般用于报表，前提：报表中部分是从一些表中查询而得，而另一部分是通过各种计算而得，这种情况下，可用此存储过程。纯属个人记录，不供大家学习。

如下：

USE [changyuan]

GO

/****** Object:  StoredProcedure [dbo].[sp_adv_qy_jpdata_Select_byDates]    Script Date: 05/19/2013 21:52:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

ALTER PROC [dbo].[sp_adv_qy_jpdata_Select_byDates] --[sp_adv_qy_jpdata_Select_byDates] "2006-11-6", "2006-11-13"  ,5         

(             

@time1 datetime,             

@time2 datetime=null,             

@qy int=null                    

)              

as

--天数     

declare @i int   

set @i=0 

--select @i= DATEDIFF(day,@time1,@time2)     

--set @i=@i+1   

--print @i   

 

--表1 显示最终结果    

CREATE TABLE #TABLE            

(argname nvarchar(100) , lshds int DEFAULT ((0)), renshu int DEFAULT ((0)),ytjs int DEFAULT ((0)), ywcs int DEFAULT ((0)), wwcs int DEFAULT ((0)), wcl nvarchar(100) DEFAULT ((0)),id int )           

 

--插入所有分公司 

insert into #TABLE (id,argname) 

select id,argname from tb_area where fatherid='1' 

 

--分公司所有店的提交数 

CREATE TABLE #TABLE1            

(eid int, qy int)  

 

--时间段内的所有周一 

set @time1=(select(convert(datetime,@time1)-datepart(dw,convert(datetime,@time1))+2))  

  

while(@time1 <=@time2)         

begin    

 

if((select datepart(dw,@time1))=2)   

begin   

 

insert into #TABLE1 

select distinct eid, (select fatherid from tb_area where id=(select fatherid from tb_area where id=(select lshfgs from tb_rt where lshid=shop))) as qy from jpdata left join employee on jpdata.eid=employee.employeeid where employee.available=1 and salesdate = @time1 

 

--select distinct salesdate, (select fatherid from tb_area where id=(select fatherid from tb_area where id=(select lshfgs from tb_rt where lshid=shop))) as qy from jpdata where salesdate = @time1 

 

set @time1=DateADD(day,1,@time1) 

set @i=@i+1      

end    

 

 

 

 

set @time1=@time1+1   

end       

print @i     

      

--select * from #TABLE1 

--岗位人数   

update #TABLE set renshu=isnull((select count(*) from employee where (available=1 or (available=0 and lzhshj > ''+cast(''+@time2 + ' 23:59' as varchar)+'')) and  employee.qy=#TABLE.id ),0) from employee where  available=1 and  employee.qy=#TABLE.id 

 

--零售店数   

update #TABLE set lshds=isnull((select count(*) from tb_rt where  lshq=#TABLE.id ),0) from tb_rt where lshq=#TABLE.id   

 

--应提交数     

update #TABLE set ytjs=renshu*@i  

     

--已提交数     

update #table set ywcs=(select count(*) from #table1 where #TABLE1.qy=#table.id) from #TABLE1 where #table1.qy=#table.id 

     

--未提交数     

update #TABLE set wwcs=(ytjs-ywcs)     

     

--提交完成率     

update #TABLE set wcl=round(convert(float,ywcs)*100/(CASE WHEN ytjs=0 THEN 1 ELSE ytjs END) ,2)     

      

if @qy is not null and @qy <>''   

begin    

select * from #table where id=@qy   

end   

else   

begin   

select * from #table  

end     

--drop table #table 

--drop table #table1