数码产品各店面分析

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

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