愿与共同兴趣者共同学习交流(U8自定义报表)

hncswc

出售Ｕ８跨年查询明细账，总账，往年比较余额表等ＳＱＬ代码.（只要在查询分析器里执行即可，包含报表格式，很好用一键式操作）


IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME ='csyy_lt_wangcheng' and TYPE='P')
    DROP PROCEDURE [csyy_lt_wangcheng]
go
-----------------------------------------------------------
create proc csyy_lt_wangcheng (@chrWhere  varchar(1000))
as
DECLARE @attribute char(20),@start varchar(20),@end varchar(20),@Warehouse char(40),
        @str varchar(40),@startPosition int,@endPosition int, @chrsql varchar(2000),
        @ctable varchar(1000),@lttable varchar(1000)
set @str='日期'
set @startPosition=CHARINDEX(@str,@chrWhere,0)
set @start=SUBSTRING(@chrWhere,@startPosition+5,10)
set @endPosition=CHARINDEX(@str,@chrWhere,@startPosition+4)
set @end=SUBSTRING(@chrWhere,@endPosition+5,10)
IF @startPosition = 0
   set @start='2007-01-01'
IF @endPosition = 0
   set @end='2007-12-31'
set @ctable='选择报表'
set @startPosition=CHARINDEX(@ctable,@chrWhere,0)
set @lttable=SUBSTRING(@chrWhere,@startPosition+7,2)


IF @lttable='01'
    BEGIN
       If  exists(select name from tempdb..sysobjects where name ='table1')
                Drop table  tempdb..table1
       select
             w.cWhName as 仓库名称,ic.cInvCCode as 存货大类编码 ,cInvCName as 物资大类,
             rds.cinvcode as 存货编码,cinvname as 物资名称, i.cInvStd 物资规格,rs.cRdName as 收发类别,
             rds.cfree1 as 完好情况,rds.cfree2 as 采购合同号,rds.cfree3 as 版本号,
             rds.cfree4 as 来源,rds.cfree5 as 状态,rds.cfree6 as 资产编号,rds.cfree7 as 闲置原因,
             rds.cfree8 as 处理建议,rds.cfree9 as 生产厂家,rds.cfree10 as 工程项目,
             rds.cdefine22 as 所属公司属性,sum(iprice) as 结存金额
       into tempdb..table1
       from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs
       where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and ((ic.cInvCCode LIKE '01%') OR (ic.cInvCCode LIKE '02%'))
       GROUP BY rds.cinvcode,i.cinvname,w.cWhName,rds.cfree1,rds.cfree2,rd.ddate,rds.cfree3,rds.cfree4,
             rds.cfree5,rds.cfree6,rds.cfree7,rds.cfree8,rds.cfree9,rds.cfree10,rds.cdefine22,
             I.cInvStd,cInvCName,ic.cInvCCode,rs.cRdName
   
       If  exists(select name from tempdb..sysobjects where name ='table1_1')
                Drop table  tempdb..table1_1
       SELECT 物资大类,
              SUM(CASE 收发类别 WHEN '财务账入库' THEN 结存金额 ELSE 0 END)
              -SUM(CASE 收发类别 WHEN '财务账出库' THEN 结存金额 ELSE 0 END)
              AS [库存工程物资(财务账)],
             SUM(CASE 收发类别 WHEN '工余料入库' THEN 结存金额 ELSE 0 END)
             AS 库存工余料,
             SUM(CASE 收发类别 WHEN '无合同入库' THEN 结存金额 ELSE 0 END)
             -SUM(CASE 收发类别 WHEN '无合同出库' THEN 结存金额 ELSE 0 END)
              AS 无合同物资,
             SUM(CASE 收发类别 WHEN '财务账入库' THEN 结存金额 ELSE 0 END)
            -SUM(CASE 收发类别 WHEN '财务账出库' THEN 结存金额 ELSE 0 END)
            +SUM(CASE 收发类别 WHEN '工余料入库' THEN 结存金额 ELSE 0 END)
            -SUM(CASE 收发类别 WHEN '工余料出库' THEN 结存金额 ELSE 0 END)
            +SUM(CASE 收发类别 WHEN '无合同入库' THEN 结存金额 ELSE 0 END)
            -SUM(CASE 收发类别 WHEN '无合同出库' THEN 结存金额 ELSE 0 END)
            AS 合计金额,null as 备注,所属公司属性
        into tempdb..table1_1
        FROM tempdb..table1
        GROUP BY 物资大类,所属公司属性
        If  exists(select name from tempdb..sysobjects where name ='table1')
                Drop table  tempdb..table1
        If  exists(select name from tempdb..sysobjects where name ='table2')
                Drop table  tempdb..table2
        select *
        into tempdb..table2
        from ufsystem..gcwzhzb_ss
        update tempdb..table2
        set  tempdb..table2.[库存工程物资(财务账)]=tempdb..table1_1.[库存工程物资(财务账)],
             tempdb..table2.库存工余料=tempdb..table1_1.库存工余料,
             tempdb..table2.无合同物资=tempdb..table1_1.无合同物资,
             tempdb..table2.合计金额=tempdb..table1_1.合计金额
         from tempdb..table2 LEFT JOIN tempdb..table1_1
              on tempdb..table1_1.物资大类=tempdb..table2.物资大类
         If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
         select *
         into tempdb..cssyywangcheng
         from tempdb..table2
         
    END
   
ELSE
     BEGIN
        print 'NEXT'
     END

[ 本帖最后由 hncswc 于 2008-2-26 15:51 编辑 ]

hncswc

IF @lttable='02'
    BEGIN
    If  exists(select name from tempdb..sysobjects where name ='table1')
                Drop table  tempdb..table1
    select
       w.cWhName as 仓库名称,ic.cInvCCode as 存货大类编码 ,cInvCName as 物资大类,rds.cinvcode as 存货编码,
       cinvname as 物资名称, i.cInvStd 物资规格,rs.cRdName as 收发类别,
       rds.cfree1 as 完好情况,rds.cfree2 as 采购合同号,rds.cfree3 as 版本号,
       rds.cfree4 as 来源,rds.cfree5 as 状态,rds.cfree6 as 资产编号,rds.cfree7 as 闲置原因,
       rds.cfree8 as 处理建议,rds.cfree9 as 生产厂家,rds.cfree10 as 工程项目,rds.cdefine22 as 所属公司属性,
       sum(iprice) as 结存金额
       into tempdb..table1
    from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs
    where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='非上市' and ddate<=@end
      and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
      and ((ic.cInvCCode LIKE '01%') OR (ic.cInvCCode LIKE '02%'))
    GROUP BY rds.cinvcode,i.cinvname,w.cWhName,rds.cfree1,rds.cfree2,rd.ddate,rds.cfree3,rds.cfree4,
         rds.cfree5,rds.cfree6,rds.cfree7,rds.cfree8,rds.cfree9,rds.cfree10,rds.cdefine22,
         I.cInvStd,cInvCName,ic.cInvCCode,rs.cRdName
  If  exists(select name from tempdb..sysobjects where name ='table1_1')
                Drop table  tempdb..table1_1
     SELECT 物资大类,
      SUM(CASE 收发类别 WHEN '财务账入库' THEN 结存金额 ELSE 0 END)
      -SUM(CASE 收发类别 WHEN '财务账出库' THEN 结存金额 ELSE 0 END)
      AS [库存工程物资(财务账)],
      SUM(CASE 收发类别 WHEN '工余料入库' THEN 结存金额 ELSE 0 END)
      -SUM(CASE 收发类别 WHEN '工余料出库' THEN 结存金额 ELSE 0 END)
      AS 库存工余料,
      SUM(CASE 收发类别 WHEN '无合同入库' THEN 结存金额 ELSE 0 END)
      -SUM(CASE 收发类别 WHEN '无合同出库' THEN 结存金额 ELSE 0 END)
      AS 无合同物资,
      SUM(CASE 收发类别 WHEN '财务账入库' THEN 结存金额 ELSE 0 END)
      -SUM(CASE 收发类别 WHEN '财务账出库' THEN 结存金额 ELSE 0 END)
      +SUM(CASE 收发类别 WHEN '工余料入库' THEN 结存金额 ELSE 0 END)
      -SUM(CASE 收发类别 WHEN '工余料出库' THEN 结存金额 ELSE 0 END)
      +SUM(CASE 收发类别 WHEN '无合同入库' THEN 结存金额 ELSE 0 END)
      -SUM(CASE 收发类别 WHEN '无合同出库' THEN 结存金额 ELSE 0 END)
      AS 合计金额,null as 备注,所属公司属性
      into tempdb..table1_1
    FROM tempdb..table1
    GROUP BY 物资大类,所属公司属性
    If  exists(select name from tempdb..sysobjects where name ='table1')
                Drop table  tempdb..table1
    If  exists(select name from tempdb..sysobjects where name ='table2')
                Drop table  tempdb..table2
    select *
    into tempdb..table2
    from ufsystem..gcwzhzb_fss
    update tempdb..table2
    set  tempdb..table2.[库存工程物资(财务账)]=tempdb..table1_1.[库存工程物资(财务账)],
     tempdb..table2.库存工余料=tempdb..table1_1.库存工余料,
     tempdb..table2.无合同物资=tempdb..table1_1.无合同物资,
     tempdb..table2.合计金额=tempdb..table1_1.合计金额
    from tempdb..table2 LEFT JOIN tempdb..table1_1 on tempdb..table1_1.物资大类=tempdb..table2.物资大类
    If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
    select *
    into tempdb..cssyywangcheng
    from tempdb..table2
   
    END
   
ELSE
     BEGIN
       print 'NEXT'
     END


IF @lttable='03'
    BEGIN
      If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
      select
       w.cWhName as 仓库名称,cInvCName as 物资大类,cinvname as 物资名称,
       i.cInvStd 物资规格,rds.cfree3 as 版本号,cu.cComUnitName AS 计量单位,
       null as 分摊单价,sum(iprice) as 结存金额,null as 分摊金额,
       rds.cfree2 as 采购合同号,rds.cfree9 as 生产厂家,
       rds.cfree10 as 工程项目,rds.cfree1 as 完好情况,rds.cdefine22 as 所属公司属性
       into tempdb..cssyywangcheng
      from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,
           Rd_Style rs ,ComputationUnit cu
      where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='上市'
            and  i.cComUnitCode = cu.cComunitCode  and ddate<=@end
            and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
            and ((ic.cInvCCode LIKE '01%') OR (ic.cInvCCode LIKE '02%'))
            and ((rd.crdcode='01') or rd.crdcode='06')
      GROUP BY i.cinvname,w.cWhName,rds.cfree1,rds.cfree2,rd.ddate,rds.cfree3,
               rds.cfree9,rds.cfree10,I.cInvStd,cInvCName,cu.cComUnitName,rds.cdefine22
    END
ELSE
    BEGIN
    print 'NEXT'
    END


IF @lttable='04'
    BEGIN
      If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
      select
       w.cWhName as 仓库名称,cInvCName as 物资大类,cinvname as 物资名称,
       i.cInvStd 物资规格,rds.cfree3 as 版本号,cu.cComUnitName AS 计量单位,
       null as 分摊单价,sum(iprice) as 结存金额,null as 分摊金额,
       rds.cfree2 as 采购合同号,rds.cfree9 as 生产厂家,
       rds.cfree10 as 工程项目,rds.cfree1 as 完好情况,rds.cdefine22 as 所属公司属性
       into tempdb..cssyywangcheng
      from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,
           Rd_Style rs ,ComputationUnit cu
      where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='非上市'
            and  i.cComUnitCode = cu.cComunitCode  and ddate<=@end
            and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
            and ((ic.cInvCCode LIKE '01%') OR (ic.cInvCCode LIKE '02%'))
            and ((rd.crdcode='01') or rd.crdcode='06')
      GROUP BY i.cinvname,w.cWhName,rds.cfree1,rds.cfree2,rd.ddate,rds.cfree3,
               rds.cfree9,rds.cfree10,I.cInvStd,cInvCName,cu.cComUnitName,rds.cdefine22
    END
ELSE
    BEGIN
    print 'NEXT'
    END



IF @lttable='05'
    BEGIN
      If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
      select
       w.cWhName as 仓库名称,cInvCName as 物资大类,cinvname as 物资名称,
       i.cInvStd 物资规格,rds.cfree3 as 版本号,cu.cComUnitName AS 计量单位,
       null as 分摊单价,sum(iprice) as 结存金额,null as 分摊金额,
       rds.cfree2 as 采购合同号,rds.cfree9 as 生产厂家,
       rds.cfree10 as 工程项目,rds.cfree1 as 完好情况,rds.cdefine22 as 所属公司属性
       into tempdb..cssyywangcheng
      from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,
           Rd_Style rs ,ComputationUnit cu
      where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='上市'
            and  i.cComUnitCode = cu.cComunitCode  and ddate<=@end
            and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
            and ((ic.cInvCCode LIKE '01%') OR (ic.cInvCCode LIKE '02%'))
            and ((rd.crdcode='02') or rd.crdcode='07')
      GROUP BY i.cinvname,w.cWhName,rds.cfree1,rds.cfree2,rd.ddate,rds.cfree3,
               rds.cfree9,rds.cfree10,I.cInvStd,cInvCName,cu.cComUnitName,rds.cdefine22
    END
ELSE
    BEGIN
    print 'NEXT'
    END

hncswc

IF @lttable='06'
    BEGIN
      If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
      select
       w.cWhName as 仓库名称,cInvCName as 物资大类,cinvname as 物资名称,
       i.cInvStd 物资规格,rds.cfree3 as 版本号,cu.cComUnitName AS 计量单位,
       null as 分摊单价,sum(iprice) as 结存金额,null as 分摊金额,
       rds.cfree2 as 采购合同号,rds.cfree9 as 生产厂家,
       rds.cfree10 as 工程项目,rds.cfree1 as 完好情况,rds.cdefine22 as 所属公司属性
       into tempdb..cssyywangcheng
      from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,
           Rd_Style rs ,ComputationUnit cu
      where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='非上市'
            and  i.cComUnitCode = cu.cComunitCode  and ddate<=@end
            and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
            and ((ic.cInvCCode LIKE '01%') OR (ic.cInvCCode LIKE '02%'))
            and (rd.crdcode='02' or  rd.crdcode='07')
      GROUP BY i.cinvname,w.cWhName,rds.cfree1,rds.cfree2,rd.ddate,rds.cfree3,
               rds.cfree9,rds.cfree10,I.cInvStd,cInvCName,cu.cComUnitName,rds.cdefine22
    END
ELSE
    BEGIN
    print 'NEXT'
    END



IF @lttable='07'
    BEGIN
      If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
      select
       w.cWhName as 仓库名称,cInvCName as 物资大类,cinvname as 物资名称,
       i.cInvStd 物资规格,rds.cfree3 as 版本号,cu.cComUnitName AS 计量单位,
       null as 分摊单价,sum(iprice) as 结存金额,null as 分摊金额,
       rds.cfree2 as 采购合同号,rds.cfree9 as 生产厂家,
       rds.cfree10 as 工程项目,rds.cfree1 as 完好情况,rds.cdefine22 as 所属公司属性
       into tempdb..cssyywangcheng
      from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,
           Rd_Style rs ,ComputationUnit cu
      where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='上市'
            and  i.cComUnitCode = cu.cComunitCode  and ddate<=@end
            and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
            and ((ic.cInvCCode LIKE '01%') OR (ic.cInvCCode LIKE '02%'))
            and ((rd.crdcode='03') or rd.crdcode='08')
      GROUP BY i.cinvname,w.cWhName,rds.cfree1,rds.cfree2,rd.ddate,rds.cfree3,
               rds.cfree9,rds.cfree10,I.cInvStd,cInvCName,cu.cComUnitName,rds.cdefine22
    END
ELSE
    BEGIN
    print 'NEXT'
    END


IF @lttable='08'
    BEGIN
      If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
      select
       w.cWhName as 仓库名称,cInvCName as 物资大类,cinvname as 物资名称,
       i.cInvStd 物资规格,rds.cfree3 as 版本号,cu.cComUnitName AS 计量单位,
       null as 分摊单价,sum(iprice) as 结存金额,null as 分摊金额,
       rds.cfree2 as 采购合同号,rds.cfree9 as 生产厂家,
       rds.cfree10 as 工程项目,rds.cfree1 as 完好情况,rds.cdefine22 as 所属公司属性
       into tempdb..cssyywangcheng
      from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,
           Rd_Style rs ,ComputationUnit cu
      where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='非上市'
            and  i.cComUnitCode = cu.cComunitCode  and ddate<=@end
            and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
            and ((ic.cInvCCode LIKE '01%') OR (ic.cInvCCode LIKE '02%'))
            and (rd.crdcode='03' or  rd.crdcode='08')
      GROUP BY i.cinvname,w.cWhName,rds.cfree1,rds.cfree2,rd.ddate,rds.cfree3,
               rds.cfree9,rds.cfree10,I.cInvStd,cInvCName,cu.cComUnitName,rds.cdefine22
    END
ELSE
    BEGIN
    print 'NEXT'
    END


IF @lttable='09'
    BEGIN
     If  exists(select name from tempdb..sysobjects where name ='table09')
                Drop table  tempdb..table09
     select
             cInvCName as 物资大类,
             sum(case cfree1 when '完好可用' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 完好可用,
             sum(case cfree5 when '完好可调配(技术落后)' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 技术落后,
             sum(case cfree5 when '完好可调配(富余)' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 富余,
             sum(case cfree5 when '修理可用' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 修理可用,
             sum(case cfree5 when '拟报废' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 拟报废,
             sum(case cfree1 when '完好可用' then case rd.brdflag when 1 then iprice else -iprice end else 0 end)
             +sum(case cfree5 when '完好可调配(技术落后)' then case rd.brdflag when 1 then iprice else -iprice end else 0 end)
             +sum(case cfree5 when '完好可调配(富余)' then case rd.brdflag when 1 then iprice else -iprice end else 0 end)
             +sum(case cfree5 when '修理可用' then case rd.brdflag when 1 then iprice else -iprice end else 0 end)
             +sum(case cfree5 when '拟报废' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 合计,
             rds.cdefine22 as 所属公司属性
       into tempdb..table09
       from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs
       where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and (ic.cInvCCode LIKE '03%')
       GROUP BY rds.cdefine22,cInvCName
       If  exists(select name from tempdb..sysobjects where name ='table0902')
                Drop table  tempdb..table0902
       select *
       into tempdb..table0902
       from  ufsystem..ywwzhzb
       update tempdb..table0902
        set  tempdb..table0902.完好可用结存=tempdb..table09.完好可用,
             tempdb..table0902.技术落后结存=tempdb..table09.技术落后,
             tempdb..table0902.富余结存=tempdb..table09.富余,
             tempdb..table0902.修理可用结存=tempdb..table09.修理可用,
             tempdb..table0902.拟报废结存=tempdb..table09.拟报废,
             tempdb..table0902.合计=tempdb..table09.合计      
         from tempdb..table0902 LEFT JOIN tempdb..table09
              on tempdb..table09.物资大类=tempdb..table0902.存货大类
       If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
       select *
       into tempdb..cssyywangcheng
       from tempdb..table0902
    END
ELSE
    BEGIN
      print 'NEXT'
    END

hncswc

IF @lttable='10'
    BEGIN
     If  exists(select name from tempdb..sysobjects where name ='table10')
                Drop table  tempdb..table10
     select
             cInvCName as 物资大类,
             sum(case cfree1 when '完好可用' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 完好可用,
             sum(case cfree5 when '完好可调配(技术落后)' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 技术落后,
             sum(case cfree5 when '完好可调配(富余)' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 富余,
             sum(case cfree5 when '修理可用' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 修理可用,
             sum(case cfree5 when '拟报废' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 拟报废,
             sum(case cfree1 when '完好可用' then case rd.brdflag when 1 then iprice else -iprice end else 0 end)
             +sum(case cfree5 when '完好可调配(技术落后)' then case rd.brdflag when 1 then iprice else -iprice end else 0 end)
             +sum(case cfree5 when '完好可调配(富余)' then case rd.brdflag when 1 then iprice else -iprice end else 0 end)
             +sum(case cfree5 when '修理可用' then case rd.brdflag when 1 then iprice else -iprice end else 0 end)
             +sum(case cfree5 when '拟报废' then case rd.brdflag when 1 then iprice else -iprice end else 0 end) as 合计,
             rds.cdefine22 as 所属公司属性
       into tempdb..table10
       from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs
       where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='非上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and (ic.cInvCCode LIKE '03%')
       GROUP BY rds.cdefine22,cInvCName
       If  exists(select name from tempdb..sysobjects where name ='table1002')
                Drop table  tempdb..table1002
       select *
       into tempdb..table1002
       from  ufsystem..ywwzhzb
       update tempdb..table1002
        set  tempdb..table1002.完好可用结存=tempdb..table10.完好可用,
             tempdb..table1002.技术落后结存=tempdb..table10.技术落后,
             tempdb..table1002.富余结存=tempdb..table10.富余,
             tempdb..table1002.修理可用结存=tempdb..table10.修理可用,
             tempdb..table1002.拟报废结存=tempdb..table10.拟报废,
             tempdb..table1002.合计=tempdb..table10.合计      
         from tempdb..table1002 LEFT JOIN tempdb..table10
              on tempdb..table10.物资大类=tempdb..table1002.存货大类
       If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
       select *
       into tempdb..cssyywangcheng
       from tempdb..table1002
    END
ELSE
    BEGIN
      print 'NEXT'
    END


IF @lttable='11'
     BEGIN
          If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
       SELECT w.cwhname as 仓库档案,ic.cinvcname as 物资大类,i.cinvname as 物资名称,
              i.cInvStd AS 物资规格型号,rds.cfree3 as 版本号, NULL AS 附加说明,
              sum(case rd.brdflag when 1 then iquantity else -iquantity end) as 数量,
              C.cComUnitName as 计量单位,
              sum(case rd.brdflag when 1 then iprice else -iprice end) as 账面金额,
              null as 估值,rds.cfree9 厂商,
              sum(case cfree4 when '随工程采购' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 随工程采购,
              sum(case cfree4 when '运维费用采购' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 运维费用采购,
              sum(case cfree5 when '完好可调配(技术落后)' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 技术落后,
              sum(case cfree5 when '完好可调配(富余)' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 富余,
              null as 备注
       into  tempdb..cssyywangcheng
       FROM  RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs,
             ComputationUnit C
            
       WHERE
             rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and ((ic.cInvCCode LIKE '03%') ) and I.cComUnitCode=C.cComUnitCode
       GROUP BY w.cwhname,ic.cinvcname,i.cinvname,i.cInvStd,rds.cfree3,C.cComUnitName,rds.cfree9   
     END
ELSE
    BEGIN
      print 'NEXT'
    END


IF @lttable='12'
     BEGIN
       If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng

       SELECT w.cwhname as 仓库档案,ic.cinvcname as 物资大类,i.cinvname as 物资名称,
              i.cInvStd AS 物资规格型号,rds.cfree3 as 版本号, NULL AS 附加说明,
              sum(case rd.brdflag when 1 then iquantity else -iquantity end) as 数量,
              C.cComUnitName as 计量单位,
              sum(case rd.brdflag when 1 then iprice else -iprice end) as 账面金额,
              null as 估值,rds.cfree9 厂商,
              sum(case cfree4 when '随工程采购' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 随工程采购,
              sum(case cfree4 when '运维费用采购' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 运维费用采购,
              sum(case cfree5 when '完好可调配(技术落后)' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 技术落后,
              sum(case cfree5 when '完好可调配(富余)' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 富余,
              null as 备注
       into  tempdb..cssyywangcheng
       FROM  RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs,
             ComputationUnit C
            
       WHERE
             rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='非上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and ((ic.cInvCCode LIKE '03%') ) and I.cComUnitCode=C.cComUnitCode
       GROUP BY w.cwhname,ic.cinvcname,i.cinvname,i.cInvStd,rds.cfree3,C.cComUnitName,rds.cfree9   
     END
ELSE
    BEGIN
      print 'NEXT'
    END

IF @lttable='13'
     BEGIN
          If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
       SELECT w.cwhname as 仓库档案,ic.cinvcname as 物资大类,i.cinvname as 物资名称,
              i.cInvStd AS 物资规格型号,rds.cfree3 as 版本号, NULL AS 附加说明,
              sum(case rd.brdflag when 1 then iquantity else -iquantity end) as 数量,
              C.cComUnitName as 计量单位,
              sum(case rd.brdflag when 1 then iprice else -iprice end) as 账面金额,
              null as 估值,rds.cfree9 厂商,
              sum(case cfree4 when '随工程采购' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 随工程采购,
              sum(case cfree4 when '运维费用采购' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 运维费用采购,
              sum(case cfree5 when '完好可用' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 完好可用,
              sum(case cfree5 when '完好可调配(技术落后)' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 技术落后,
              sum(case cfree5 when '完好可调配(富余)' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 富余,
              sum(case cfree5 when '修理可用' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 修理可用,
              sum(case cfree5 when '拟报废' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 拟报废,
              null as 备注
       into  tempdb..cssyywangcheng
       FROM  RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs,
             ComputationUnit C
            
       WHERE
             rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and ((ic.cInvCCode LIKE '03%') ) and I.cComUnitCode=C.cComUnitCode
       GROUP BY w.cwhname,ic.cinvcname,i.cinvname,i.cInvStd,rds.cfree3,C.cComUnitName,rds.cfree9   
     END
ELSE
    BEGIN
      print 'NEXT'
    END

hncswc

IF @lttable='14'
     BEGIN
          If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
       SELECT w.cwhname as 仓库档案,ic.cinvcname as 物资大类,i.cinvname as 物资名称,
              i.cInvStd AS 物资规格型号,rds.cfree3 as 版本号, NULL AS 附加说明,
              sum(case rd.brdflag when 1 then iquantity else -iquantity end) as 数量,
              C.cComUnitName as 计量单位,
              sum(case rd.brdflag when 1 then iprice else -iprice end) as 账面金额,
              null as 估值,rds.cfree9 厂商,
              sum(case cfree4 when '随工程采购' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 随工程采购,
              sum(case cfree4 when '运维费用采购' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 运维费用采购,
              sum(case cfree5 when '完好可用' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 完好可用,
              sum(case cfree5 when '完好可调配(技术落后)' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 技术落后,
              sum(case cfree5 when '完好可调配(富余)' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 富余,
              sum(case cfree5 when '修理可用' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 修理可用,
              sum(case cfree5 when '拟报废' then (case rd.brdflag when 1 then iquantity else -iquantity end  ) else 0 end ) as 拟报废,
              null as 备注
       into  tempdb..cssyywangcheng
       FROM  RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs,
             ComputationUnit C      
       WHERE
             rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='非上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and ((ic.cInvCCode LIKE '03%') ) and I.cComUnitCode=C.cComUnitCode
       GROUP BY w.cwhname,ic.cinvcname,i.cinvname,i.cInvStd,rds.cfree3,C.cComUnitName,rds.cfree9   
     END
ELSE
    BEGIN
      print 'NEXT'
    END

IF @lttable='15'
    BEGIN
       If  exists(select name from tempdb..sysobjects where name ='table15')
                Drop table  tempdb..table15
       select
             cInvCName as 物资大类,
             sum(case rd.brdflag when 1 then  iprice else -iprice end) as 合计
       into tempdb..table15
       from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs
       where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and (ic.cInvCCode LIKE '05%')
       GROUP BY cInvCName
       select *
       into tempdb..table1502
       from  ufsystem..xzzchzb
       update tempdb..table1502
        set tempdb..table1502.[合计金额(人民币元)]=tempdb..table15.合计      
         from tempdb..table1502 LEFT JOIN tempdb..table15
              on tempdb..table15.物资大类=tempdb..table1502.物资大类
       If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
       select *
       into tempdb..cssyywangcheng
       from tempdb..table1502
    END
ELSE
    BEGIN
      print 'NEXT'
    END

IF @lttable='16'
    BEGIN
       If  exists(select name from tempdb..sysobjects where name ='table16')
                Drop table  tempdb..table16
       select
             cInvCName as 物资大类,
             sum(case rd.brdflag when 1 then  iprice else -iprice end) as 合计
       into tempdb..table16
       from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs
       where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='非上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and (ic.cInvCCode LIKE '05%')
       GROUP BY cInvCName
       If  exists(select name from tempdb..sysobjects where name ='table1602')
                Drop table  tempdb..table1602
       select *
       into tempdb..table1602
       from  ufsystem..fxzzchzb
       update tempdb..table1602
        set  
             tempdb..table1602.[合计金额(人民币元)]=tempdb..table16.合计      
         from tempdb..table1602 LEFT JOIN tempdb..table16
              on tempdb..table16.物资大类=tempdb..table1602.物资大类
       If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
       select *
       into tempdb..cssyywangcheng
       from tempdb..table1602
    END
ELSE
    BEGIN
      print 'NEXT'
    END


IF @lttable='17'
     BEGIN
      If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
      select w.cwhname as 仓库档案,ic.cinvcname as 物资大类,i.cinvcode as 资产编号,
             i.cinvname as 设备名称,i.cInvStd AS 物资规格型号,null as 附加说明,rds.cfree9 厂商,
             cu.cComUnitName as 计量单位,
             sum(case rd.brdflag when 1 then  iquantity else -iquantity end) as 数量,
             sum(case rd.brdflag when 1 then  iprice else -iprice end) as 财务账面净值,
             rds.cDefine36 as 投入使用时间, D.cDepName as 使用部门,rds.cFree10 as 项目归属,
             rds.cFree7 as 闲置原因,rds.cFree1 as 完好情况,rds.cFree8 as 处置建议,
             null as  备注
      into tempdb..cssyywangcheng
      from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs ,
           ComputationUnit cu ,Department D
      where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and (ic.cInvCCode LIKE '05%') and I.cComunitCode=cu.cComunitCode and D.cdepcode=rd.cdepcode
       GROUP BY  w.cwhname ,i.cInvStd,i.cinvcode,i.cinvname,i.cInvStd,rds.cfree9, cu.cComUnitName ,
                 rds.cDefine36 ,D.cDepName,rds.cFree10 , rds.cFree7 ,rds.cFree1,rds.cFree8 ,ic.cinvcname
     END


ELSE
     BEGIN
      print 'NEXT'
     END

hncswc

IF @lttable='18'
     BEGIN
      If  exists(select name from tempdb..sysobjects where name ='cssyywangcheng')
                Drop table  tempdb..cssyywangcheng
      select w.cwhname as 仓库档案,ic.cinvcname as 物资大类,i.cinvcode as 资产编号,
             i.cinvname as 设备名称,i.cInvStd AS 物资规格型号,null as 附加说明,rds.cfree9 厂商,
             cu.cComUnitName as 计量单位,
             sum(case rd.brdflag when 1 then  iquantity else -iquantity end) as 数量,
             sum(case rd.brdflag when 1 then  iprice else -iprice end) as 财务账面净值,
             rds.cDefine36 as 投入使用时间, D.cDepName as 使用部门,rds.cFree10 as 项目归属,
             rds.cFree7 as 闲置原因,rds.cFree1 as 完好情况,rds.cFree8 as 处置建议,
             null as  备注
      into tempdb..cssyywangcheng
      from RdRecord rd,RdRecords rds,Inventory i ,Warehouse w,InventoryClass IC ,Rd_Style rs ,
           ComputationUnit cu ,Department D
      where rd.id=rds.id and i.cinvcode=rds.cinvcode and rds.cdefine22='非上市' and ddate<=@end
             and w.cWhCode=rd.cwhcode and i.cInvCCode=ic.cInvCCode and rd.cRdCode=rs.cRdCode
             and (ic.cInvCCode LIKE '05%') and I.cComunitCode=cu.cComunitCode and D.cdepcode=rd.cdepcode
       GROUP BY  w.cwhname ,i.cInvStd,i.cinvcode,i.cinvname,i.cInvStd,rds.cfree9, cu.cComUnitName ,
                 rds.cDefine36 ,D.cDepName,rds.cFree10 , rds.cFree7 ,rds.cFree1,rds.cFree8 ,ic.cinvcname
     END


ELSE
     BEGIN
      print 'NEXT'
     END


go

hncswc

----------------------------------------------------------------------------------
If  exists(select name from ufsystem..sysobjects where name ='LT_table_name')
  Drop table  ufsystem..LT_table_name
CREATE TABLE ufsystem..LT_table_name
        (
        序号 char(10) NOT NULL,
        表名 char(50) NOT NULL
        )  ON [PRIMARY]

go
---------------------------------------------------------------------------------
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('01','工程物资存量汇总表_上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('02','工程物资存量汇总表_非上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('03','有财务账_上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('04','有财务账_非上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('05','工余料_上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('06','工余料_非上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('07','无合同物资_上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('08','无合同物资_非上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('09','运维汇总表_上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('10','运维汇总表_非上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('11','完好可调配运维明细表_上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('12','完好可调配运维明细表_非上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('13','运维物资盘点表_上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('14','运维物资盘点表_非上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('15','闲置资产汇总表_上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('16','闲置资产汇总表_非上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('17','闲置资产盘点表_上市')
INSERT INTO ufsystem..LT_table_name (序号,表名)
            VALUES ('18','闲置资产盘点表_非上市')

----------------------------------------------------------------------------------
If  exists(select name from ufsystem..sysobjects where name ='gcwzhzb_ss')
  Drop table  ufsystem..gcwzhzb_ss

CREATE TABLE ufsystem..gcwzhzb_ss
        (
        序号 char(10) NOT NULL,
        物资大类 char(100) NULL,
        [库存工程物资(财务账)] float(53) NULL,
        库存工余料 float(53) NULL,
        无合同物资 float(53) NULL,
        合计金额 float(53) NULL,
        备注 char(100) NULL,
        所属公司属性 char(10) null
        )  ON [PRIMARY]
GO

----------------------------------------------------
INSERT INTO  ufsystem..gcwzhzb_ss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (1,'有线传输',0,0,0,0,null,'上市')
INSERT INTO  ufsystem..gcwzhzb_ss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (2,'无线传输',0,0,0,0,null,'上市')
INSERT INTO  ufsystem..gcwzhzb_ss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (3,'电路交换',0,0,0,0,null,'上市')
INSERT INTO  ufsystem..gcwzhzb_ss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (4,'移动通信',0,0,0,0,null,'上市')
INSERT INTO  ufsystem..gcwzhzb_ss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (5,'数据通信',0,0,0,0,null,'上市')
INSERT INTO  ufsystem..gcwzhzb_ss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (6,'IT 设备',0,0,0,0,null,'上市')
INSERT INTO  ufsystem..gcwzhzb_ss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (7,'电源动力',0,0,0,0,null,'上市')
INSERT INTO  ufsystem..gcwzhzb_ss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (8,'仪器仪表',0,0,0,0,null,'上市')
INSERT INTO  ufsystem..gcwzhzb_ss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (9,'通用设备',0,0,0,0,null,'上市')
go
------------------------------------------------------------------------------------------------------------------
If  exists(select name from ufsystem..sysobjects where name ='gcwzhzb_fss')
  Drop table  ufsystem..gcwzhzb_fss

CREATE TABLE ufsystem..gcwzhzb_fss
        (
        序号 char(10) NOT NULL,
        物资大类 char(100) NULL,
        [库存工程物资(财务账)] float(53) NULL,
        库存工余料 float(53) NULL,
        无合同物资 float(53) NULL,
        合计金额 float(53) NULL,
        备注 char(100) NULL,
        所属公司属性 char(10) null
        )  ON [PRIMARY]
GO

------------------------------------------------------------------------------------------------------------------
INSERT INTO  ufsystem..gcwzhzb_fss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (1,'有线传输',0,0,0,0,null,'非上市')
INSERT INTO  ufsystem..gcwzhzb_fss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (2,'无线传输',0,0,0,0,null,'非上市')
INSERT INTO  ufsystem..gcwzhzb_fss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (3,'电路交换',0,0,0,0,null,'非上市')
INSERT INTO  ufsystem..gcwzhzb_fss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (4,'移动通信',0,0,0,0,null,'非上市')
INSERT INTO  ufsystem..gcwzhzb_fss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (5,'数据通信',0,0,0,0,null,'非上市')
INSERT INTO  ufsystem..gcwzhzb_fss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (6,'IT 设备',0,0,0,0,null,'非上市')
INSERT INTO  ufsystem..gcwzhzb_fss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (7,'电源动力',0,0,0,0,null,'非上市')
INSERT INTO  ufsystem..gcwzhzb_fss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (8,'仪器仪表',0,0,0,0,null,'非上市')
INSERT INTO  ufsystem..gcwzhzb_fss (序号,物资大类,[库存工程物资(财务账)],库存工余料,无合同物资,合计金额,备注,所属公司属性)
         VALUES (9,'通用设备',0,0,0,0,null,'非上市')
go
----------------------------------------------------------------------------------------
If  exists(select name from ufsystem..sysobjects where name ='ywwzhzb')
  Drop table  ufsystem..ywwzhzb

CREATE TABLE ufsystem..ywwzhzb
        (
        序号 char(4) null,
        存货大类 char(10) NULL,
        完好可用结存 float(53) NULL,
        技术落后结存 float(53) NULL,
        富余结存 float(53) NULL,
        修理可用结存 float(53) NULL,
        拟报废结存 float(53) NULL,
        合计 float(53) NULL,
        所属公司属性 char(10) null
        )  ON [PRIMARY]
go
-------------------------------------------------------------------------------------------

hncswc

INSERT INTO  ufsystem..ywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (1,'有线传输',0,0,0,0,0,0,'上市')
INSERT INTO  ufsystem..ywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (2,'无线传输',0,0,0,0,0,0,'上市')
INSERT INTO  ufsystem..ywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (3,'电路交换',0,0,0,0,0,0,'上市')
INSERT INTO  ufsystem..ywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (4,'移动通信',0,0,0,0,0,0,'上市')
INSERT INTO  ufsystem..ywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (5,'数据通信',0,0,0,0,0,0,'上市')
INSERT INTO  ufsystem..ywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (6,'IT 设备',0,0,0,0,0,0,'上市')
INSERT INTO  ufsystem..ywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (7,'电源动力',0,0,0,0,0,0,'上市')
INSERT INTO  ufsystem..ywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (8,'仪器仪表',0,0,0,0,0,0,'上市')
INSERT INTO  ufsystem..ywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (9,'通用设备',0,0,0,0,0,0,'上市')

go
---------------------------------------------------------------------------------------------------------
If  exists(select name from ufsystem..sysobjects where name ='fywwzhzb')
  Drop table  ufsystem..fywwzhzb

CREATE TABLE ufsystem..fywwzhzb
        (
        序号 char(4) null,
        存货大类 char(10) NULL,
        完好可用结存 float(53) NULL,
        技术落后结存 float(53) NULL,
        富余结存 float(53) NULL,
        修理可用结存 float(53) NULL,
        拟报废结存 float(53) NULL,
        合计 float(53) NULL,
        所属公司属性 char(10) null
        )  ON [PRIMARY]
go
-------------------------------------------------------------------------------------------


INSERT INTO  ufsystem..fywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (1,'有线传输',0,0,0,0,0,0,'非上市')
INSERT INTO  ufsystem..fywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (2,'无线传输',0,0,0,0,0,0,'非上市')
INSERT INTO  ufsystem..fywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (3,'电路交换',0,0,0,0,0,0,'非上市')
INSERT INTO  ufsystem..fywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (4,'移动通信',0,0,0,0,0,0,'非上市')
INSERT INTO  ufsystem..fywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (5,'数据通信',0,0,0,0,0,0,'非上市')
INSERT INTO  ufsystem..fywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (6,'IT 设备',0,0,0,0,0,0,'非上市')
INSERT INTO  ufsystem..fywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (7,'电源动力',0,0,0,0,0,0,'非上市')
INSERT INTO  ufsystem..fywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (8,'仪器仪表',0,0,0,0,0,0,'非上市')
INSERT INTO  ufsystem..fywwzhzb (序号,存货大类,完好可用结存,技术落后结存,富余结存,修理可用结存,拟报废结存,合计,所属公司属性)
         VALUES (9,'通用设备',0,0,0,0,0,0,'非上市')
go
--------------------------------------------------------------------------------------------
If  exists(select name from ufsystem..sysobjects where name ='xzzchzb')
  Drop table  ufsystem..xzzchzb

CREATE TABLE ufsystem..xzzchzb
        (
        序号 char(10) NULL,
        物资大类 char(20) NULL,
        [合计金额(人民币元)] float(53) NULL,
        备注 char(40) null
        )  ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------

INSERT INTO  ufsystem..xzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (1,'有线传输',0,null)
INSERT INTO  ufsystem..xzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (2,'无线传输',0,null)
INSERT INTO  ufsystem..xzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (3,'电路交换',0,null)
INSERT INTO  ufsystem..xzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (4,'移动通信',0,null)
INSERT INTO  ufsystem..xzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (5,'数据通信',0,null)
INSERT INTO  ufsystem..xzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (6,'IT 设备',0,null)
INSERT INTO  ufsystem..xzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (7,'电源动力',0,null)
INSERT INTO  ufsystem..xzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (8,'仪器仪表',0,null)
INSERT INTO  ufsystem..xzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (9,'通用设备',0,null)
go

------------------------------------------------------------------------------------------------
If  exists(select name from ufsystem..sysobjects where name ='fxzzchzb')
  Drop table  ufsystem..fxzzchzb

CREATE TABLE ufsystem..fxzzchzb
        (
        序号 char(10) NULL,
        物资大类 char(20) NULL,
        [合计金额(人民币元)] float(53) NULL,
        备注 char(40) null
        )  ON [PRIMARY]
GO
--------------------------------------------------------------------------------------------

INSERT INTO  ufsystem..fxzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (1,'有线传输',0,null)
INSERT INTO  ufsystem..fxzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (2,'无线传输',0,null)
INSERT INTO  ufsystem..fxzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (3,'电路交换',0,null)
INSERT INTO  ufsystem..fxzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (4,'移动通信',0,null)
INSERT INTO  ufsystem..fxzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (5,'数据通信',0,null)
INSERT INTO  ufsystem..fxzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (6,'IT 设备',0,null)
INSERT INTO  ufsystem..fxzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (7,'电源动力',0,null)
INSERT INTO  ufsystem..fxzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (8,'仪器仪表',0,null)
INSERT INTO  ufsystem..fxzzchzb (序号,物资大类,[合计金额(人民币元)],备注)
         VALUES (9,'通用设备',0,null)
go

==================================================================================================


with procedure  csyy_lt_wangcheng  tempdb..table_csyy

hncswc

论坛管理员有点不正常Ｕ８里的东西，非得要移动数据库版块里

topunion009

经典的SQL，很感谢，谢谢。。。。。。

switchlinda

学到东西，谢谢提供！！！

lxzsxy

学习了,谢谢

DSY-GH

这位楼主对SQL可谓炉火纯青了

zhua

有点搞不懂！学习中！

linshengtong

很强的东西，学习