给移动公司做的一个查询的存储过程

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER                                                    PROCEDURE [dbo].[wlwxxntj]
@riqi_start datetime,
@riqi_end datetime,
@sjd_start varchar(10),
@sjd_end varchar(10),
@BSS varchar(20),
@Site varchar(20),
@Cell varchar(5),
@orderby_key varchar(30),
@pxfs varchar(10),
@ysf varchar(6),
@ysf_z varchar(10),
@top_z varchar(10)
AS
begin
--定义变量
declare @sql varchar(8000),@sql2 varchar(8000),@sqlwhere varchar(4000),@sqlorderby varchar(8000)
declare @GSR7_5XDHL varchar(5000)     --GSR7五项掉话率
declare @GSR7_5XDHL_HJ varchar(5000)  --GSR7五项掉话率_合计
declare @GSR7_5XDHZJ VARCHAR(2000)    --GSR7五项掉话总计
declare @GSR7_5XDHZJ_HJ VARCHAR(2000) --GSR7五项掉话总计_合计
declare @SDCCH_HJCSCS varchar(2000)   --SDCCH呼叫尝试次数
declare @SDCCH_JRSBL varchar(2000)    --SDCCH接入失败率
declare @SDCCH_JRSBL_HJ varchar(2000) --SDCCH接入失败率_合计
declare @SDCCH_YSCS varchar(2000)     --SDCCH拥塞次数
declare @SDCCH_YSL varchar(2000)      --SDCCH拥塞率
declare @SDCCH_YSL_HJ varchar(2000)   --SDCCH拥塞率_合计
declare @TCH_SHCS_BHQH varchar(2000)  --TCH试呼次数bhqh
declare @TCH_SHCS_HQH varchar(2000)   --TCH试呼次数hqh
declare @HWL varchar(2000)            --话务量
declare @QHCGL varchar(2000)          --切换成功率
declare @QHCGL_HJ varchar(3000)       --切换成功率_合计
declare @QHSBL varchar(2000)          --切换失败率
declare @QHSBL_HJ varchar(2000)       --切换失败率_合计
declare @4XDHL_PLMN varchar(2000)     --四项掉话率plmn
declare @4XDHL_PLMN_HJ varchar(2000)  --四项掉话率plmn_合计
declare @4XDHL_XQJ varchar(2000)      --四项掉话率XQJ
declare @4XDHL_XQJ_HJ varchar(2000)   --四项掉话率XQJ_合计
declare @WXJTL varchar(2000)          --无线接通率
declare @WXJTL_HJ varchar(2000)       --无线接通率_合计
declare @ZTHCS varchar(2000)          --总通话次数
declare @TCH_YSL_BHQH varchar(2000)   --TCH拥塞率bhqh
declare @TCH_YSL_BHQH_HJ varchar(2000)--TCH拥塞率bhqh_合计
declare @TCH_YSL_HQH varchar(2000)    --TCH拥塞率hqh
declare @TCH_YSL_HQH_HJ varchar(2000) --TCH拥塞率hqh_合计
declare @TCH_YSCS_BHQH varchar(2000)  --TCH拥塞次数bhqh
declare @TCH_YSCS_HQH  varchar(2000)  --TCH拥塞次数hqh
declare @ok_acc_proc_suc_r varchar(1000) --
declare @ok_acc_proc_suc_r_HJ varchar(2000) -- xxx合计
--设置变量值
set @sql=''
set @sql2=''
set @sqlwhere=''
set @sqlorderby=''

set @GSR7_5XDHL=
'(case when RF_LOSS_TCH_ROLL="?" or INTRA_CELL_HO_LOS="?" or O_INTRA_BS_HO_LOS="?" or O_INTER_BS_HO_FAIL="?" then "0.00%"
   when TOTAL_CALLS=0 or TOTAL_CALLS="?" then "0.00%"
      when RF_LOSS_TCH_ROLL="0" and INTRA_CELL_HO_LOS="0" and O_INTRA_BS_HO_LOS="0" and  O_INTER_BS_HO_FAIL="0" then "0.00%"
 else
rtrim(CONVERT(char(8),
cast(  (cast(cast(RF_LOSS_TCH_ROLL as int)+cast(INTRA_CELL_HO_LOS as int)+cast(O_INTRA_BS_HO_LOS as int)+cast(O_INTER_BS_HO_FAIL as int) as decimal(8,2))/cast(cast(TOTAL_CALLS as int) as decimal(8,2))*100) as decimal(10,2))
 ) )+"%"
end
)'--GSR7五项掉话率
-----------------------
set @GSR7_5XDHL_HJ=
'rtrim(CONVERT(char(8),
cast(
cast(sum(case when RF_LOSS_TCH_ROLL=''?'' then 0 else RF_LOSS_TCH_ROLL end)+ sum(case when INTRA_CELL_HO_LOS=''?'' then 0 else INTRA_CELL_HO_LOS end)+
 sum(case when O_INTRA_BS_HO_LOS=''?'' then 0 else O_INTRA_BS_HO_LOS end)+ sum(case when O_INTER_BS_HO_FAIL=''?'' then 0 else O_INTER_BS_HO_FAIL end) as decimal(16,2))/
 cast(sum(case when TOTAL_CALLS=''?'' then 0 else TOTAL_CALLS end) as decimal(16,2))
 as decimal(10,2))*100
))+''%'''
-------------------------
set @GSR7_5XDHZJ='(case when RF_LOSS_TCH_ROLL="?" then cast(0 as int)
 else
(cast(RF_LOSS_TCH_ROLL as int)+cast(INTRA_CELL_HO_LOS as int)+cast(O_INTRA_BS_HO_LOS as int)+cast(O_INTER_BS_HO_FAIL as int))
end)'--GSR7五项掉话总计
-------------------------------------------------------
/*set @GSR7_5XDHZJ_HJ='(case when RF_LOSS_TCH_ROLL="?" then 0
 else
(cast(RF_LOSS_TCH_ROLL as int)+cast(INTRA_CELL_HO_LOS as int)+cast(O_INTRA_BS_HO_LOS as int)+cast(O_INTER_BS_HO_FAIL as int))
end)'--GSR7五项掉话总计_合计*/
-------------------------------------------
set @SDCCH_HJCSCS=
'(case when ALLOC_SDCCH_FAIL=''?'' then 0
 else
(cast(ALLOC_SDCCH as int)+cast(ALLOC_SDCCH_FAIL as int))
end)'  --SDCCH呼叫尝试次数
----------------------------------------------
set @SDCCH_JRSBL=
'(case when chan_req_fail_rol="?" or chan_req_caus_atm="?" then 0.00
  when chan_req_caus_atm=0 or chan_req_caus_atm="?" then 0.00
else

cast( (cast(cast(chan_req_fail_rol as int) as decimal(8,2))/cast((cast(chan_req_caus_atm as int)) as decimal(8,2))*100) as decimal(10,2))

end
)'--SDCCH接入失败率
----------------------------------------------------
set @SDCCH_JRSBL_HJ=
'rtrim(CONVERT(char(8),
cast(
cast(sum(case when chan_req_fail_rol=''?'' then 0 else chan_req_fail_rol end) as decimal(16,2))/
 cast(sum(case when chan_req_caus_atm=''?'' then 0 else chan_req_caus_atm end) as decimal(16,2))*100
 as decimal(16,2))
))'
-------------------------------------------------
set @SDCCH_YSCS=
'(case when ALLOC_SDCCH_FAIL="?" then 0
  else ALLOC_SDCCH_FAIL
end)'--SDCCH拥塞次数
-----------------------------------------------
set @SDCCH_YSL=
'(case when ALLOC_SDCCH=''?'' or (ALLOC_SDCCH=''0'' and ALLOC_SDCCH_FAIL=''0'') then ''0.00%''
else
rtrim(CONVERT(char(8),
cast(  (cast(cast(ALLOC_SDCCH_FAIL as int) as decimal(8,2))/cast((cast(ALLOC_SDCCH as int)+cast(ALLOC_SDCCH_FAIL as int)) as decimal(8,2))*100) as decimal(10,2))
 ) )+''%''
end)'-- as SDCCH拥塞率
-------------------------------------------
set @SDCCH_YSL_HJ=
'rtrim(CONVERT(char(8),cast(
 cast(sum(case when ALLOC_SDCCH_FAIL=''?'' then 0 else ALLOC_SDCCH_FAIL end) as decimal(16,2))/
 cast(sum(case when ALLOC_SDCCH=''?'' then 0 else ALLOC_SDCCH end)+sum(case when ALLOC_SDCCH_FAIL=''?'' then 0 else ALLOC_SDCCH_FAIL end) as decimal(16,2))*100
 as decimal(16,2))
 ) )+"%"'
-------------------------------------------
set @TCH_SHCS_BHQH=
'(case when MA_REQ_FROM_MSC="?" then 0
  else MA_REQ_FROM_MSC
  end)'
-----------------------------------------------
set @TCH_SHCS_HQH=
'(case when ALLOC_TCH_FAIL=''?'' then 0
 else
(cast(ALLOC_TCH as int)+cast(ALLOC_TCH_FAIL as int))
end)'-- as TCH试呼次数hqh
-----------------------------------------------
set @HWL=
'(case when BUSY_TCH_MEAN=''?'' then ''0''
  else BUSY_TCH_MEAN
  end)'
-------------------------------------------------
set @QHCGL=
'(case when intra_cell_ho_suc=''?'' or o_intra_bs_ho_suc=''?'' or o_inter_bs_ho_suc=''?'' then ''0''
      when (o_intra_bs_ho_atm=''0'' and o_inter_bs_ho_atm=''0'' and intra_cell_ho_f_f=''0'') then ''0''
 else
rtrim(CONVERT(char(8),
cast(  (cast(cast(intra_cell_ho_suc as int)+cast(o_intra_bs_ho_suc as int)+cast(o_inter_bs_ho_suc as int) as decimal(16,2))/cast((cast(o_intra_bs_ho_atm as int)+cast(o_inter_bs_ho_atm as int)+cast(intra_cell_ho_f_f as int)) as decimal(16,2))*100) as decimal(16,2))
 ) )+''%''
end
)'-- as 切换成功率
----------------------------------------------
set @QHCGL_HJ=
'rtrim(CONVERT(char(8),cast(
cast(sum(case when intra_cell_ho_suc=''?'' then 0 else intra_cell_ho_suc end)+sum(case when o_intra_bs_ho_suc=''?'' then 0 else o_intra_bs_ho_suc end)+sum(case when o_inter_bs_ho_suc=''?'' then 0 else o_inter_bs_ho_suc end) as decimal(16,2))/
 cast(sum(case when o_intra_bs_ho_atm=''?'' then 0 else o_intra_bs_ho_atm end)+sum(case when o_inter_bs_ho_atm=''?'' then 0 else o_inter_bs_ho_atm end)+sum(case when intra_cell_ho_f_f=''?'' then 0 else intra_cell_ho_f_f end) as decimal(16,2))*100
 as decimal(16,2))
))+"%"'
-----------------------------------------------
set @QHSBL=
'(case when o_intra_bs_ho_atm=''?'' or o_inter_bs_rq_msc=''?'' or o_inter_bs_ho_suc=''?'' or o_intra_bs_ho_suc=''?''  then ''0''
      when (o_intra_bs_ho_atm=''0'' and o_inter_bs_rq_msc=''0'') then ''0''
 else
rtrim(CONVERT(char(8),
cast(  (cast(cast(o_intra_bs_ho_atm as int)+cast(o_inter_bs_rq_msc as int)-cast(o_inter_bs_ho_suc as int)-cast(o_intra_bs_ho_suc as int) as decimal(10,2))/cast((cast(o_intra_bs_ho_atm as int)+cast(o_inter_bs_rq_msc as int)) as decimal(10,2))*100) as decimal(10,2))
 ) )+''%''
end
)'-- as 切换失败率
------------------------------------------------
set @QHSBL_HJ=
'rtrim(CONVERT(char(8),cast(
 cast(sum(case when o_intra_bs_ho_atm=''?'' then 0 else o_intra_bs_ho_atm end)+sum(case when o_inter_bs_rq_msc=''?'' then 0 else o_inter_bs_rq_msc end)-sum(case when o_inter_bs_ho_suc=''?'' then 0 else o_inter_bs_ho_suc end)-sum(case when o_intra_bs_ho_suc=''?'' then 0 else o_intra_bs_ho_suc end) as decimal(16,2))/
 cast(sum(case when o_intra_bs_ho_atm=''?'' then 0 else o_intra_bs_ho_atm end)+sum(case when o_inter_bs_rq_msc=''?'' then 0 else o_inter_bs_rq_msc end) as decimal(16,2))*100
 as decimal(16,2))
))+"%"'
-----------------------------------------------
set @4XDHL_PLMN=
'(case when rf_loss_tch_roll=''?'' or intra_cell_ho_los=''?'' or o_intra_bs_ho_los=''?'' or o_inter_bs_ho_clr=''?'' or TOTAL_CALLS=''0'' then ''0''
else
rtrim(CONVERT(char(8),
cast(  (cast(cast(rf_loss_tch_roll as int)+cast(intra_cell_ho_los as int)+cast(o_intra_bs_ho_los as int)+cast(o_inter_bs_ho_clr as int) as decimal(8,2))/cast((cast(TOTAL_CALLS as int)) as decimal(8,2))*100) as decimal(10,2))
 ) )+''%''
end
)'-- as 四项掉话率plmn
-----------------------------------------------
set @4XDHL_PLMN_HJ=
'rtrim(CONVERT(char(8),cast(
 cast(sum(case when rf_loss_tch_roll=''?'' then 0 else rf_loss_tch_roll end)+sum(case when intra_cell_ho_los=''?'' then 0 else intra_cell_ho_los end)+sum(case when o_intra_bs_ho_los=''?'' then 0 else o_intra_bs_ho_los end)+sum(case when o_inter_bs_ho_clr=''?'' then 0 else o_inter_bs_ho_clr end) as decimal(16,2))/
 cast(sum(case when TOTAL_CALLS=''?'' then 0 else TOTAL_CALLS end) as decimal(16,2))*100
 as decimal(16,2))
))+"%"'
------------------------------------------------
set @4XDHL_XQJ=
'(case when rf_loss_tch_roll=''?'' or intra_cell_ho_los=''?'' or o_intra_bs_ho_los=''?'' or o_inter_bs_ho_clr=''?'' or TOTAL_CALLS=''0'' then ''0''
else
rtrim(CONVERT(char(8),
cast(  (cast(cast(rf_loss_tch_roll as int)+cast(intra_cell_ho_los as int)+cast(o_intra_bs_ho_los as int)+cast(o_inter_bs_ho_clr as int) as decimal(8,2))/cast((cast(TOTAL_CALLS as int)+cast(i_inter_bs_ho_suc as int)+cast(i_intra_bs_ho_suc as int)) as decimal(8,2))*100) as decimal(10,2))
 ) )+''%''
end
)'-- as 四项掉话率xqj
-----------------------------------------------
set @4XDHL_XQJ_HJ=
'rtrim(CONVERT(char(8),cast(
 cast(sum(case when rf_loss_tch_roll=''?'' then 0 else rf_loss_tch_roll end)+sum(case when intra_cell_ho_los=''?'' then 0 else intra_cell_ho_los end)+sum(case when o_intra_bs_ho_los=''?'' then 0 else o_intra_bs_ho_los end)+sum(case when o_inter_bs_ho_clr=''?'' then 0 else o_inter_bs_ho_clr end) as decimal(16,2))/
 cast(sum(case when TOTAL_CALLS=''?'' then 0 else TOTAL_CALLS end)+sum(case when i_inter_bs_ho_suc=''?'' then 0 else i_inter_bs_ho_suc end)+sum(case when i_intra_bs_ho_suc=''?'' then 0 else i_intra_bs_ho_suc end) as decimal(16,2))*100
 as decimal(16,2))
))+"%"'
-------------------------------------------------
set @WXJTL=
'(case when MA_CMD_TO_MS_BLKD=''?'' or MA_REQ_FROM_MSC=''?'' then ''0.00%''
      when MA_REQ_FROM_MSC=''0'' or (ALLOC_SDCCH=''0'' and ALLOC_SDCCH_FAIL=''0'') then ''0.00%''
 else
rtrim(CONVERT(char(8),
   cast( 
       (1-(cast(cast(MA_CMD_TO_MS_BLKD as int) as decimal(8,2))
       /cast(cast(MA_REQ_FROM_MSC as int) as decimal(8,2))))
       *
       (1-  (cast(cast(ALLOC_SDCCH_FAIL as int) as decimal(8,2))
        /cast(cast(ALLOC_SDCCH as int)+cast(ALLOC_SDCCH_FAIL as int) as decimal(8,2)))
        )
   as decimal(10,2))
    )
)+''%''
end
)'-- as 无线接通率
-----------------------------------------------
SET @WXJTL_HJ=
'rtrim(CONVERT(char(8),cast(
cast(1-cast(sum(case when MA_CMD_TO_MS_BLKD=''?'' then 0 else MA_CMD_TO_MS_BLKD end) as decimal(16,6))/
cast(sum(case when MA_REQ_FROM_MSC=''?'' then 0 else MA_REQ_FROM_MSC end) as decimal(16,2)) as decimal(16,6))
*
cast(1-cast(sum(case when ALLOC_SDCCH_FAIL=''?'' then 0 else ALLOC_SDCCH_FAIL end) as decimal(16,6))/
cast(sum(case when ALLOC_SDCCH=''?'' then 0 else ALLOC_SDCCH end)+sum(case when ALLOC_SDCCH_FAIL=''?'' then 0 else ALLOC_SDCCH_FAIL end) as decimal(16,6)) as decimal(16,6))
as decimal(8,2))
) )+''%'' '
---------------------------------------------
set @ZTHCS=
'(case when TOTAL_CALLS="?" then 0
  else TOTAL_CALLS
  end)'
-----------------------------------------------
set @TCH_YSL_BHQH=
'(case when MA_CMD_TO_MS_BLKD=''?'' or MA_REQ_FROM_MSC=''?'' or MA_REQ_FROM_MSC=''0'' then ''0''
else
rtrim(CONVERT(char(8),
cast(  (cast(cast(MA_CMD_TO_MS_BLKD as int) as decimal(8,2))/cast(cast(MA_REQ_FROM_MSC as int) as decimal(8,2))*100) as decimal(10,2))
 ) )+''%''
end
)'-- as TCH拥塞率bhqh
------------------------------------------------
set @TCH_YSL_BHQH_HJ=
'rtrim(CONVERT(char(8),cast(
 cast(sum(case when MA_CMD_TO_MS_BLKD=''?'' then 0 else MA_CMD_TO_MS_BLKD end) as decimal(16,2))/
 cast(sum(case when MA_REQ_FROM_MSC=''?'' then 0 else MA_REQ_FROM_MSC end) as decimal(16,2))*100
 as decimal(16,2))
 ) )+"%"'
-----------------------------------------------
set @TCH_YSL_HQH=
'(case when ALLOC_TCH=''?'' or (ALLOC_TCH=''0'' and ALLOC_TCH_FAIL=''0'') then ''0''
else
rtrim(CONVERT(char(8),
cast(  (cast(cast(ALLOC_TCH_FAIL as int) as decimal(8,2))/cast((cast(ALLOC_TCH as int)+cast(ALLOC_TCH_FAIL as int)) as decimal(8,2))*100) as decimal(10,2))
 ) )+''%''
end
)'-- as TCH拥塞率hqh
-----------------------------------------------
set @TCH_YSL_HQH_HJ=
'rtrim(CONVERT(char(8),cast(
 cast(sum(case when ALLOC_TCH_FAIL=''?'' then 0 else ALLOC_TCH_FAIL end) as decimal(16,2))/
 cast(sum(case when ALLOC_TCH=''?'' then 0 else ALLOC_TCH end) as decimal(16,2))*100
 as decimal(16,2))
 ) )+"%"'
-----------------------------------------------
set @TCH_YSCS_BHQH=
'(case when MA_CMD_TO_MS_BLKD="?" then 0
  else MA_CMD_TO_MS_BLKD
  end)'
-----------------------------------------------
set @TCH_YSCS_HQH=
'(case when ALLOC_TCH_FAIL=''?'' then 0
  else ALLOC_TCH_FAIL
  end)'
---------------------------------------------
set @ok_acc_proc_suc_r=
'(case when ok_acc_proc_suc_r="?" then 0
  else ok_acc_proc_suc_r
  end)'
--------------------------------------------
set @ok_acc_proc_suc_r_HJ=
'sum(case when @ok_acc_proc_suc_r_HJ=''?'' then 0 else @ok_acc_proc_suc_r_HJ end)'
---------------------------------------------
set @sqlwhere=' where '
if @riqi_start=@riqi_end  set @sqlwhere=@sqlwhere+' riqi='''+cast(@riqi_start as varchar(20))+''' ' else set @sqlwhere=@sqlwhere+' riqi>='''+cast(@riqi_start as varchar(20))+''' and riqi<='''+cast(@riqi_end as varchar(20))+''' '
set @sqlwhere=@sqlwhere+' and [Time]>='+@sjd_start+' and [Time]<='+@sjd_end+' '
if @BSS<>'' set @sqlwhere=@sqlwhere+' and BSS='''+@BSS+''''
if @Site<>'' set @sqlwhere=@sqlwhere+' and Site like '''+@Site+''''
if @Cell<>'' set @sqlwhere=@sqlwhere+' and right(Cell,5)='''+@Cell+''''
if (@orderby_key<>'不选')
  begin
    if (@ysf<>'不选')
       begin
           if @orderby_key='GSR7五项掉话率' set @sqlwhere=@sqlwhere+' and '+@GSR7_5XDHL+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='GSR7五项掉话总计' set @sqlwhere=@sqlwhere+' and '+@GSR7_5XDHZJ+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='SDCCH呼叫尝试次数' set @sqlwhere=@sqlwhere+' and '+@SDCCH_HJCSCS+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='SDCCH接入失败率' set @sqlwhere=@sqlwhere+' and '+@SDCCH_JRSBL+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='SDCCH拥塞次数' set @sqlwhere=@sqlwhere+' and ALLOC_SDCCH_FAIL '+@ysf+' '+@ysf_z+' '
           if @orderby_key='SDCCH拥塞率' set @sqlwhere=@sqlwhere+' and '+@SDCCH_YSL+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='TCH试呼次数bhqh' set @sqlwhere=@sqlwhere+' and MA_REQ_FROM_MSC '+@ysf+' '+@ysf_z+' '
           if @orderby_key='TCH试呼次数hqh' set @sqlwhere=@sqlwhere+' and '+@TCH_SHCS_HQH+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='话务量' set @sqlwhere=@sqlwhere+' and BUSY_TCH_MEAN '+@ysf+' '+@ysf_z+' '
           if @orderby_key='切换成功率' set @sqlwhere=@sqlwhere+' and '+@QHCGL+' '+@ysf+' '+@ysf_z+' '           if @orderby_key='切换失败率' set @sqlwhere=@sqlwhere+' and '+@QHSBL+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='四项掉话率plmn' set @sqlwhere=@sqlwhere+' and '+@4XDHL_PLMN+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='四项掉话率xqj' set @sqlwhere=@sqlwhere+' and '+@4XDHL_XQJ+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='无线接通率' set @sqlwhere=@sqlwhere+' and '+@WXJTL+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='总通话次数' set @sqlwhere=@sqlwhere+' and TOTAL_CALLS '+@ysf+' '+@ysf_z+' '
           if @orderby_key='TCH拥塞率bhqh' set @sqlwhere=@sqlwhere+' and '+@TCH_YSL_BHQH+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='TCH拥塞率hqh' set @sqlwhere=@sqlwhere+' and '+@TCH_YSL_HQH+' '+@ysf+' '+@ysf_z+' '
           if @orderby_key='TCH拥塞次数bhqh' set @sqlwhere=@sqlwhere+' and MA_CMD_TO_MS_BLKD '+@ysf+' '+@ysf_z+' '
           if @orderby_key='TCH拥塞次数hqh' set @sqlwhere=@sqlwhere+' and ALLOC_TCH_FAIL '+@ysf+' '+@ysf_z+' '
           if @orderby_key='RACH接入成功次数' set @sqlwhere=@sqlwhere+' and '+@ok_acc_proc_suc_r+' '+@ysf+' '+@ysf_z+' '
         end
  end
------------------------------------------------
set @sqlorderby=' order by riqi asc,时段 asc'
if (@orderby_key<>'不选')
   begin
     set @sqlorderby=@sqlorderby+','+@orderby_key+''
     if (@pxfs='不选' or @pxfs='正排') set @sqlorderby=@sqlorderby+' asc '  else  set @sqlorderby=@sqlorderby+' desc '   
   end
-------------------------------------SQL语句开始
SET @sql='select '
if @top_z<>'' set @sql=@sql+' top '+@top_z+' '
set @sql=@sql+'riqi,[Time] as 时段,
BSS AS BSS,
SITE AS SITE,
CELL as CELL,
'+@GSR7_5XDHL+' as GSR7五项掉话率,'+@GSR7_5XDHZJ+' as GSR7五项掉话总计,
'+@SDCCH_HJCSCS+' as SDCCH呼叫尝试次数,rtrim(CONVERT(char(8),'+@SDCCH_JRSBL+'))+"%" as SDCCH接入失败率,
'+@SDCCH_YSCS+' as SDCCH拥塞次数,'+@SDCCH_YSL+' as SDCCH拥塞率,
'+@TCH_SHCS_BHQH+' as TCH试呼次数bhqh,'+@TCH_SHCS_HQH+' as TCH试呼次数hqh,
'+@HWL+' as 话务量,'+@QHCGL+' as 切换成功率,
'+@QHSBL+' as 切换失败率,'+@4XDHL_PLMN+' as 四项掉话率plmn,
'+@4XDHL_XQJ+' as 四项掉话率xqj,'+@WXJTL+' as 无线接通率,
'+@ZTHCS+' as 总通话次数,'+@TCH_YSL_BHQH+' as TCH拥塞率bhqh,
'+@TCH_YSL_HQH+' as TCH拥塞率hqh,'+@TCH_YSCS_BHQH+' as TCH拥塞次数bhqh,
'+@TCH_YSCS_HQH+' as TCH拥塞次数hqh,'+@ok_acc_proc_suc_r+' as RACH接入成功次数
from rawstatscell '
set @sql=@sql+@sqlwhere
--

set @sql2=' union all select '
if @top_z<>'' set @sql2=@sql2+' top '+@top_z+' '
set @sql2=@sql2+' getdate() as a0,'' '' as a1,"" as a2,'''' as a3,''合计：'' as 合计,cast('+@GSR7_5XDHL_HJ+' as varchar(20)) as GSR7五项掉话率,'
set @sql2=@sql2+'sum('+@GSR7_5XDHZJ+') as GSR7五项掉话总计,sum('+@SDCCH_HJCSCS+') as SDCCH呼叫尝试次数,'
set @sql2=@sql2+' '+@SDCCH_JRSBL_HJ+' as SDCCH接入失败率,sum('+@SDCCH_YSCS+') as SDCCH拥塞次数,'+@SDCCH_YSL_HJ+' as SDCCH拥塞率,'
set @sql2=@sql2+'sum('+@TCH_SHCS_BHQH+') as TCH试呼次数bhqh,sum('+@TCH_SHCS_HQH+') as TCH试呼次数hqh,sum(cast('+@HWL+' as numeric)) as 话务量,'
set @sql2=@sql2+' '+@QHCGL_HJ+' as 切换成功率,'+@QHSBL_HJ+' as 切换失败率,'+@4XDHL_PLMN_HJ+' as 四项掉话率plmn,'+@4XDHL_XQJ_HJ+' as 四项掉话率xqj,'+@WXJTL_HJ+' as 无线接通率,sum('+@ZTHCS+') as 总通话次数,'
set @sql2=@sql2+' '+@TCH_YSL_BHQH_HJ+' as TCH拥塞率bhqh,'+@TCH_YSL_HQH_HJ+' as TCH拥塞率hqh,SUM('+@TCH_YSCS_BHQH+') as TCH拥塞次数bhqh,
SUM('+@TCH_YSCS_HQH+') as TCH拥塞次数hqh,sum(cast('+@ok_acc_proc_suc_r+' as numeric)) as ok_acc_proc_suc_r_hj from rawstatscell '
set @sql2=@sql2+@sqlwhere

set @sql2=@sql2+@sqlorderby
--set @sql=@sql+@sqlorderby
------------------------------------SQL语句结束

/*
set @sql2=@sql2+' ''2999-01-01'' as a0,'' '' as a1,"" as a2,'''' as a3,''合计：'' as 合计,cast('+@GSR7_5XDHL_HJ+' as varchar(20)) as GSR7五项掉话率,'
set @sql2=@sql2+'sum('+@GSR7_5XDHZJ+') as GSR7五项掉话总计,sum('+@SDCCH_HJCSCS+') as SDCCH呼叫尝试次数,'
set @sql2=@sql2+' '+@SDCCH_JRSBL_HJ+' as SDCCH接入失败率,sum('+@SDCCH_YSCS+') as SDCCH拥塞次数,'+@SDCCH_YSL_HJ+' as SDCCH拥塞率,'
set @sql2=@sql2+'sum('+@TCH_SHCS_BHQH+') as TCH试呼次数bhqh,sum('+@TCH_SHCS_HQH+') as TCH试呼次数hqh,sum(cast('+@HWL+' as numeric)) as 话务量,'
set @sql2=@sql2+' '+@QHCGL_HJ+' as 切换成功率,'+@QHSBL_HJ+' as 切换失败率,'+@4XDHL_PLMN_HJ+' as 四项掉话率plmn,'+@4XDHL_XQJ_HJ+' as 四项掉话率xqj,'+@WXJTL_HJ+' as 无线接通率,sum('+@ZTHCS+') as 总通话次数,'
set @sql2=@sql2+' '+@TCH_YSL_BHQH_HJ+' as TCH拥塞率bhqh,'+@TCH_YSL_HQH_HJ+' as TCH拥塞率hqh,SUM('+@TCH_YSCS_BHQH+') as TCH拥塞次数bhqh,
SUM('+@TCH_YSCS_HQH+') as TCH拥塞次数hqh from rawstatscell '
*/
print @sql+@sql2 --打印SQL
exec(@sql+@sql2)

end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--------------------------------------------------------------------------------------

学习到和注意的地方：

1，可以 把SQL语句赋给变量，再exec 变量。

2，等续。。。