经典的SQL

CREATE Proc dbo.MyReport

 @BeginDate varchar(10),
 @DeptID    varchar(10),
 @PerID   varchar(20),
 @OrderBy  varchar(20),

 @SubmitButtonFlag varchar(20),
 @OperName  varchar(20),
 @OperID   varchar(20),
 @OperIP   varchar(200),
 @PageName  varchar(200)

as
 
 --调试
 --  exec MyReport '2010-07-22','','','','AllDept','王小刚', 'JCNEP4699','127.0.0.1','/reportnew/mcm/mcm_index.jsp'

 --      exec MyReport '2010-10-26','','','tnum','AllDept','王小刚', 'JCNEP4699','192.168.61.104','/reportnew/mcm/mcm_index.jsp'

 

 

 

 --定义变量
 declare @OperDeptID int
 declare @OperPerTypeID varchar(20)
 declare @StrSql varchar(8000)
 declare @MonthDays int
 declare @SqlStr varchar(8000)

 declare @ExecStr varchar(200)
 declare @ProcName varchar(200)
 declare @ExecBeginDate datetime
 declare @ExecEndDate datetime
 declare @CostTime varchar(20)
 declare @M1 varchar(20)
 declare @PP1 varchar(20)
 declare @PP2 varchar(20)
 declare @ManagerPerName varchar(50)
 declare @DeptNameSelect varchar(20)

 --declare @FieldStr varchar(2000)

 --给传入的变量去空格
 set @BeginDate=Ltrim(Rtrim(@BeginDate)) 
 set @DeptID=Ltrim(Rtrim(@DeptID)) 
 set @SubmitButtonFlag=Ltrim(Rtrim(@SubmitButtonFlag)) 
 set @OperName=Ltrim(Rtrim(@OperName)) 
 set @OperID=Ltrim(Rtrim(@OperID)) 
 set @OperIP=Ltrim(Rtrim(@OperIP)) 
 set @PageName=Ltrim(Rtrim(@PageName)) 
 

 --判断管控人员所管理的部门
 --    select DeptID from jobcn_boss_hr.dbo.PerDeptPart  where  PerID = 'JCNEP4625'
 select DeptID,DeptName  from Person_Main where DeptID in (select DeptID from jobcn_boss_hr.dbo.PerDeptPart  where  PerID = 'JCNEP4625') order by DeptID

 --begin 检查经理和销售人员的权限
 --如果是经理，则只能查看本部门的
 --如果是销售人员，则视情况处理，有些可以看，有些不能看
 select  top 1 '统计时间：'+jobcn_boss_statistic.dbo.F_ConvertDateToChinese(convert(varchar(20),getdate(),120),'partAll')  as ReportStatisticDate
   --,'时间:'+@BeginDate+'到'+@EndDate as ReportTableDate
   ,'部门每日签单' as XslTitle
   ,'部门每日签单' as ReportTableTitle
   ,@SubmitButtonFlag as SubmitButtonFlag
 from Person_Main table0
 for xml auto

 select @OperDeptID=DeptID,@OperPerTypeID=PerTypeID from Person_Main where ID=@OperID
 if @@rowcount<=0
  begin
   return 0  
  end

 if(@OperPerTypeID='2700' or  (@OperPerTypeID='2701' or @OperPerTypeID='2706') and @DeptID='' ) and @OperID<>'JCNEP3063'
  begin
   set @DeptID=@OperDeptID
   set @SubmitButtonFlag='UnderDeptEveryDay'
  end

 

 if @SubmitButtonFlag='AllDept' or @SubmitButtonFlag='UnderDept'
  begin
   --部门表 #temp11
   select distinct DeptID,DeptName,DeptSequence
   into #temp11
   from Person_Main
   where @SubmitButtonFlag='AllDept' or @SubmitButtonFlag='UnderDept' and DeptID=@DeptID

   --每日签单 #temp12
   select case when Person_Main.pertypeid in('2701','2706') or Person_Main.DeptTypeID<>'3500' then '187'
     else TractBargain.DeptID 
     end as DeptID
    ,TractBargain.ID as TractID
    ,TractBargain.TractType
    ,TractBargain.StampFlag
    ,TractBargain.ServerPeriod
    ,TractBargain.TractMoney
    ,TractBargain.ImagesNum
    ,TractBargain.FileExt
    --,TractBargain.Tnum
    ,TractBargain.PerID
    ,Person_Main.PerName as PerName
    ,Person_Main.StateName
    ,Person_Main.PerTypeID
    ,Person_Main.Tnum
    ,DicAddress.Name+DicAddress2.Name as ProvinceCityName
    ,Customer.Name as CusName
    ,Customer.ID as CusID
   into #temp12
   from  jobcn_boss_sale.dbo.TractBargain TractBargain
    ,jobcn_boss_sale.dbo.Customer Customer
         ,jobcn_boss_sale.dbo.DicAddress DicAddress
         ,jobcn_boss_sale.dbo.DicAddress DicAddress2
    ,jobcn_boss_hr.dbo.PerDeptMonth Person_Main
   where TractBargain.CusID=Customer.ID
    and Customer.ProvinceID=DicAddress.ID
    and Customer.CityID=DicAddress2.ID
    and TractBargain.PerID=Person_main.PerID
    and Person_Main.YearNum=Year(@BeginDate)
    and Person_Main.MonthNum=Month(@BeginDate)
    and convert(varchar(10),TractBargain.TractDate,120)=@BeginDate --and datediff(d,TractBargain.TractDate,@BeginDate)=0 
    and (  
     @SubmitButtonFlag='AllDept'
     or @SubmitButtonFlag='UnderDept' and TractBargain.DeptID=@DeptID
         )
    
   --for xml auto   当天签单
   

   ----------------------------------------------------------------------------------------------
   --#DestTractNum 取各部门某天的签单目标单数
   select  DestDayPerson.YearNum
    ,DestDayPerson.MonthNum
    ,DestDayPerson.PerID
    ,DestDayPerson.DeptID
    ,case when day(@BeginDate)=1 then TractNum1
     when day(@BeginDate)=2 then TractNum2
     when day(@BeginDate)=3 then TractNum3
     when day(@BeginDate)=4 then TractNum4
     when day(@BeginDate)=5 then TractNum5
     when day(@BeginDate)=6 then TractNum6
     when day(@BeginDate)=7 then TractNum7
     when day(@BeginDate)=8 then TractNum8
     when day(@BeginDate)=9 then TractNum9
     when day(@BeginDate)=10 then TractNum10
     when day(@BeginDate)=11 then TractNum11
     when day(@BeginDate)=12 then TractNum12
     when day(@BeginDate)=13 then TractNum13
     when day(@BeginDate)=14 then TractNum14
     when day(@BeginDate)=15 then TractNum15
     when day(@BeginDate)=16 then TractNum16
     when day(@BeginDate)=17 then TractNum17
     when day(@BeginDate)=18 then TractNum18
     when day(@BeginDate)=19 then TractNum19
     when day(@BeginDate)=20 then TractNum20
     when day(@BeginDate)=21 then TractNum21
     when day(@BeginDate)=22 then TractNum22
     when day(@BeginDate)=23 then TractNum23
     when day(@BeginDate)=24 then TractNum24
     when day(@BeginDate)=25 then TractNum25
     when day(@BeginDate)=26 then TractNum26
     when day(@BeginDate)=27 then TractNum27
     when day(@BeginDate)=28 then TractNum28
     when day(@BeginDate)=29 then TractNum29
     when day(@BeginDate)=30 then TractNum30
     when day(@BeginDate)=31 then TractNum31
    end as DestTractNum
   into #DestTractNum
   from DestDayPerson
    ,DestMonthDept
   where DestMonthDept.YearNum=DestDayPerson.YearNum
    and DestMonthDept.MonthNum=DestDayPerson.MonthNum
    and DestMonthDept.managerPerID=DestDayPerson.PerID
    and DestMonthDept.YearNum=year(@BeginDate)
    and DestMonthDept.MonthNum=month(@BeginDate)
    and (  
     @SubmitButtonFlag='AllDept'
     or @SubmitButtonFlag='UnderDept' and DestMonthDept.DeptID=@DeptID
         )

   
   --#DeptTnumPst
   select DeptID
    ,sum(dbo.F_Tnum_Pst(Person_Main.DeptID,Person_Main.PerTypeID,datediff(m,comeindate,@BeginDate)+1) ) as DeptTnumPst --某月销售部门T数总目标
   into #DeptTnumPst
   from jobcn_boss_hr.dbo.PerDeptMonth Person_Main
   where YearNum=Year(@BeginDate)
    and MonthNum=Month(@BeginDate)
    and (StateName='在职'
     or datediff(m,MoveOutDate,@BeginDate)=0
    )
    and perTypeID='2700'
   group by DeptID

  
   --#DeptTractMoney
   select case when Person_Main.PerTypeID in ('2701','2706') or Person_Main.DeptTypeID <>'3500' then '187'
     else TractBargain.DeptID end as DeptID
   ,sum(convert(int,TractMoney)) as DeptTractMoney  ----某月销售部门签单金额
   into #DeptTractMoney
   from jobcn_boss_sale.dbo.TractBargain TractBargain
    ,jobcn_boss_hr.dbo.PerDeptMonth Person_Main
   where  TractBargain.PerID=Person_main.PerID
    and datediff(m,TractBargain.TractDate,@BeginDate)=0
    and Person_Main.YearNum=year(@BeginDate)
    and Person_Main.MonthNum=month(@BeginDate)
   group by case when Person_Main.PerTypeID in ('2701','2706') or Person_Main.DeptTypeID <>'3500' then '187'
     else TractBargain.DeptID end

   --#temp13    Person_Main.DeptID, Person_Main.name, Person_Main.DeptName
    

   select Person_Main.PerName as ManagerPerName
    ,Person_Main.PerID as ManagerPerID
    ,Person_Main.StateID
    ,Person_Main.StateName
    ,Person_Main.Extension
    ,Person_Main.DeptName
    ,Person_Main.DeptSequence
    ,SaleMonthDept.DeptID
    ,DestMonthPerson.TractDest as PMPT
    ,SaleMonthPerson.TractMoney as PMPA
    ,ISNULL(convert(int,#DeptTnumPst.DeptTnumPst),0)  as PSTT
    ,ISNULL(convert(int,#DeptTractMoney.DeptTractMoney),0) as PDTA
    ,ISNULL(convert(int,#DestTractNum.DestTractNum),0) as DestTractNum
    ,case when Person_Main.PerName='龚翃' then 0 else 1 end ManagerPerNamesequence
   into #temp13
   from SaleMonthDept
    ,SaleMonthPerson
    ,DestMonthPerson
    ,#DeptTnumPst
    ,#DeptTractMoney
    ,#DestTractNum
    ,jobcn_boss_hr.dbo.PerDeptMonth Person_Main
   where SaleMonthDept.ManagerPerID *= DestMonthPerson.PerID
    and SaleMonthDept.ManagerPerID *= SaleMonthPerson.PerID
    and SaleMonthDept.DeptID *= #DeptTnumPst.DeptID
    and SaleMonthDept.DeptID *= #DeptTractMoney.DeptID
    and SaleMonthDept.managerPerID=#DestTractNum.PerID
    and SaleMonthDept.managerPerID*=Person_Main.PerID
    and SaleMonthDept.YearNum=year(@BeginDate)
    and SaleMonthDept.MonthNum=month(@BeginDate)
    and DestMonthPerson.YearNum=year(@BeginDate)
    and DestMonthPerson.MonthNum=month(@BeginDate)
    and SaleMonthPerson.YearNum=year(@BeginDate)
    and SaleMonthPerson.MonthNum=month(@BeginDate)
    and Person_Main.yearNum=year(@BeginDate)
    and Person_Main.monthNum=month(@BeginDate)
    and ( 
     @SubmitButtonFlag='AllDept'
     or @SubmitButtonFlag='UnderDept' and SaleMonthDept.DeptID=@DeptID
         )
   
   -------------------------------------------------------------------------------------------
   --#CurrentDay 当天签单数据
   select SaleDayDept.DeptID
    ,isnull(PerID_CurrentDay.PerID_CurrentDay,0) as PMPA_CurrentDay  --个人当天签单金额
    ,isnull(DeptID_CurrentDay.DeptID_CurrentDay,0) AS PDTA_CurrentDay --部门当天签单金额
    ,isnull(DeptID_CurrentDayTractNum.DeptID_CurrentDayTractNum,0) AS TractNum  --部门当天签单单数
   into #CurrentDay
   from SaleDayDept
    ,(
     select  PerID
      ,sum(convert(int,TractMoney)) as PerID_CurrentDay 
     from #temp12
     group by PerID
    ) PerID_CurrentDay  
    ,(
     select  DeptID
      ,sum(convert(int,TractMoney)) as DeptID_CurrentDay 
     from #temp12
     group by DeptID
    ) DeptID_CurrentDay
    ,(
     select  DeptID
      ,count(*) as DeptID_CurrentDayTractNum 
     from #temp12
     where convert(int,TractMoney)>=3600
     group by DeptID
    ) DeptID_CurrentDayTractNum
   where   SaleDayDept.ManagerPerID *=PerID_CurrentDay.PerID
    and SaleDayDept.DeptID*=DeptID_CurrentDay.DeptID
    and SaleDayDept.DeptID*=DeptID_CurrentDayTractNum.DeptID

    and SaleDayDept.YearNum=year(@BeginDate)
    and SaleDayDept.MonthNum=month(@BeginDate)
   

   --#PerContTable 各部门的人员数
   select count(PerID) as PerCount, DeptID
   into #PerContTable
   from jobcn_boss_hr.dbo.PerDeptMonth Person_Main
   where YearNum=Year(@BeginDate)
    and monthNum=Month(@BeginDate)
    and perTypeID='2700'
    and StateName='在职'
   group by DeptID
   
   --查出未到账
   select
     t1.DeptID,
     sum(convert(int,t1.GatherMoney)) as GatherMoney,
     sum(convert(int,t1.TractDiff)) as TractDiff,
     sum(convert(int,t1.TractMoney)) as TractMoney
   into #table2
   from
    jobcn_boss_sale.dbo.TractBargain t1,Person_Main t2
   
   where
    t1.DeptID=T2.DeptID
    and t2.PerTypeID='2701'
    and  t2.StateID='1701'
    and Month(t1.TractDate)=Month(@BeginDate)
    and Year(t1.TractDate)=Year(@BeginDate)
   group by
    T1.DeptID,DeptName

   select distinct  #temp13.DeptName
    ,case when len(#temp13.ManagerPerName)=2 then Left(#temp13.ManagerPerName,1)+'　'+Right(#temp13.ManagerPerName,1) 
     else #temp13.ManagerPerName
     end as ManagerPerName
    ,#temp13.ManagerPerNameSequence
    ,#temp13.ManagerPerID
    ,#temp13.StateName
    ,#temp13.Extension
    ,#temp13.DeptID

    ,#temp13.PSTT+ #temp13.PMPT as PDTT --部门目标***
    ,#temp13.PSTT  --部门目标***
    ,#temp13.PMPT  --经理目标
    ,#temp13.PDTA  --部门业绩
    ,#temp13.PDTA as PSTA --部门业绩***
    ,#temp13.PMPA  --经理业绩
    ,#temp13.DestTractNum --目标签单数***
    ,#temp13.DeptSequence
    
    ,#CurrentDay.PMPA_CurrentDay --个人当天签单金额
    ,#CurrentDay.PDTA_CurrentDay --部门当天签单金额
    ,#CurrentDay.PDTA_CurrentDay as PSTA_CurrentDay  --部门当天签单金额***
    ,#CurrentDay.TractNum  --签单数***
    
    ,case when (#temp13.PSTT+#temp13.PMPT)=0 then 0.00 else ISNULL( #temp13.PDTA,0)/(#temp13.PSTT+#temp13.PMPT+0.0001)  end as PDTTPercent  --部门达成率***
    ,case when (#temp13.PSTT)=0 then 0.00 else ISNULL(#temp13.PDTA,0)/(#temp13.PSTT+0.0001)  end as PSTTPercent --部门达成率***
    ,dbo.F_SaleAchieve_Level(100,#temp13.DeptID,ISNULL(#temp13.PDTA,0),ISNULL(#temp13.PSTT+#temp13.PMPT,0)) as SaleAchieve_Level --****
    ,ISNULL(convert(int,#PerContTable.PerCount),0) as PerCount  --部门人数***
    
   into #temp4--------------------------------------------------------------------------------------------------------------------------------------------------
   from #temp13 left join #CurrentDay on #temp13.DeptID=#CurrentDay.DeptID
    left join #PerContTable on  #temp13.DeptID = #PerContTable.DeptID
    
   where #PerContTable.PerCount>0 or #temp13.DeptID='187'
   --select * from #temp4

   --for xml r
   select * from #temp4 order by DeptSequence,PDTTPercent desc for xml raw

 

   /***************************** 每个管控人员分出一个独立的表格 *********************************/

   --#TeamPercent管控人员全部的达标率
   select ManagerPerName
   ,case when sum(PSTT)=0 then 0.00
    else ISNULL(sum(PSTA),0) / (sum(PSTT)+0.0001)  --部门业绩/部门目标
    end as TeamPercent
   into #TeamPercent
   from #temp4
   group by ManagerPerName
   --select * from #TeamPercent
   --#temp44

   select #TeamPercent.TeamPercent
    ,#temp4.*
   into #temp44
   from #temp4 left join #TeamPercent on #temp4.ManagerPerName=#TeamPercent.ManagerPerName
   --select * from #temp44

   select #table2.GatherMoney,#table2.TractMoney,#table2.TractDiff
    ,#temp44.*
   into #temp444
   from #temp44,#table2
   where #temp44.DeptID = #table2.DeptID
   
   

   select #temp444.*
   into #temp4444
   from #temp444 left join #TeamPercent on #temp444.ManagerPerName=#TeamPercent.ManagerPerName
  
   

   -- for xml auto 各管控人员业绩进度
   select  table3.ManagerPerName
    ,table3.StateName
    ,table3.Extension
    ,table4.GatherMoney
    ,table4.TractMoney
    ,table4.TractDiff 
    ,case when (table4.TractMoney)=0 then 0.00 else ISNULL(table4.GatherMoney,0)/(table4.TractMoney+0.0001) end as DzPercent 
    ,table4.DeptID
    ,table4.DeptName
    ,table4.PerCount
    ,table4.PDTT
    ,table4.PSTT
    ,table4.PMPT
    ,table4.PDTA
    ,table4.PSTA
    ,table4.PMPA
    ,table4.DestTractNum
    ,table4.PMPA_CurrentDay
    ,table4.PDTA_CurrentDay
    ,table4.PSTA_CurrentDay
    ,table4.TractNum
    ,table4.PDTTPercent
    ,table4.PSTTPercent
    ,table4.SaleAchieve_Level
 
   --into #temp7
   from #temp44 table3
    ,#temp4444 table4
   where table3.ManagerPerID = table4.ManagerPerID
    and table3.DeptID = table4.DeptID
    and table4.DeptID in (select DeptID from jobcn_boss_hr.dbo.PerDeptPart  where  PerID = 'JCNEP4625') 
    and  table3.ManagerPerName<>'常到帐'
   order by
     table3.TeamPercent desc
    ,table3.ManagerPerName asc
    ,table4.PSTTPercent desc
    ,table4.DeptSequence asc
    
   --select * from #temp7
   for xml auto
   
  
  
   

   --for xml auto
   select top 1 left(jobcn_boss_statistic.dbo.F_ConvertDateToChinese(@BeginDate,'partYMD'),8) as YearMonthNum
     ,jobcn_boss_statistic.dbo.F_ConvertDateToChinese(@BeginDate,'partYMD') as YearMonthDayNum
   from  #temp11  YearMonthDay
   for xml auto
   

 

   drop table #CurrentDay, #temp11, #temp12, #DestTractNum, #temp13

   drop table #temp4, #TeamPercent, #temp44

---------------------------------------------------------------------------------每个部门的情况-------------------------------------------------------------------------------------------------------
  --初始化自定义变量
  set @ExecBeginDate=getdate()
   set @ProcName ='ReF_TractOrderControl'
   set @ExecStr='exec '+@ProcName +''''+@OperName+''','''+@OperID+''','''+@OperIP+''','''+@PageName+''''
   
  if @DeptID=''
   
   Begin
     select @ManagerPerName = PerName+'('+ Extension +')'
     from jobcn_boss_hr.dbo.PerDeptMonth Person_Main
     where PerID=@OperID
      and StateName='在职'
      and DeptID='209'
      and YearNum=Year(@BeginDate)
      and MonthNum=Month(@BeginDate)
      
     -- select top 1 DeptName from person_main where deptid='35'
     select top 1 @DeptNameSelect=DeptName from person_main where deptid=@DeptID
     
     --for xml auto
     select  top 1
      '统计时间：'+jobcn_boss_statistic.dbo.F_ConvertDateToChinese(convert(varchar(20),getdate(),120),'partAll')  as ReportStatisticDate
      ,'管控组员签单业绩及进度'  as TableTitle
      ,'管控组员中心数据' as XslTitle
      ,@DeptNameSelect as TableTitle1
      ,jobcn_boss_statistic.dbo.F_ConvertDateToChinese(@BeginDate,'partYM')  as ReportTableDate
     from Person_Main table0
     for xml auto
    
    
     --某月M1,PP1,PP2
     select @M1=M1Num,@PP1=PP1Num, @PP2=PP2Num
     from PP1MonthPerson
     where YearNum=year(@BeginDate)
      and MonthNum=month(@BeginDate)
     if @@rowcount<=0
      begin
       print '查询月份参数不存在！'
       return
      end
    
    
     --#temp1 座位号
     select case when left(seat.seatid,2)=10 then 'A' else 'B' end+'-' +replace(dicseat.name,'区','')+'-'+seat.name as SeatName
      ,seat.PerID
      ,seat.PerName 
     into #temp1
     from jobcn_boss_oa.dbo.Seat  Seat
      ,jobcn_boss_oa.dbo.dicseat dicseat    
     where seat.seatid=dicseat.id
    
     --#temp2
     select DeptID,Person.Name
     into #temp2
     from jobcn_boss_hr.dbo.Person Person
     where  (StateID='1701' or  StateID='1702')
      and Person.PertypeID='2701'
      and Person.SaleType='3308'
      and Person.DeptID<>'187'
      and Person.Name=@OperName
     
    
    
     --取某天的销售签单 #TractBargain
     select  TractBargain.TractMoney
      ,TractBargain.PerID
     into #TractBargain
     from jobcn_boss_sale.dbo.TractBargain TractBargain
      ,jobcn_boss_hr.dbo.PerDeptMonth Person_Main
     where TractBargain.PerID=Person_main.PerID
      and datediff(d,TractBargain.TractDate,@BeginDate)=0
      and Person_Main.YearNum=year(@BeginDate)
      and Person_Main.MonthNum=month(@BeginDate)
    
     select Person_Main.PerID
      ,Person_Main.Pername
      ,Person_Main.Extension
      ,Seat.SeatName
      ,Person_Main.Tnum
      ,SaleMonthPerson.DeptName
      ,dbo.F_Tnum_Pst(Person_Main.DeptID,Person_Main.PerTypeID,Person_Main.Tnum)*1.0*@PP1*@M1 as DestPerTractMoney   --个人奖励目标业绩
      ,SaleMonthPerson.TractMoney  --本月签单
      ,case when (SaleMonthPerson.GatherMoney > SaleMonthPerson.TractMoney ) then SaleMonthPerson.TractMoney 
       else  SaleMonthPerson.GatherMoney end as GatherMoney  --本月到账

      ,case when (SaleMonthPerson.GatherMoney < SaleMonthPerson.TractMoney )
       then  ISNULL(SaleMonthPerson.GatherMoney,0)/(SaleMonthPerson.TractMoney+0.0001)
       else ISNULL(SaleMonthPerson.TractMoney,0)/(SaleMonthPerson.GatherMoney+0.0001)  end as DzPercent   --到账达成率
      
      

      ,isnull(PerID_CurrentDayTractNum.PerID_CurrentDayTractNum,0) AS TractNum  --个人标单数
      ,isnull(PerID_CurrentDay.PerID_CurrentDay,0) as PerID_CurrentDay   --个人当天签单
      , case when (dbo.F_Tnum_Pst(Person_Main.DeptID,Person_Main.PerTypeID,Person_Main.Tnum)*1.0*@PP1*@M1)=0 then 0.00
       else ISNULL(SaleMonthPerson.TractMoney,0)/(dbo.F_Tnum_Pst(Person_Main.DeptID,Person_Main.PerTypeID,Person_Main.Tnum)*1.0*@PP1*@M1) 
       end as TractPercent --签单达成率
      ,dbo.F_SaleAchieve_Level(100, Person_Main.DeptID
       ,ISNULL( SaleMonthPerson.TractMoney,0)
       ,ISNULL( (dbo.F_Tnum_Pst(Person_Main.DeptID,Person_Main.PerTypeID,Person_Main.Tnum)*1.0*@PP1*@M1),0))
        as SaleAchieve_Level
     into #table9
     from jobcn_boss_hr.dbo.PerDeptMonth Person_Main 
      ,#temp1 Seat
      ,SaleMonthPerson
      ,(select PerID,sum(convert(int,TractMoney)) as PerID_CurrentDay  from #TractBargain group by PerID) PerID_CurrentDay 
      ,(select PerID,count(*) as PerID_CurrentDayTractNum  from #TractBargain where convert(int,TractMoney)>=3600 group by PerID) PerID_CurrentDayTractNum
     where Person_Main.PerID *= PerID_CurrentDay.PerID
      and Person_Main.PerID *= Seat.PerID
      and Person_Main.PerID *= PerID_CurrentDayTractNum.PerID
      and Person_Main.PerID = SaleMonthPerson.PerID
      and SaleMonthPerson.YearNum=year(@BeginDate)
      and SaleMonthPerson.MonthNum=month(@BeginDate)
      and Person_Main.DeptTypeID=3500
      and Person_Main.SaleType<>'3308'
      and Person_Main.YearNum=year(@BeginDate)
      and Person_Main.MonthNum=month(@BeginDate)
      

     select *  from  #table9
      order by
      
      subString(DeptName,3,2)  asc,
       case
        when @OrderBy='tnum' then Tnum    --T数年
        when @OrderBy='dzpercent' then DzPercent  --到账率 
        else TractPercent   --签单率

        
       end
     desc
      -- for xml auto 

 

     --利用循环将不同的部门分开成独立的表
     select IDENTITY(INT,1,1) as seq, DeptName into #table10  from #table9 group by DeptName

     declare @deptname varchar(20),@num int

     select @deptname=deptname from #table10 where seq = 1

     while exists(select * from #table10)
      begin

       select * from #table9 table9 where DeptName =@deptname for xml auto

       select @num = min(seq) from #table10
       delete from #table10 where seq = @num

       select @num = min(seq) from #table10
       select @deptname = deptname from #table10 where seq = @num
      end
       

    
     drop table #temp1,#temp2,#TractBargain,#table9,#table10

 

     
     -- drop  table #table11

     --  exec MyReport '2010-03-22','','','','AllDept','王小刚', 'JCNEP4699','127.0.0.1','/reportnew/mcm/mcm_index.jsp'
     --  exec MyReport '2010-10-22','','','','AllDept','王小刚', 'JCNEP4699','127.0.0.1','/reportnew/mcm/mcm_index.jsp'

    
  end

 
 end 
  

GO

 

--    select * from jobcn_boss_hr.dbo.PerDeptPart
--    select DeptID,DeptName  from Person_Main where DeptID in (select DeptID from jobcn_boss_hr.dbo.PerDeptPart  where  PerID = 'JCNEP4625') order by DeptID
---