部門消費次數統計表(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