Create  PROC [GetPerformance](    @vp varchar(25),           @range char(1) ,           @depetid int   ,           @Q varchar(1),                 @QBeginDate datetime ,              @QEndDate datetime ,                    @YBeginDate datetime ,              @YEndDate datetime             )ASBegin      --1:创建临时表存储全部数据的容器      Create Table #MyTempTable        (            Did int ,                               MRange varchar(20),                     TeamName varchar(50),                   MGRName varchar(25),                                 M1 decimal(18,2),                       M2 decimal(18,2),                       M3 decimal(18,2),                                  C decimal(18,2),                        F decimal(18,2),                        J decimal(18,2),                        T decimal(18,2),                                    QuarterML DECIMAL(18,2),                       QuarterMLRW decimal(18,2),                     QuarterMLRate varchar(30),                                 YearHTPrice DECIMAL(18,2),                                 YearML DECIMAL(18,2),                       YearMLRW decimal(18,2),                     YearMLRate varchar(30),                     YML DECIMAL(18,2),            QML decimal(18,2)        )    --2：获得部门信息,利用游标循环每一个部门，获得相应的数据                declare @Did int                                declare @MRange varchar(20)                     declare @TeamName varchar(50)                   declare @MGRName varchar(25)                                declare @M1 decimal(18,2)                       declare @M2 decimal(18,2)                       declare @M3 decimal(18,2)                                   declare @C decimal(18,2)                        declare @F decimal(18,2)                        declare @J decimal(18,2)                        declare @T decimal(18,2)                                    declare @QuarterML DECIMAL(18,2)                       declare @QuarterMLRW decimal(18,2)                     declare @QuarterMLRate varchar(30)                                 declare @YearHTPrice DECIMAL(18,2)                                 declare @YearML DECIMAL(18,2)                          declare @YearMLRW decimal(18,2)                        declare @YearMLRate varchar(30)                                    declare @vpid varchar(25)                  -                                    declare @YML decimal(18,2)   --用于排序没有百分比            declare @QML decimal(18,2)        --3:获得年度     declare @nowYear varchar(20)    --当前年     set @nowYear = Year(GETDATE())  --默认为当前年     if(MONTH(GETDATE())=1 or MONTH(GETDATE())=2 or MONTH(GETDATE())=3)    -- 当前时间为1，2，3月     Begin          if(@Q = ‘1‘ or @Q = ‘2‘ or @Q = ‘3‘)          begin          set @nowYear = Year(GETDATE()) - 1          end     End     Else      Begin                         ---否则4~12月为当前年，查询的季度为4，则年+1          if(@Q = ‘4‘)          Begin          set @nowYear = Year(GETDATE()) + 1          End      End          --3:获得季度是从那年开始的，方便取出任务     declare @qrwYear varchar (20)     set @qrwYear = Year(GETDATE())     if(MONTH(GETDATE())=1 or MONTH(GETDATE())=2 or MONTH(GETDATE())=3)    -- 当前时间为1，2，3月，并且查询的季度是1.2.3,则年减1     Begin        set @qrwYear = Year(GETDATE()) - 1    End     ---------------------------------------------------------------------------------------     --4:获得月分     declare @tempm1 varchar(10)  --第一个月     declare @tempm2 varchar(10)  --第二个月     declare @tempm3 varchar(10)  --第三个月     if(@Q = ‘1‘)     Begin       set @tempm1 =‘4‘       set @tempm2 =‘5‘       set @tempm3 =‘6‘     End      else if (@Q = ‘2‘)     Begin       set @tempm1 =‘7‘       set @tempm2 =‘8‘       set @tempm3 =‘9‘     End     else if (@Q = ‘3‘)     Begin       set @tempm1 =‘10‘       set @tempm2 =‘11‘       set @tempm3 =‘12‘     End     else if (@Q = ‘4‘)     Begin       set @tempm1 =‘1‘       set @tempm2 =‘2‘       set @tempm3 =‘3‘     End   ---------------------------------------------------------------------------------------             declare cur cursor  --定义一个游标    read_only    for            SELECT     dbo.Department.DepartmentID, CASE WHEN Department.Range = ‘0‘ THEN ‘内‘ ELSE ‘外‘ END AS ‘范围‘, dbo.Department.DepartmentName AS ‘部门‘,                        T.mgrName AS ‘总监‘, M.VPEid            FROM         dbo.Department LEFT OUTER JOIN                          (SELECT     dbo.Employee.EmployeeID AS mgrEid, dbo.Employee.DepartmentID AS mgrDid, dbo.Employee.EmployeeName AS mgrName                            FROM          dbo.Employee INNER JOIN                                                   dbo.Role ON dbo.Employee.RoleID = dbo.Role.RoleID                            WHERE      (dbo.Role.RoleName = ‘salesmgr‘) AND (dbo.Employee.State = 1)) AS T ON T.mgrDid = dbo.Department.DepartmentID LEFT OUTER JOIN                          (SELECT     Employee_1.EmployeeID AS VPEid, Employee_1.EmployeeName AS VPName, dbo.EmployeeOwnDepartment.DepartmentID AS VPID                            FROM          dbo.Employee AS Employee_1 INNER JOIN                                                   dbo.Role AS Role_1 ON Employee_1.RoleID = Role_1.RoleID RIGHT OUTER JOIN                                                   dbo.EmployeeOwnDepartment ON Employee_1.EmployeeID = dbo.EmployeeOwnDepartment.EmployeeID                            WHERE      (Role_1.RoleName = ‘saleVP‘) AND (Employee_1.State = 1)) AS M ON M.VPID = dbo.Department.DepartmentID                        WHERE     (dbo.Department.IsValid = 1)            and (@vp =‘‘ or M.VPEid=@vp)            and (@range =‘‘ or Department.Range = @range)            and (@depetid =‘‘ or Department.DepartmentID=@depetid)    open cur            --打开游标            fetch next from cur into @Did,@MRange,@TeamName,@MGRName,@vpid --把提取操作的列数据放到局部变量中    while(@@fetch_status=0) --返回被 FETCH 语句执行的最后游标的状态     Begin        --1:获得季度内3个月份的合同金额                select @M1=CAST((SUM(SalesAgreement.HTPrice)/10000) as decimal(18,2)) from SalesAgreement inner join Project on Project.ProjectID=SalesAgreement.ProjectID        where project.EmployeeID in ( SELECT EmployeeID FROM Employee WHERE DepartmentID= @Did )        AND YEAR(SalesAgreement.HtDate) = @nowYear AND MONTH(SalesAgreement.HtDate)=@tempm1        --4: 季度毛利任务        select @QuarterMLRW = MAX(ML) from DeptQuarterTask where [Year]=@qrwYear and [Quarter]=@Q AND DepartmentID=@Did        --5: 季度完成率        if(@QuarterMLRW = 0)        begin           set  @QuarterMLRate = ‘0‘           set @QML = 0        end        else         begin           set @QuarterMLRate =CAST (  cast (  ( (@QuarterML / @QuarterMLRW) * 100) as decimal(18,2) )    as varchar(30))  + ‘%‘           set @QML = cast (   (  (@QuarterML / @QuarterMLRW) * 100) as decimal(18,2))         end                --10：插入各个字段的值到临时表产生一行部门的所有数据        insert into #MyTempTable values(             @Did ,             @MRange ,             @TeamName ,             @MGRName,             @M1 ,             @M2 ,             @M3 ,             @C ,             @F ,             @J ,             @T ,             @QuarterML ,             @QuarterMLRW ,             @QuarterMLRate ,             @YearHTPrice ,             @YearML ,             @YearMLRW ,             @YearMLRate , @YML,@QML)                --提前下一位信息        fetch next from cur into @Did,@MRange,@TeamName,@MGRName,@vpid    End    close cur     deallocate cur --删除游标    select * from #MyTempTable order by  MRange asc,YML desc , QML desc , TeamName asc    --查询临时表传入数据进行分页    --set @sql = ‘select * from #MyTempTable‘    --set @sqlCountText = ‘select count(*) from #MyTempTable‘        ----执行分页存储过程    --exec dbo.GetPageData @sql,@PageCurrent,@PageSize,@PageCount,@sqlCountText        --删除临时表    drop table #MyTempTableEnd