數(shù)碼產(chǎn)品各店面分析

//數(shù)碼產(chǎn)品各店面分析報(bào)表//

創(chuàng)新互聯(lián)主營佳縣網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,重慶APP開發(fā),佳縣h5微信小程序定制開發(fā)搭建,佳縣網(wǎng)站營銷推廣歡迎佳縣等地區(qū)企業(yè)咨詢

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),[數(shù)量] 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),[數(shù)量] int,[含稅金額] decimal(18,2),[毛利] decimal(18,2))
create table #table22 ([商品名稱] varchar(100),[部門名稱] varchar(100),[數(shù)量]int,[含稅金額] decimal(18,2),[毛利] decimal(18,2))
   insert into #table21
select [商品名稱] ,[部門名稱],sum([數(shù)量]),sum([含稅金額]) [含稅金額],sum([不含稅金額])-sum([成本]) from #temp12
group by [商品名稱] ,[部門名稱]
insert into #table22
select [商品名稱],[部門名稱],sum([數(shù)量]),sum([含稅金額]),sum([毛利]) from #table21 group by [商品名稱],[部門名稱]

create table #table1 ([商品名稱] varchar(100),[部門名稱] varchar(100),[數(shù)量] int,[含稅金額] decimal(18,2),[毛利] decimal(18,2))

    set @sql1='insert into #table1 '
  set @sql1= @sql1+ ' select [商品名稱],[部門名稱],[數(shù)量],[含稅金額],[毛利] from #table22 '
exec (@Sql1)
set @sql1='insert into #table1 '
  set @sql1= @sql1+ ' select ''小計(jì)'',[部門名稱],sum([數(shù)量]),sum([含稅金額]),sum([毛利]) from #table22 '
  set @sql1= @sql1+ ' GROUP BY [部門名稱] '
-- exec (@Sql1)
--set @sql1='insert into #table1 '
--  set @sql1= @sql1+ ' select ''總計(jì)'',[部門名稱],sum([含稅金額]),sum([毛利]) from #table22 '
--  set @sql1= @sql1+ '   GROUP BY [部門名稱] '
exec (@Sql1)

    set @sql='select [商品名稱] 商品名稱,'
    select @sql= @sql+'sum(case [部門名稱] when '''+[部門名稱]+'''
     then         數(shù)量 else 0 end) as ['+[部門名稱]+'|數(shù)量],
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(數(shù)量) [合計(jì)數(shù)量],sum(含稅金額) [合計(jì)含稅金額],sum(毛利) [合計(jì)毛利]'+'from #table1 group by [商品名稱] '

      exec (@sql)

drop table #table21,#table22,#temp12,#table1
END

分享標(biāo)題：數(shù)碼產(chǎn)品各店面分析
文章路徑：http://muchs.cn/article20/ipggco.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)建站、關(guān)鍵詞優(yōu)化、定制開發(fā)、企業(yè)建站、做網(wǎng)站、用戶體驗(yàn)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容