數碼產品各店面分析

//數碼產品各店面分析報表//

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