存储过程及视图

CREATE   PROCEDURE [dbo].[PROC_INSERT_DATA_ID]
  
  AS
  BEGIN
delete from t_user_special
                
insert into t_user_special(userName,dateOfBirth,fileNo,type) select name,dateOfBirth,fileNo,'1' from t_File_Information where datediff(MONTH,cast([dateOfBirth] as datetime),GETDATE())>694 and datediff(MONTH,cast([dateOfBirth] as datetime),GETDATE())<706 and sex=1 and dr=1


insert into t_user_special(userName,dateOfBirth,fileNo,type) select name,dateOfBirth,fileNo,'1' from t_File_Information where datediff(MONTH,cast([dateOfBirth] as datetime),GETDATE())>658 and datediff(MONTH,cast([dateOfBirth] as datetime),GETDATE())<670 and sex=2  and dr=1


insert into t_user_special(userName,dateOfBirth,fileNo,type) select name,dateOfBirth,fileNo,'2' from t_File_Information where datediff(MONTH,cast([workingTime] as datetime),GETDATE())>48 and datediff(MONTH,cast([workingTime] as datetime),GETDATE())<60 and dr=1
            
    END



GO

<pre name="code" class="java">CREATE proc [dbo].[changeState]
as 
declare @evaluationid as int

declare ids CURSOR  for
select evaluationid from EVALUATION_BATCH where convert(datetime,endDate)<getdate() and states in(1,2);

update EVALUATION_BATCH set states=2 where convert(datetime,startDate)<getdate() and states=1;

open ids

fetch next from ids into @evaluationid

 WHILE @@FETCH_STATUS = 0
 BEGIN
 begin
update EVALUATION_BATCH set states=3 where evaluationid=@evaluationid
end
begin
exec delc @evaluationid
print @evaluationid
end
fetch next from ids into @evaluationid

END
close ids
deallocate ids

GO

CREATE  PROCEDURE [dbo].[delc] @evaluationids int
AS
BEGIN

DECLARE @evaluationid as int
DECLARE @groupid as int
DECLARE @delmaxno as int
DECLARE @delminno as int
DECLARE @loaduserid as int

DECLARE @id as int

DECLARE @sql as NVARCHAR(4000)
update t_people_result_content set i = null where  evaluationid=@evaluationids
 DECLARE rs CURSOR LOCAL SCROLL FOR
 select loaduserid,evaluationid,groupid,isnull(delmaxno,0),isnull(delminno,0) from bb where evaluationId=@evaluationids
 OPEN rs
 FETCH NEXT FROM rs INTO @loaduserid,@evaluationid,@groupid,@delmaxno,@delminno
 WHILE @@FETCH_STATUS = 0
 BEGIN
 if @delmaxno>0
 begin
	set @sql='update t_people_result_content set i=1 where peopleResultId in(select top '+ cast(isnull(@delmaxno,0)as varchar)  +' peopleResultId from aa where evaluationid='+cast(@evaluationid as varchar)  +' and groupid='+cast(@groupid as varchar)+' and byEvaluationUserID = '+cast(@loaduserid as varchar)+' order by cast(result as float) desc)'
	
	exec  sp_executesql @sql    
end
 if @delminno>0
 begin
	set @sql='update t_people_result_content set i=1 where peopleResultId in(select top '+ cast(isnull(@delminno,0)as varchar)  +' peopleResultId from aa where evaluationid='+cast(@evaluationid as varchar)  +' and groupid='+cast(@groupid as varchar)+' and byEvaluationUserID = '+cast(@loaduserid as varchar)+' order by cast(result as float) )'
	exec  sp_executesql @sql
   print @sql
end
 
 FETCH NEXT FROM rs INTO @loaduserid,@evaluationid,@groupid,@delmaxno,@delminno
 END
 CLOSE rs
--exec delc
	
END

CREATE VIEW [dbo].[people_result1]
AS
SELECT     m.groupId, t.evaluationId, t.byEvaluationUserName, t.byEvaluationUserId, CAST(AVG(ISNULL(t.result * t.weightedValue / 100, 0)) AS decimal(15, 2)) AS result
FROM         dbo.t_people_result_content AS t INNER JOIN
                      dbo.t_memberShip AS m ON t.evaluationUserId = m.userId
WHERE     (t.checkIn = 0) AND (t.i IS NULL)
GROUP BY t.evaluationId, t.byEvaluationUserName, t.byEvaluationUserId, m.groupId