declare @RQ1 datetime,@RQ2 datetime

select B.DEPTID,B.DEPT_NAME,W.CODE,W.NAME,(CASE G.HSFS when 0 then '自营' when 3 then '联营' when 1 then '代销' when 2 then '租赁' END) HSFS,sum(G.XSJE) XSJE,sum(G.XSJE_BHS) XSJE_BHS,sum(G.SJ_17+G.SJ_13) SJ,sum(G.XSCB) XSCB
 ,sum(G.XSCB_HS) XSCB_HS,sum(G.ZKJE) ZKJE,sum(G.YHJE) YHJE,0 XZJE,0 XZYHJE,0 GZJE,0 GZYHJE
 from BFBHDD.GHDWJXCR G,BFBHDD.BM B,BFBHDD.WLDW W
where G.RQ>=@RQ1
  and G.RQ<=@RQ2
  and G.DEPTID=B.DEPTID
  and G.CODE=W.CODE
group by B.DEPTID,B.DEPT_NAME,W.CODE,W.NAME,G.HSFS
union
select M.DEPTID,B.DEPT_NAME,'00' CODE,'中天' NAME,'自营' HSFS,0 XSJE,0 XSJE_BHS,0 SJ,sum(M.XSCB) XSCB,0 XSCB_HS,sum(M.ZKJE) ZKJE,0 YHJE,sum(M.XSJE) XZJE,sum(M.YHJE) XZYHJE,0 GZJE,0 GZYHJE
 from  BFBHDD.WRZSPCLJL W,BFBHDD.WRZSPCLJLITEM M,BFBHDD.BM B
 where ((W.ZXRQ>=@RQ1) AND (W.ZXRQ<=@RQ2))
  and W.JLBH=M.JLBH
  and M.DEPTID = B.DEPTID
 group by M.DEPTID,B.DEPT_NAME
union
select R.DEPTID,B.DEPT_NAME,'00' CODE,'中天' NAME,'自营' HSFS,0 XSJE,0 XSJE_BHS,0 SJ,sum(R.XSCB) XSCB,0 XSCB_HS,sum(R.ZKJE) ZKJE,0 YHJE,0 XZJE,0 XZYHJE,sum(R.XSJE) GZJE,sum(R.YHJE) GZYHJE
 from BFBHDD.WRZSPXSR R,BFBHDD.BM B,BFBHDD.SPXX X
 where R.DEPTID=B.DEPTID
 and R.SP_ID=X.SP_ID
 and R.RQ>=@RQ1
 and R.RQ<=@RQ2
group by R.DEPTID,B.DEPT_NAME