数码产品各店面分析

//数码产品各店面分析报表//

BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
create table #temp12([日期] datetime,[部门代码] varchar(100),[部门名称]  varchar(100), [商品代码]  varchar(100),
[商品名称]  varchar(100),[品牌] varchar(100),[数量] int,[含税金额] decimal(10,2),
[不含税金额] decimal(10,2),[成本] decimal(10,2))

insert into #temp12
select t0.F_PBKA_DATE,
t12.fnumber,t11.fname fname1,t2.FNUMBER,t3.FNAME,t2.F_PBKA_MOBILE_BRANK,
case when t0.FBILLTYPEID='57faf492b10d31' then sum(isnull(t1.FQtY,0))
 when  t0.FBILLTYPEID='580778655b6d7d' then  -sum(isnull(t1.FQTY,0))  end FQTY,
case when t0.FBILLTYPEID='57faf492b10d31' then sum(isnull(t1.FSDJAMOUNT ,0))
 when  t0.FBILLTYPEID='580778655b6d7d' then  -sum(isnull(t1.FSDJAMOUNT ,0))  end 含税金额,
 case when t0.FBILLTYPEID='57faf492b10d31' then sum(isnull(t1.F_PBKA_BHSAMOUNT ,0))
 when  t0.FBILLTYPEID='580778655b6d7d' then  -sum(isnull(t1.F_PBKA_BHSAMOUNT ,0))  end 不含税金额,0
from PBKA_t_BillHead t0
inner join PBKA_t_GoodsDetail t1 on t0.fid=t1.fid
left join t_bd_material t2 on t1.FMATERIALID=t2.FMATERIALID
inner join T_BD_MATERIAL_L t3 on t1.FMATERIALID=t3.FMATERIALID and t3.FLOCALEID=2052
inner join t_bd_material t9 on t3.FMATERIALID=t9.FMATERIALID
join t_bd_department_l t11 on t0.FOLDDEPARTMENT=t11.FDEPTID
join t_bd_department t12 on t11.FDEPTID=t12.FDEPTID
where  t12.fnumber like '203%'and t9.FNUMBER like '03%'
and (convert(varchar(10),t0.F_PBKA_DATE,21)>='2017-03-01'
and convert(varchar(10),t0.F_PBKA_DATE,21)<='2017-03-31')
 
group by  t0.F_PBKA_DATE,t2.FNUMBER,t11.fname,t12.fnumber,t3.FNAME,t2.F_PBKA_MOBILE_BRANK,t0.FBILLTYPEID order by 1

insert into #temp12
select t0.FDATE,t12.FNUMBER FNUMBER,t11.fname fname1, t9.FNUMBER,t5.FNAME,t9.F_PBKA_MOBILE_BRANK,0,0,0,sum(t2.FCOSTAMOUNT)
 From T_SAL_OUTSTOCK t0
      join  T_SAL_OUTSTOCKENTRY t1 on t0.FID =t1.fid
      join T_SAL_OUTSTOCKENTRY_F t2 on t1.FENTRYID =t2.FENTRYID
      join t_bd_material_l t5 on t1.FMATERIALID =t5.FMATERIALID and FLOCALEID=2052
      join t_bd_material t9 on t5.FMATERIALID=t9.FMATERIALID
     join  T_SAL_OUTSTOCKENTRY_R t10 on t2.FENTRYID=t10.FENTRYID
     join t_bd_department_l t11 on t0.FOLDDEPARTMENT=t11.FDEPTID
                join t_bd_department t12 on t11.FDEPTID=t12.FDEPTID
      where  (convert(varchar(10),t0.fdate,21)>='2017-03-01'
          and convert(varchar(10),t0.fdate,21)<='2017-03-31') 
       and t10.FSRCTYPE ='PBKA_XSKD'  and t9.fnumber like '03%'   and t12.fnumber like '203%'
         group by t0.FDATE,t12.FNUMBER,t11.fname, t9.FNUMBER,t5.FNAME,t9.F_PBKA_MOBILE_BRANK

insert into #temp12
select t0.fdate,t12.FNUMBER FNUMBER,t11.fname fname1, t9.FNUMBER,t5.FNAME,t9.F_PBKA_MOBILE_BRANK,0,0,0,-sum(t2.FCOSTAMOUNT)
 From T_SAL_RETURNSTOCK t0
      join  T_SAL_RETURNSTOCKENTRY t1 on t0.FID =t1.fid
      join T_SAL_RETURNSTOCKENTRY_F t2 on t1.FENTRYID =t2.FENTRYID
      join t_bd_material_l t5 on t1.FMATERIALID =t5.FMATERIALID and FLOCALEID=2052
      join t_bd_material t9 on t5.FMATERIALID=t9.FMATERIALID
     join  T_SAL_RETURNSTOCKENTRY_R t10 on t2.FENTRYID=t10.FENTRYID
     join t_bd_department_l t11 on t0.FOLDDEPARTMENT=t11.FDEPTID
                join t_bd_department t12 on t11.FDEPTID=t12.FDEPTID
      where  (convert(varchar(10),t0.fdate,21)>='2017-03-01'
          and convert(varchar(10),t0.fdate,21)<='2017-03-31')
       and t10.FSRCBILLTYPEID ='PBKA_XSKD'   and t12.fnumber like '203%' and t9.fnumber like '03%'
       group by t0.fdate,t12.FNUMBER,t11.fname, t9.FNUMBER,t5.FNAME,t9.F_PBKA_MOBILE_BRANK
 declare @sql varchar(8000)
 select @sql=''
 declare @sql1  varchar(8000)
 select @sql1=''
 create table #table21 ([商品名称]  varchar(100),[部门名称] varchar(100),[数量] int,[含税金额] decimal(18,2),[毛利] decimal(18,2))
 create table #table22 ([商品名称]  varchar(100),[部门名称] varchar(100),[数量]int,[含税金额] decimal(18,2),[毛利] decimal(18,2))
   insert into #table21
select  [商品名称] ,[部门名称],sum([数量]),sum([含税金额]) [含税金额],sum([不含税金额])-sum([成本])  from #temp12
group by  [商品名称] ,[部门名称]
insert into #table22
select [商品名称],[部门名称],sum([数量]),sum([含税金额]),sum([毛利]) from #table21 group by [商品名称],[部门名称]

  create table #table1 ([商品名称] varchar(100),[部门名称] varchar(100),[数量] int,[含税金额] decimal(18,2),[毛利] decimal(18,2))

    set @sql1='insert into #table1 '
  set @sql1= @sql1+ ' select [商品名称],[部门名称],[数量],[含税金额],[毛利] from  #table22 '
 exec (@Sql1)
  set @sql1='insert into #table1 '
  set @sql1= @sql1+ '  select ''小计'',[部门名称],sum([数量]),sum([含税金额]),sum([毛利])  from  #table22 '
  set @sql1= @sql1+ ' GROUP BY [部门名称] '
-- exec (@Sql1)
--set @sql1='insert into #table1 '
--  set @sql1= @sql1+ '  select ''总计'',[部门名称],sum([含税金额]),sum([毛利]) from  #table22 '
--  set @sql1= @sql1+ '   GROUP BY [部门名称] '
 exec (@Sql1)

    set @sql='select [商品名称] 商品名称,'
    select @sql= @sql+'sum(case [部门名称] when '''+[部门名称]+'''
     then         数量 else 0 end) as ['+[部门名称]+'|数量],
  sum(case [部门名称] when '''+[部门名称]+'''
     then         含税金额 else 0 end) as ['+[部门名称]+'|含税金额],
  sum(case [部门名称] when '''+[部门名称]+'''
     then         毛利 else 0 end) as ['+[部门名称]+'|毛利],'
  from (select distinct [部门名称] from #table1) as a
  select @sql=left(@sql,len(@sql)-1)+' ,sum(数量) [合计数量],sum(含税金额) [合计含税金额],sum(毛利) [合计毛利]'+'from #table1 group by [商品名称] '
 
      exec (@sql)
 
drop table #table21,#table22,#temp12,#table1
END