报餐制食堂月消费报表

用了一下动态SQL和游标，自己弄的外挂，记录下来自己查阅

USE [XXXXX]

GO
/****** 对象:  StoredProcedure [dbo].[monthfy2]    脚本日期: 09/05/2016 15:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[monthfy2]
@monthid int
as 
begin
declare @id int
declare @yggh int
declare @days int
declare @rq nvarchar(10)
declare @fy float
declare @cmd nvarchar(2000) 
declare @cb nvarchar(4)
declare @ts int
declare @yy int
declare @mm int
declare @mm2 CHAR(2)
declare @newts int
declare @newts1 int
declare @newts2 int
declare @newts3 int
declare @newts4 int
declare @sum float
declare @idtemp nvarchar(10) 
declare @gyidtemp nvarchar(10)
declare @mstart nvarchar(8)
declare @mend nvarchar(8)
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##tempfy'))
drop table ##tempfy
create table ##tempfy
(
ygid int,
fy float
)
--当月在职且非本月入的老员工  515
select @id=ID,@days=alldays,@yy=left(memo,4),@mm=substring(memo,6,2),@mm2=substring(memo,6,2) from s_session where ID=@monthid 
-- set @mstart=convert(varchar(4),@yy)+'-'+convert(varchar(2),@mm)+'-'+'01'
-- set @mend=convert(varchar(4),@yy)+'-'+convert(varchar(2),@mm)+'-'+convert(varchar(2),@days)
set @mstart=convert(varchar(4),@yy)+convert(char(2),@mm2)+'01'
set @mend=convert(varchar(4),@yy)+convert(varchar(2),@mm2)+convert(varchar(2),@days)
-- SELECT @mm2,@MSTART,@mend
declare xygh cursor for select   emp_id from pos_paican where  sessionid=@id and emp_id  in
(select emp_id from emp_baseinfo 
where DATEDIFF(DAY,convert(varchar(8),emp_pydate,112),@mstart)>0 and (DATEDIFF(DAY,convert(varchar(8),emp_lzdate,112),@mend)<0 or emp_lzdate is null))
--where datepart(yy,emp_pydate)<=@yy and datepart(mm,emp_pydate)<>@mm  and datepart(yy,emp_lzdate)>=@yy and datepart(mm,emp_lzdate) is null)
--and emp_id in (select emp_id from pos_paican where  sessionid=@monthid))


open  xygh
fetch next from xygh into @yggh
while @@fetch_status=0
begin
set @fy=0
set @sum=0
set @ts=@days
set @rq='B'+CONVERT(VARCHAR(2),@ts)
while @ts>0
begin
set @rq='B'+CONVERT(VARCHAR(2),@ts)
set @idtemp=convert(nvarchar(2),@id)
set @gyidtemp=convert(nvarchar(10),@yggh)
set @cmd='select  @cb='+@rq+' from pos_paican where  sessionid= '
set @cmd=@cmd+@idtemp+'  and emp_id= '+@gyidtemp
execute sp_executesql @cmd, N'@cb nvarchar(20) output', @cb output 
SELECT @fy=Pt_price/@days FROM Pos_Type where pt_code=@cb
set @sum=@sum+@fy
set @ts=@ts-1
end
set @sum=CEILING(@sum)
insert into ##tempfy values(@yggh,@sum)
fetch next from xygh into @yggh

end
CLOSE   xygh
    DEALLOCATE xygh
-- 在职且是本月入新员工
declare xygh1 cursor for select   emp_id from pos_paican where  sessionid=@id and emp_id  in
(select emp_id from emp_baseinfo 
where datepart(yy,emp_pydate)=@yy and datepart(mm,emp_pydate)=@mm and (DATEDIFF(DAY,convert(varchar(8),emp_lzdate,112),@mend)<0 or emp_lzdate is null))
--and emp_id in (select emp_id from pos_paican where  sessionid=@monthid))


open  xygh1
fetch next from xygh1 into @yggh
while @@fetch_status=0
begin
select @newts=datepart(dd,emp_pydate) from emp_baseinfo where emp_id=@yggh
set @fy=0
set @sum=0
set @ts=@days
set @rq='B'+CONVERT(VARCHAR(2),@ts)
while @ts>=@newts
begin
set @rq='B'+CONVERT(VARCHAR(2),@ts)
set @idtemp=convert(nvarchar(2),@id)
set @gyidtemp=convert(nvarchar(10),@yggh)
set @cmd='select  @cb='+@rq+' from pos_paican where  sessionid= '
set @cmd=@cmd+@idtemp+'  and emp_id= '+@gyidtemp
execute sp_executesql @cmd, N'@cb nvarchar(20) output', @cb output 
                SELECT @fy=Pt_price/@days FROM Pos_Type where pt_code=@cb
set @sum=@sum+@fy
set @ts=@ts-1
end
set @sum=CEILING(@sum)
insert into ##tempfy values(@yggh,@sum)
fetch next from xygh1 into @yggh

end
CLOSE   xygh1
    DEALLOCATE xygh1
--非当月入职且本月已经离职
declare xygh2 cursor for select   emp_id from pos_paican where  sessionid=@id and emp_id  in
(select emp_id from emp_baseinfo 
where DATEDIFF(DAY,convert(varchar(8),emp_pydate,112),@mstart)>0 
and  datepart(yy,emp_lzdate)=@yy and datepart(mm,emp_lzdate)=@mm
--where datepart(yy,emp_pydate)<=@yy and datepart(mm,emp_pydate)<>@mm 
--and datepart(yy,emp_lzdate)=@yy and datepart(mm,emp_lzdate)=@mm
)
--and emp_id in (select emp_id from pos_paican where  sessionid=@monthid))




open  xygh2
fetch next from xygh2 into @yggh
while @@fetch_status=0
begin
select @newts1=datepart(dd,emp_lzdate) from emp_baseinfo where emp_id=@yggh
set @fy=0
set @sum=0
set @ts=@newts1
set @rq='B'+CONVERT(VARCHAR(2),@ts)
while @ts>0
begin
set @rq='B'+CONVERT(VARCHAR(2),@ts)
set @idtemp=convert(nvarchar(2),@id)
set @gyidtemp=convert(nvarchar(10),@yggh)
set @cmd='select  @cb='+@rq+' from pos_paican where  sessionid= '
set @cmd=@cmd+@idtemp+'  and emp_id= '+@gyidtemp
execute sp_executesql @cmd, N'@cb nvarchar(20) output', @cb output 
                SELECT @fy=Pt_price/@days FROM Pos_Type where pt_code=@cb
set @sum=@sum+@fy
set @ts=@ts-1
end
set @sum=CEILING(@sum)
insert into ##tempfy values(@yggh,@sum)
fetch next from xygh2 into @yggh

end
CLOSE   xygh2
    DEALLOCATE xygh2
-- 当月入职当月离职
declare xygh3 cursor for select   emp_id from pos_paican where  sessionid=@id and emp_id  in
(select emp_id from emp_baseinfo 
where datepart(yy,emp_pydate)=@yy and datepart(mm,emp_pydate)=@mm 
and datepart(yy,emp_lzdate)=@yy and datepart(mm,emp_lzdate)=@mm)
--and emp_id in (select emp_id from pos_paican where  sessionid=@monthid))


open  xygh3
fetch next from xygh3 into @yggh
while @@fetch_status=0
begin
select @newts2=datepart(dd,emp_pydate) from emp_baseinfo where emp_id=@yggh
select @newts3=datepart(dd,emp_lzdate) from emp_baseinfo where emp_id=@yggh
set @fy=0
set @sum=0
set @ts=@newts3
set @rq='B'+CONVERT(VARCHAR(2),@ts)
while @ts>=@newts2
begin
set @rq='B'+CONVERT(VARCHAR(2),@ts)
set @idtemp=convert(nvarchar(2),@id)
set @gyidtemp=convert(nvarchar(10),@yggh)
set @cmd='select  @cb='+@rq+' from pos_paican where  sessionid= '
set @cmd=@cmd+@idtemp+'  and emp_id= '+@gyidtemp
execute sp_executesql @cmd, N'@cb nvarchar(20) output', @cb output 
                SELECT @fy=Pt_price/@days FROM Pos_Type where pt_code=@cb
set @sum=@sum+@fy
set @ts=@ts-1
end
set @sum=CEILING(@sum)
insert into ##tempfy values(@yggh,@sum)
fetch next from xygh3 into @yggh

end
CLOSE   xygh3
    DEALLOCATE xygh3




select emp_code,emp_name,fy, emp_pydate,emp_lzdate from ##tempfy
left join emp_baseinfo on ygid=emp_id
end