27,579
社区成员
发帖
与我相关
我的任务
分享
create PROCEDURE spGetSM_FiveSCheck
@employeeName NVARCHAR(20)='',
@BeginDate DATETIME,
@EndDate DATETIME
AS
BEGIN
declare @sql varchar(8000)
DECLARE @TempDate DATETIME
set @sql = 'select employeeName'
select @sql = @sql + ' , max(case ckDate when ''' + convert(NVARCHAR(10),ckDate,121)
+ ''' then score else 0 end) [' + convert(nvarchar(10),ckDate,121) + ']'
+ ',max(case ckDate when ''' + convert(nvarchar(10),ckDate,121)
+ ''' then remark else '''' end) [' + convert(nvarchar(10),ckDate,121) + '_备注]'
from SM_FiveSCheck
WHERE [status]='汇总' AND
(employeeName =@employeeName OR @employeeName='') AND
DATEDIFF(DAY,@BeginDate,ckDate)>=0 AND
DATEDIFF(DAY,ckDate,@EndDate)>=0
group by ckDate
order by ckDate
set @sql = @sql + ' from SM_FiveSCheck group by employeeName'
exec(@sql)
CREATE TABLE #TempFields([id] INT IDENTITY,fieldname NVARCHAR(50),columnname NVARCHAR(50),columntype NVARCHAR(50),width INT,needfilter INT,backcolor NVARCHAR(20),alignment NVARCHAR(20))
INSERT INTO #TempFields(fieldname,columnname,width,needfilter,columntype) VALUES('selected','',30,0,'SelectorColumnStyle')
INSERT INTO #TempFields(fieldname,columnname,width,needfilter) VALUES('employeeName','员工姓名',80,1)
SET @TempDate = @BeginDate
WHILE DATEDIFF(DAY,@TempDate,@EndDate)>0
BEGIN
INSERT INTO #TempFields(fieldname,columnname,width,needfilter)
VALUES(CONVERT(NVARCHAR(10),@TempDate,121),null,80,0)
INSERT INTO #TempFields(fieldname,columnname,width,needfilter)
VALUES(CONVERT(NVARCHAR(10),@TempDate,121)+'_备注',null,110,0)
SET @TempDate = DATEADD(DAY,1,@TempDate)
END
SELECT 1 controlid,1 gridid,'SM_FiveSCheck' mappingname,0 alternatingbackcolor,0 backcolor,0 forecolor,
0 isallowdelete,0 isallowedit,0 isallownew,1 isheadervisible,'' headerfontname,0 headerfontsize,
0 headerforecolor,0 headerbackcolor,1 iscaptionvisible,'' captiontext,'' fontfamily,
0 fontsize,'' [description],0 allowsorting
SELECT 1 gridid,[id] columnid,case when columnname is null then fieldname else columnname end columnname,ISNULL(columntype,'LabelColumnStyle') columntype,fieldname mappingname,
'' mappingtype,ISNULL(width,100) width,'Left' alignment,1 isreadonly,0 isallownull,0 ishidden,'' [format],
'' nullvalue,'' truevalue,'' falsevalue,'White' backcolor,'Black' forecolor,
'' backcolorcondition,'' forecolorcondition,'宋体' fontfamily,9 fontsize,'Regular' fontstyle,
'' items,ISNULL(needfilter,0) needfilter,'' tablename,'' visiblecondition,'' enabledcondition,
null validate,null validateinfo,null validatepattern,null defaultvalue
FROM #TempFields
ORDER BY [id]
CREATE TABLE #TempTables(
tableid INT IDENTITY,
tablename NVARCHAR(100)
)
INSERT INTO #TempTables VALUES('SM_FiveSCheck')
INSERT INTO #TempTables VALUES('t_sheetgrids')
INSERT INTO #TempTables VALUES('t_sheetcolumns')
INSERT INTO #TempTables VALUES('t_tables')
SELECT * FROM #TempTables ORDER BY tableid
END
declare @sql varchar(8000)
DECLARE @TempDate DATETIME
set @sql = 'select employeeName,max(department)'
select @sql = @sql + ' , max(case ckDate when ''' + convert(NVARCHAR(10),ckDate,121)
+ ''' then score else 0 end) [' + convert(nvarchar(10),ckDate,121) + ']'
+ ',max(case ckDate when ''' + convert(nvarchar(10),ckDate,121)
+ ''' then remark else '''' end) [' + convert(nvarchar(10),ckDate,121) + '_备注]'
from SM_FiveSCheck
WHERE [status]='汇总' AND
(employeeName =@employeeName OR @employeeName='') AND
DATEDIFF(DAY,@BeginDate,ckDate)>=0 AND
DATEDIFF(DAY,ckDate,@EndDate)>=0
group by ckDate
order by ckDate
set @sql = @sql + ' from SM_FiveSCheck group by employeeName with rollup'
exec(@sql)
declare @sql varchar(8000)
DECLARE @TempDate DATETIME
set @sql = 'select employeeName,department'
select @sql = @sql + ' , max(case ckDate when ''' + convert(NVARCHAR(10),ckDate,121)
+ ''' then score else 0 end) [' + convert(nvarchar(10),ckDate,121) + ']'
+ ',max(case ckDate when ''' + convert(nvarchar(10),ckDate,121)
+ ''' then remark else '''' end) [' + convert(nvarchar(10),ckDate,121) + '_备注]'
from SM_FiveSCheck
WHERE [status]='汇总' AND
(employeeName =@employeeName OR @employeeName='') AND
DATEDIFF(DAY,@BeginDate,ckDate)>=0 AND
DATEDIFF(DAY,ckDate,@EndDate)>=0
group by ckDate
order by ckDate
set @sql = @sql + ',(select sum(score) from SM_FiveSCheck where employeeName = t.employeeName '
+' and department = t.department) as emp_sum '
+' from SM_FiveSCheck t group by employeeName,department'
exec(@sql)
declare @sql varchar(8000)
DECLARE @TempDate DATETIME
set @sql = 'select employeeName,department'
select @sql = @sql + ' , max(case ckDate when ''' + convert(NVARCHAR(10),ckDate,121)
+ ''' then score else 0 end) [' + convert(nvarchar(10),ckDate,121) + ']'
+ ',max(case ckDate when ''' + convert(nvarchar(10),ckDate,121)
+ ''' then remark else '''' end) [' + convert(nvarchar(10),ckDate,121) + '_备注]'
from SM_FiveSCheck
WHERE [status]='汇总' AND
(employeeName =@employeeName OR @employeeName='') AND
DATEDIFF(DAY,@BeginDate,ckDate)>=0 AND
DATEDIFF(DAY,ckDate,@EndDate)>=0
group by ckDate
order by ckDate
set @sql = @sql + ' from SM_FiveSCheck group by employeeName,department'
exec(@sql)
declare @sql varchar(8000)
DECLARE @TempDate DATETIME
set @sql = 'select a.employeeName,b.department'
select @sql = @sql + ' , max(case ckDate when ''' + convert(NVARCHAR(10),ckDate,121)
+ ''' then score else 0 end) [' + convert(nvarchar(10),ckDate,121) + ']'
+ ',max(case ckDate when ''' + convert(nvarchar(10),ckDate,121)
+ ''' then remark else '''' end) [' + convert(nvarchar(10),ckDate,121) + '_备注]'
from SM_FiveSCheck
WHERE [status]='汇总' AND
(employeeName =@employeeName OR @employeeName='') AND
DATEDIFF(DAY,@BeginDate,ckDate)>=0 AND
DATEDIFF(DAY,ckDate,@EndDate)>=0
group by ckDate
order by ckDate
set @sql = @sql + ' from SM_FiveSCheck a,SM_FiveSCheck b where a.employeeName=b.employeeName
group by a.employeeName,b.department'
exec(@sql)
declare @sql varchar(8000)
DECLARE @TempDate DATETIME
set @sql = 'select a.employeeName,b.部门名'
select @sql = @sql + ' , max(case a.ckDate when ''' + convert(NVARCHAR(10),ckDate,121)
+ ''' then a.score else 0 end) [' + convert(nvarchar(10),ckDate,121) + ']'
+ ',max(case a.ckDate when ''' + convert(nvarchar(10),ckDate,121)
+ ''' then a.remark else '''' end) [' + convert(nvarchar(10),ckDate,121) + '_备注]'
from SM_FiveSCheck
WHERE [status]='汇总' AND
(employeeName =@employeeName OR @employeeName='') AND
DATEDIFF(DAY,@BeginDate,ckDate)>=0 AND
DATEDIFF(DAY,ckDate,@EndDate)>=0
group by ckDate
order by ckDate
set @sql = @sql + ' from SM_FiveSCheck a join 部门表 b on a.部门ID = b.部门ID group by a.employeeName,b.部门名'
exec(@sql)
declare @sql varchar(8000)
DECLARE @TempDate DATETIME
set @sql = 'select employeeName,max(department)'
select @sql = @sql + ' , max(case ckDate when ''' + convert(NVARCHAR(10),ckDate,121)
+ ''' then score else 0 end) [' + convert(nvarchar(10),ckDate,121) + ']'
+ ',max(case ckDate when ''' + convert(nvarchar(10),ckDate,121)
+ ''' then remark else '''' end) [' + convert(nvarchar(10),ckDate,121) + '_备注]'
from SM_FiveSCheck
WHERE [status]='汇总' AND
(employeeName =@employeeName OR @employeeName='') AND
DATEDIFF(DAY,@BeginDate,ckDate)>=0 AND
DATEDIFF(DAY,ckDate,@EndDate)>=0
group by ckDate
order by ckDate
set @sql = @sql + ' from SM_FiveSCheck group by employeeName'
exec(@sql)
declare @sql varchar(8000)
DECLARE @TempDate DATETIME
set @sql = 'select employeeName'
select @sql = @sql + ' , max(case ckDate when ''' + convert(NVARCHAR(10),ckDate,121)
+ ''' then score else 0 end) [' + convert(nvarchar(10),ckDate,121) + ']'
+ ',max(case ckDate when ''' + convert(nvarchar(10),ckDate,121)
+ ''' then remark else '''' end) [' + convert(nvarchar(10),ckDate,121) + '_备注]'
from SM_FiveSCheck
WHERE [status]='汇总' AND
(employeeName =@employeeName OR @employeeName='') AND
DATEDIFF(DAY,@BeginDate,ckDate)>=0 AND
DATEDIFF(DAY,ckDate,@EndDate)>=0
group by ckDate
order by ckDate
set @sql = @sql + ' from SM_FiveSCheck group by employeeName'
exec(@sql)