sql 销售月报表

CREATE PROCEDURE proc_sale_branch_month 
( 
@startdate datetime, 
@enddate datetime, 
@branch_no varchar(6) 
) 
 AS 
declare @sumAmount numeric(16,6) 
set @sumAmount=
(select sum(case kp_trans_no when 'SO' then 订单金额 else 0 end)-sum(case kp_trans_no when 'RI' then 订单金额 else 0 end)
from  
( 
SELECT  b.kp_trans_no, SUM(a.kp_sub_amt) AS 订单金额 
FROM dbo.pf_dj_detail a , dbo.pf_dj_master b  
where 
a.kp_sheet_no = b.kp_sheet_no 
and (b.kp_approve_flag = '1') 
and (b.kp_oper_date >=@startdate) and (b.kp_oper_date<=@enddate) 
and (b.kp_branch_no like @branch_no) 
GROUP BY b.kp_trans_no 
) bbb 
) 
 
begin 
select 年月,kp_branch_no,(select kp_branch_name from jb_jgck where kp_branch_no=aaa.kp_branch_no) as '仓库', 
        
       sum(case kp_trans_no when 'SS' then 数量 else 0 end) as '订单数量', 
       sum(case kp_trans_no when 'SS' then 订单金额 else 0 end) as '订单金额', 
       sum(case kp_trans_no when 'SO' then 数量 else 0 end) as '销售数量', 
       sum(case kp_trans_no when 'SO' then 订单金额 else 0 end) as '销售金额', 
       sum(case kp_trans_no when 'RI' then 数量 else 0 end) as '退货数量', 
       sum(case kp_trans_no when 'RI' then 订单金额 else 0 end) as '退货金额', 
       sum(case kp_trans_no when 'SM' then 数量 else 0 end) as '赠送数量', 
       sum(case kp_trans_no when 'SM' then 订单金额 else 0 end) as '赠送金额', 
       sum(case kp_trans_no when 'SO' then 数量 else 0 end)-sum(case kp_trans_no when 'RI' then 数量 else 0 end) as '小计数量', 
       sum(case kp_trans_no when 'SO' then 订单金额 else 0 end)-sum(case kp_trans_no when 'RI' then 订单金额 else 0 end) as '小计金额', 
       cast((sum(case kp_trans_no when 'SO' then 订单金额 else 0 end)-sum(case kp_trans_no when 'RI' then 订单金额 else 0 end))/@sumAmount*100 as varchar(20))+'%' as '占比' 
from  
( 
SELECT CONVERT(char(7), b.kp_oper_date, 120) AS 年月, b.kp_trans_no, b.kp_branch_no,
SUM(a.kp_real_qty) AS 数量, SUM(a.kp_sub_amt) AS 订单金额 
FROM dbo.pf_dj_detail a , dbo.pf_dj_master b  
where 
a.kp_sheet_no = b.kp_sheet_no 
and (b.kp_approve_flag = '1') 
and (b.kp_oper_date >=@startdate) and (b.kp_oper_date<=@enddate) 
and (b.kp_branch_no like @branch_no) 
GROUP BY CONVERT(char(7), b.kp_oper_date, 120), b.kp_trans_no,b.kp_branch_no 
) aaa 
group by kp_branch_no,年月 
order by kp_branch_no,年月 
end