|
楼主 |
发表于 2013-3-9 08:18
|
显示全部楼层
- select case when left(Fnumber,5)='01.03' then '双金属片(进口)'
- when left(Fnumber,5)='01.02' then '基座(进口)'
- when left(Fnumber,5)='01.04' then '固定接点板(进口)'
- when left(Fnumber,5)='01.05' then '热敏端子(进口)'
- when left(Fnumber,5)='01.06' then '连接线(进口)'
- when left(Fnumber,5)='01.07' then '螺丝(进口)'
- when left(Fnumber,5)='01.01' then '电热丝(进口)'
- when left(Fnumber,5)='01.08' then '螺母(进口)'
- when left(Fnumber,5)='01.09' then '半制品(进口)'
- when left(Fnumber,5)='01.10' then '弹簧(进口)'
- when left(Fnumber,5)='01.11' then '盖子(进口)'
- when left(Fnumber,5)='01.15' then '其他(进口)'
- when left(Fnumber,5)='02.01' then '电热丝(国产)'
- when left(Fnumber,5)='02.02' then '基座(国产)'
- when left(Fnumber,5)='02.03' then '固定接点板(国产)'
- when left(Fnumber,5)='02.04' then '热敏端子(国产)'
- when left(Fnumber,5)='02.05' then '连接线(国产)'
- when left(Fnumber,5)='02.06' then '弹簧(国产)'
- when left(Fnumber,5)='02.07' then '螺丝、垫片(国产)'
- when left(Fnumber,5)='02.08' then '盖子(国产)'
- when left(Fnumber,5)='02.09' then '其他(国产)'
- when left(Fnumber,5)='02.10' then '双金属片(国产)'
- when left(Fnumber,5)='03.01' then '双金属片(自制)'
- when left(Fnumber,5)='03.04' then '连接线(自制)'
- when left(Fnumber,5)='03.03' then '固定接点板(自制)'
- when left(Fnumber,5)='04.01' then '电热丝(委外)'
- else ' '
- end as Fname,
-
-
- case when (grouping(Fnumber)=1) then '小计'
- else isnull(Fnumber,'Unknown')
- end as Fnumber,
- case when (grouping(Fmodel)=1) then ' '
- else isnull(Fmodel,'Unknown')
- end as Fmodel,
- sum(BegQty) as BegQty,sum(InQty) as InQty,sum(OutQty) as OutQty,sum(EndQty) as EndQty
- from
- (select icinvbal.fitemid,t_IcItemCore.Fnumber,t_IcItemCore.Fname,t_IcItemCore.Fmodel,icinvbal.fyear,icinvbal.fperiod,icinvbal.fbegqty as BegQty,isnull(BillInQry.InQty,0) as InQty,isnull(BillOutQry.OutQty,0) as OutQty,
- (icinvbal.fbegqty+isnull(BillInQry.InQty,0)-isnull(BillOutQry.OutQty,0)) as EndQty,left(t_IcItemCore.Fnumber,5) as Ftype,icinvbal.fstockid
- from icinvbal full join
- (select icstockbillentry.fitemid as Fitemid,sum(icstockbillentry.fqty) as InQty
- from icstockbillentry inner join icstockbill on icstockbillentry.finterid=icstockbill.finterid
- where icstockbill.fdate>'2013-01-01' and icstockbill.fdate<='2013-01-31'
- and icstockbill.fdcstockid='212'
- group by icstockbillentry.fitemid) as BillInQry on BillInQry.fitemid=icinvbal.fitemid
- full join
- (select icstockbillentry.fitemid as Fitemid,sum(icstockbillentry.fqty) as OutQty
- from icstockbillentry inner join icstockbill on icstockbillentry.finterid=icstockbill.finterid
- where icstockbill.fdate>'2013-01-01' and icstockbill.fdate<='2013-01-31'
- and icstockbill.fscstockid='212'
- group by icstockbillentry.fitemid) as BillOutQry on BillOutQry.Fitemid=icinvbal.fitemid
- inner join t_IcItemCore on icinvbal.fitemid=t_IcItemCore.FitemId
- where icinvbal.fyear='2013' and icinvbal.fperiod='1' and icinvbal.fstockid='212'
- ) as MainTbl
- group by Ftype,Fnumber,Fmodel with rollup
- having grouping(Fnumber)=1 or grouping(Fmodel)=0
复制代码 代码奉上。这段代码要放到vba的 sub过程中,如何处理? 我用换行符,提示换行符太多! |
|