春节 销售排名 存储过程(PB反编译服务 sqlserver sybase维护)_侠客天空——pb开发长益商友技术交流_百度空间 相册 广场游戏 登录注册 关注此空间 侠客天空——pb开发长益商友技术交流欢迎访问我们的官方站点：http://www.xkerp.com QQ群:31329369 2008-06-07 14:09 春节 销售排名 存储过程(PB反编译服务 sqlserver sybase维护) print 'rep_wangcjsalesort'SETUSER 'dbo'goCREATE PROC rep_wangcjsalesort @XSRQ char(10),@JSRQ char(10),@rowpm int,@LX char (1) /* 接收 销售日期和排名 春节前 10天 08.1.28 —— 2.6 后6 天 08.2.7 —— 2.12 wang_cjsalesort (XSRQ,JSRQ,LX,SPFL,SP_ID,XSSL,XSJE) if @LX='1' —— 代表大类 2 位 其余是 中类 4位 begin */ as begin /* 销售排名 存储过程 rep_wangcjsalesort */ declare @FDBH int /* 定义分店编号 */ declare @fddm varchar(10) /* 定义分店代码 */ /* 找到分店编号和代码 */ select @FDBH=convert(int,CUR_VAL) from BFCONFIG where JLBH=0 select @fddm=DEPTID from FDDEF where /* //销售排名 set rowcount 10 select SUBSTRING(s.SPFL,1,4) as SPFL,s.SPCODE,s.NAME,s.GHDW, SUM(d.XSSL) as XSSL,SUM(d.XSJE) XSJE from XSHZD d,SPXX s where d.SP_ID =s.SP_ID and d.RQ='2006.05.01' and d.DEPTID like '0212%' // and substring(d.DEPTID,1,4)='0212' and SUBSTRING(s.SPFL,1,2) in('01') GROUP BY SUBSTRING(s.SPFL,1,4),s.SPCODE,s.NAME,s.GHDW order by XSJE desc select XSRQ,SPFL,SP_ID,XSSL,XSJE,BZ from wang_cjsalesort select * from wang_cjsalesort; //只显示 20 条数据 set rowcount 20 //执行存储过程 生成数据 update statistics XSHZD exec rep_wangcjsalesort '2008.01.28','2008.02.06',20,'1' exec rep_wangcjsalesort '2008.02.07','2008.02.12',20,'1' ---查询语句 select w.XSRQ,w.JSRQ,w.SPFL,f.NAME,w.SP_ID,s.NAME,w.XSSL,w.XSJE from wang_cjsalesort w,SPXX s,SPFL f where w.SP_ID=s.SP_ID and SUBSTRING(s.SPFL,1,2)=f.SPFL and w.SPFL=f.SPFL; // order by w.XSRQ,w.JSRQ,w.SPFL; */ /* 删除已经存在的数据*/ DELETE wang_cjsalesort where XSRQ between @XSRQ and @JSRQ and /* 向 wang_cjsalesort 插入 01 食品部门 数据 */ set rowcount @rowpm insert wang_cjsalesort (XSRQ,JSRQ,LX,SPFL,SP_ID,XSSL,XSJE,BZ) select @XSRQ,@JSRQ,'1',SUBSTRING(s.SPFL,1,2),s.SP_ID, sum(isnull(d.XSSL,0)), sum(isnull(d.XSJE,0)) , '' from XSHZD d,SPXX s where d.SP_ID =s.SP_ID and d.RQ between @XSRQ and @JSRQ and substring(d.DEPTID,1,4)=@fddm and SUBSTRING(s.SPFL,1,2) in('01') and d.XSSL >0 and d.XSJE>0 group by SUBSTRING(s.SPFL,1,4),s.SP_ID order by 7 desc /* 向 wang_cjsalesort 插入 02 日化部门 数据 */ set rowcount @rowpm insert wang_cjsalesort (XSRQ,JSRQ,LX,SPFL,SP_ID,XSSL,XSJE,BZ) select @XSRQ,@JSRQ,'1',SUBSTRING(s.SPFL,1,2),s.SP_ID, sum(isnull(d.XSSL,0)), sum(isnull(d.XSJE,0)) , '' from XSHZD d,SPXX s where d.SP_ID =s.SP_ID and d.RQ between @XSRQ and @JSRQ and substring(d.DEPTID,1,4)=@fddm and SUBSTRING(s.SPFL,1,2) in('02') and d.XSSL >0 and d.XSJE>0 group by SUBSTRING(s.SPFL,1,4),s.SP_ID order by 7 desc /* 向 wang_cjsalesort 插入 03 生鲜部门 数据 */ set rowcount @rowpm insert wang_cjsalesort (XSRQ,JSRQ,LX,SPFL,SP_ID,XSSL,XSJE,BZ) select @XSRQ,@JSRQ,'1',SUBSTRING(s.SPFL,1,2),s.SP_ID, sum(isnull(d.XSSL,0)), sum(isnull(d.XSJE,0)) , '' from XSHZD d,SPXX s where d.SP_ID =s.SP_ID and d.RQ between @XSRQ and @JSRQ and substring(d.DEPTID,1,4)=@fddm and SUBSTRING(s.SPFL,1,2) in('03') and d.XSSL >0 and d.XSJE>0 group by SUBSTRING(s.SPFL,1,4),s.SP_ID order by 7 desc /* 向 wang_cjsalesort 插入 04 针织服装部门 数据 */ set rowcount @rowpm insert wang_cjsalesort (XSRQ,JSRQ,LX,SPFL,SP_ID,XSSL,XSJE,BZ) select @XSRQ,@JSRQ,'1',SUBSTRING(s.SPFL,1,2),s.SP_ID, sum(isnull(d.XSSL,0)), sum(isnull(d.XSJE,0)) , '' from XSHZD d,SPXX s where d.SP_ID =s.SP_ID and d.RQ between @XSRQ and @JSRQ and substring(d.DEPTID,1,4)=@fddm and SUBSTRING(s.SPFL,1,2) in('04') and d.XSSL >0 and d.XSJE>0 group by SUBSTRING(s.SPFL,1,4),s.SP_ID order by 7 desc /* 向 wang_cjsalesort 插入 05 家电类 数据 */ set rowcount @rowpm insert wang_cjsalesort (XSRQ,JSRQ,LX,SPFL,SP_ID,XSSL,XSJE,BZ) select @XSRQ,@JSRQ,'1',SUBSTRING(s.SPFL,1,2),s.SP_ID, sum(isnull(d.XSSL,0)), sum(isnull(d.XSJE,0)) , '' from XSHZD d,SPXX s where d.SP_ID =s.SP_ID and d.RQ between @XSRQ and @JSRQ and substring(d.DEPTID,1,4)=@fddm and SUBSTRING(s.SPFL,1,2) in('05') and d.XSSL >0 and d.XSJE>0 group by SUBSTRING(s.SPFL,1,4),s.SP_ID order by 7 desc /* 向 wang_cjsalesort 插入 06 自营生鲜类 数据 */ set rowcount @rowpm insert wang_cjsalesort (XSRQ,JSRQ,LX,SPFL,SP_ID,XSSL,XSJE,BZ) select @XSRQ,@JSRQ,'1',SUBSTRING(s.SPFL,1,2),s.SP_ID, sum(isnull(d.XSSL,0)), sum(isnull(d.XSJE,0)) , '' from XSHZD d,SPXX s where d.SP_ID =s.SP_ID and d.RQ between @XSRQ and @JSRQ and substring(d.DEPTID,1,4)=@fddm and SUBSTRING(s.SPFL,1,2) in('06') and d.XSSL >0 and d.XSJE>0 group by SUBSTRING(s.SPFL,1,4),s.SP_ID order by 7 desc /* OK,结束 */ endgoSETUSERgo #长益商友erp v6 分享到： 举报 浏览(159) 评论 转载 你可能也喜欢 所罗门室内设计索拉纳海滩的房子 优雅北欧风 54平紧凑型公寓 叠影重重之装饰柜 管理社扩招了！ 【空间周刊 - 小编风采 - 第6期】特别访谈：水冰月（娜娜） mop住宅~ AGi architects 宝洁公司卖场陈列KBD2（生意发展基金）执行标准 评论 帮助中心 | 空间客服 | 投诉中心 | 空间协议©2014 Baidu