无标题4.sql

用SQLSERVER2000做的分各个单位的报表

declare @jgbm varchar(18)
declare @kjyf int

set @jgbm='16504124'
set @kjyf=4

if (object_id('tempdb..#tb_jxc')is not null)
   drop table #tb_jxc

create table #TB_JXC      --网点进销存
   ( JGBM   varchar(18),    --机构编码
     ZJM    varchar(8),     --商品助记码
     SPBM   Varchar(13),    --商品编码
     SPMC   varchar(60),    --商品名称
     qckcsl numeric(18,6),  --上期库存数量
     qckcje numeric(18,6),  --上期库存金额
     GJSL   numeric(18,6),  --购进数量
     GJJE   numeric(18,6),  --购进金额 
     XSSL   numeric(18,6),  --销售数量
     xscbdj numeric(18,6),  --销售成本单价
     cb     numeric(18,6),  --成本
     XSWSJE numeric(18,6),  --销售无税金额
     KCSL   numeric(18,6),  --库存数量
     KCJE   numeric(18,6)   --库存金额 
   )
insert into #tb_jxc (jgbm,spbm,qckcsl,qckcje)
(select ckbm,spbm,qckcdl,qckcje from ylzyc2004.dbo.zw_rhz 
 where ( ckbm =@jgbm )and jgbm='16504100' and(ywrq in (select kjstart from ylzyc2004.dbo.zw_kjqj where kjyf=@kjyf)))
insert into #tb_jxc (jgbm,spbm,qckcsl,qckcje)
(select ckbm,spbm,0,0 from ylzyc2004.dbo.zw_rhz 
 where ( ckbm =@jgbm )and jgbm='16504100' and (spbm not in(select spbm from #tb_jxc))
      and(ywrq in (select kjend from ylzyc2004.dbo.zw_kjqj where kjyf=@kjyf)))


update #tb_jxc set xscbdj=(select kccbdj from ylzyc2004.dbo.zw_yhz 
              where #tb_jxc.spbm=ylzyc2004.dbo.zw_yhz.spbm and kjyf=@kjyf)
update #tb_jxc set zjm=(select zjm from ylzyc2004.dbo.sp_spxx
              where #tb_jxc.spbm=ylzyc2004.dbo.sp_spxx.spbm )
update #tb_jxc set spmc=(select spmc from ylzyc2004.dbo.sp_spxx
              where #tb_jxc.spbm=ylzyc2004.dbo.sp_spxx.spbm )

if (object_id('tempdb..#zw_rhz')is not null)
   drop table #zw_rhz
declare @kjstart datetime,
        @kjend   datetime
set @kjstart=(select kjstart from ylzyc2004.dbo.zw_kjqj where kjyf=@kjyf)
set @kjend=(select kjend from ylzyc2004.dbo.zw_kjqj where kjyf=@kjyf)

select spbm,sum(bqgjsl) as bqgjsl,sum(bqgjje) as bqgjje,sum(bqxssl) as bqxssl,sum(bqxsje) as bqxsje ,sum(qmkcsl) as qmkcsl
    into #zw_rhz from ylzyc2004.dbo.zw_rhz  where ckbm=@jgbm and jgbm='16504100' and( ywrq between @kjstart and @kjend)
     group by ylzyc2004.dbo.zw_rhz.spbm
update #zw_rhz set bqgjsl=(select sum(bqgjsl) from ylzyc2004.dbo.zw_rhz 
              where #zw_rhz.spbm=ylzyc2004.dbo.zw_rhz.spbm and jgbm='16504100'and
                   (ywrq between @kjstart and @kjend)and ckbm=@jgbm
              group by spbm)
update #zw_rhz set qmkcsl=(select sum(qmkcsl) from ylzyc2004.dbo.zw_rhz 
              where #zw_rhz.spbm=ylzyc2004.dbo.zw_rhz.spbm and jgbm='16504100' and
                   (ywrq = @kjend)and ckbm=@jgbm
              group by spbm)

 
update #tb_jxc set gjsl=
          (select bqgjsl from #zw_rhz where #zw_rhz.spbm=#tb_jxc.spbm)
update #tb_jxc set gjje=
          (select bqgjje from #zw_rhz where #zw_rhz.spbm=#tb_jxc.spbm)
update #tb_jxc set xssl=
          (select bqxssl from #zw_rhz where #zw_rhz.spbm=#tb_jxc.spbm)
update #tb_jxc set xswsje=
          (select bqxsje from #zw_rhz where #zw_rhz.spbm=#tb_jxc.spbm)
update #tb_jxc set kcsl=
          (select qmkcsl from #zw_rhz where #zw_rhz.spbm=#tb_jxc.spbm)

update #tb_jxc set gjje=gjsl*xscbdj
update #tb_jxc set cb=xssl*xscbdj
update #tb_jxc set kcje=kcsl*xscbdj
select * from #tb_jxc where qckcsl+gjsl+xssl+kcsl<>0
select b.ckmc,aa.jgbm,zjm,spbm,spmc,qckcsl,qckcje,gjsl,gjje,xssl,xscbdj,cb,xswsje,kcsl,kcje from #tb_jxc aa
   left join ck_ckxx b on aa.jgbm=b.ckbm where qckcsl+gjsl+xssl+kcsl<>0

select ckbm from ck_ckxx where ckbm<>'90'order by ckbm 
select '关内烟仓库','01'='地产烟仓库'   ,'16504109'='伊宁市卷烟物流配送中心' ,
'16504121'='伊宁县卷烟物流配送中心'  ,
察布查尔县卷烟物流配送中心 as '16504122',
霍城县卷烟物流配送中心 as '16504123',
巩留县卷烟物流配送中心 as '16504124',
新源县卷烟物流配送中心 as '16504125',
昭苏县卷烟物流配送中心 as '16504126',
尼勒克县卷烟物流配送中心 as '16504127',
特克斯县卷烟物流配送中心 as '16504128'

select b.ckmc,aa.jgbm,zjm,spbm,spmc,qckcsl,qckcje,gjsl,gjje,xssl,xscbdj,cb,xswsje,kcsl,kcje from #tb_jxc aa
   left join ck_ckxx b on aa.jgbm=b.ckbm where qckcsl+gjsl+xssl+kcsl<>0



select * from ck_ckxx order by ckbm