部门消费次数统计表(sql)
--------------
declare @strName varchar(50)
declare @strSQL varchar(8000)
declare @strwhere varchar (300)
declare @strSDate varchar(6)
declare @strEDate varchar(6)
set @strSDate=datename(year,'@FSDate')+datename(month,'@FSDate')
set @strEDate=datename(year,'@FEDate')+datename(month,'@FEDate')
set @strSQL=''
--第一个数据源
select '部门消费次数统计表' as [报表标题],'日期:'+convert(char(10),cast('@FSDate' as datetime),20)
+'--'+convert(char(10),cast('@FEDate' as datetime),20)+' 报表日期:'+convert(char
(10),GetDate(),20) as [编制日期]
create table #Temp_FDepaIDs
(FDepaID int)
insert Into #Temp_FDepaIDs(FDepaID)
exec dbo.m_Get_AllMulFDepaIDs '@FDepaID'
--中餐人次
declare cur_TableName cursor for (
select name from sysobjects where xtype='u' and name like 'kq_cardrecord%'
and right(name,6)>=@strSDate and right(name,6)<=@strEDate
)
open cur_TableName
fetch next from cur_TableName into @strName
while @@fetch_status<>-1
begin
set @strSQL=@strSQL+'union Select Hr_Employee.FDepaID as 部门, count(*) as 中餐人次 from '+@strName+'
Left Outer Join Hr_Employee On '+@strName+'.FEmplID=Hr_Employee.FEmplID
where FType=''02''
and FDepaID in (Select FDepaID From #Temp_FDepaIDs)
and (Hr_Employee.FEmplType =''@FEmplType'' or ''@FEmplType'' ='''')
and DateDiff(day, '+@strName+'.FCardDay ,''@FSDate'')<=0
and DateDiff(day, '+@strName+'.FCardDay,''@FEDate'')>=0
and DateDiff(Hour, '+@strName+'.FCardTime ,''10:30:00'')<=0
and DateDiff(Hour, '+@strName+'.FCardTime,''13:00:00'')>=0
group by Hr_Employee.FDepaID '
fetch next from cur_TableName into @strName
end
close cur_TableName
deallocate cur_TableName
set @strSQL=substring(@strSQL,6,len(@strSQL))
create table #Kd_date1
(
部门 varchar(30),
中餐人次 int
)
insert into #KD_date1(部门,中餐人次)
exec(@strSQL)
--晚餐人次
declare @strSQL1 varchar(8000)
set @strSQL1=''
declare cur_TableName1 cursor for (
select name from sysobjects where xtype='u' and name like 'kq_cardrecord%'
and right(name,6)>=@strSDate and right(name,6)<=@strEDate
)
open cur_TableName1
fetch next from cur_TableName1 into @strName
while @@fetch_status<>-1
begin
set @strSQL1=@strSQL1+'union Select Hr_Employee.FDepaID as 部门, count(*) as 晚餐人次 from '+@strName+'
Left Outer Join Hr_Employee On '+@strName+'.FEmplID=Hr_Employee.FEmplID
where FType=''02''
and FDepaID in (Select FDepaID From #Temp_FDepaIDs)
and (Hr_Employee.FEmplType =''@FEmplType'' or ''@FEmplType'' ='''')
and DateDiff(day, '+@strName+'.FCardDay ,''@FSDate'')<=0
and DateDiff(day, '+@strName+'.FCardDay,''@FEDate'')>=0
and DateDiff(Hour, '+@strName+'.FCardTime ,''16:30:00'')<=0
and DateDiff(Hour, '+@strName+'.FCardTime,''18:30:00'')>=0
group by Hr_Employee.FDepaID '
fetch next from cur_TableName1 into @strName
end
close cur_TableName1
deallocate cur_TableName1
set @strSQL1=substring(@strSQL1,6,len(@strSQL1))
create table #Kd_date2
(
部门 varchar(30),
晚餐人次 int
)
insert into #KD_date2(部门,晚餐人次)
exec(@strSQL1)
--夜宵人次
declare @strSQL2 varchar(8000)
set @strSQL2=''
declare cur_TableName2 cursor for (
select name from sysobjects where xtype='u' and name like 'kq_cardrecord%'
and right(name,6)>=@strSDate and right(name,6)<=@strEDate
)
open cur_TableName2
fetch next from cur_TableName2 into @strName
while @@fetch_status<>-1
begin
set @strSQL2=@strSQL2+'union Select Hr_Employee.FDepaID as 部门, count(*) as 夜宵人次 from '+@strName+'
Left Outer Join Hr_Employee On '+@strName+'.FEmplID=Hr_Employee.FEmplID
where FType=''02''
and FDepaID in (Select FDepaID From #Temp_FDepaIDs)
and (Hr_Employee.FEmplType =''@FEmplType'' or ''@FEmplType'' ='''')
and DateDiff(day, '+@strName+'.FCardDay ,''@FSDate'')<=0
and DateDiff(day, '+@strName+'.FCardDay,''@FEDate'')>=0
and DateDiff(Hour, '+@strName+'.FCardTime ,''00:01:00'')<=0
and DateDiff(Hour, '+@strName+'.FCardTime,''04:00:00'')>=0
group by Hr_Employee.FDepaID '
fetch next from cur_TableName2 into @strName
end
close cur_TableName2
deallocate cur_TableName2
set @strSQL2=substring(@strSQL2,6,len(@strSQL2))
create table #Kd_date3
(
部门 varchar(30),
夜宵人次 int
)
insert into #KD_date3(部门,夜宵人次)
exec(@strSQL2)
create table #date
(
部门 varchar(50),
人数 int default 0,
中餐人次 int default 0,
晚餐人次 int default 0,
夜宵人次 int default 0,
合计 int default 0
)
insert into #date(部门,人数)
select Bd_Department.FDepaID as 部门, count(*) as '人数' from Hr_Employee
left outer join Bd_Department on Bd_Department.FDepaID=Hr_Employee.FDepaID
left outer join Hr_JobOutApply on Hr_Employee.FEmplID=Hr_JobOutApply.FEmplID
where Hr_Employee.FDepaID in (Select FDepaID From #Temp_FDepaIDs)
and (Hr_Employee.FEmplType ='@FEmplType' or '@FEmplType' ='')
and (DateDiff(day,'@FSDate',Hr_JobOutApply.FOutDate)>0 or FJobStatus In('002','003')
and (DateDiff(day,Hr_Employee.FInDutyDate,'@FSDate')>0) And DateDiff(day,'@FSDate','@FEDate')>=0)
group by Bd_Department.FDepaID
update #date
set 中餐人次=#KD_date1.中餐人次 from #KD_date1
where #KD_date1.部门=#date.部门
update #date
set 晚餐人次=#KD_date2.晚餐人次 from #KD_date2
where #KD_date2.部门=#date.部门
update #date
set 夜宵人次=#KD_date3.夜宵人次 from #KD_date3
where #KD_date3.部门=#date.部门
Select Bd_Department.FDepaName as 部门,人数,中餐人次,晚餐人次,夜宵人次,中餐人次+晚餐人次+夜宵人次 as 合计 from #date
left outer join Bd_Department on Bd_Department.FDepaID=#date.部门
drop table #KD_date1
drop table #KD_date2
drop table #KD_date3
drop table #Temp_FDepaIDs
drop table #date
--第三个数据源
select col from
(
select '部门' as col ,'001' as sort
union select '人数' as col, '002' as sort
union select '中餐人次' as col,'003' as sort
union select '晚餐人次' as col, '004' as sort
union select '夜宵人次' as col, '005' as sort
union select '合计' as col, '006' as sort
) aa order by sort
