统计加班时间的存储过程。

来源：互联网发布：python pika 编辑：程序博客网时间：2024/05/16 10:53

CREATE PROCEDURE dt_jb_tg
(
@fristtime int,
@lasttime int,
@depid int,
@IntDateTypeID int
)
AS
Declare @S1 Nvarchar(4000),@S2 Nvarchar(4000),@S3 Nvarchar(4000)
Set @S1=''
Set @S2=''
Select @S1=@S1+',SUM(Case IntDay When '+Rtrim(IntDay)+N' Then IntHoursYd Else 0 End) As '''+Rtrim(IntDay)+'''',
@S2=@S2+',SUM(Case IntDay When '+Rtrim(IntDay)+N' Then IntHoursYx Else 0 End) As '''+Rtrim(IntDay)+''''
From vwTG where intyear*10000+intmonth*100+intday between @fristtime and @lasttime and UserDeptID=@depid and IntDateTypeID=@IntDateTypeID Group By IntDay Order By IntDay
EXEC(N'Select * From(Select 姓名+N''预定加班时间'' As 姓名,工号 '+@S1+N',SUM(IntHoursYd) As 合计 From vwTG where intyear*10000+intmonth*100+intday between ('+@fristtime+') and ('+@lasttime+') and UserDeptID=('+ @depid +') and IntDateTypeID=('+ @IntDateTypeID +') Group By 姓名+N''预定加班时间'',工号 Union All Select 姓名,工号'+@S2+N',SUM(IntHoursYx) As 合计 From vwTG where intyear*10000+intmonth*100+intday between ('+@fristtime+') and ('+@lasttime+') and UserDeptID=('+ @depid +') and IntDateTypeID=('+ @IntDateTypeID +') Group By 姓名,工号) A Order By (Case 姓名 When N''合计'' Then 1 Else 0 End), 工号,姓名 Desc')
GO