*/declare @rq varchar(10)
set @rq=convert(char(7),dateadd(month,-datepart(month,getdate())+1,getdate()),21)+'-01'declare @bl decimal(16,4)
set @bl=0
select a.splb,a.hzflchina,b.bm,d.month as yf,@bl as xsje,@bl as ml,@bl as mll into #info
from #splb a
cross join
(select bm from #bm)b
cross join
(
select [Month]=convert(char(7),dateadd(month,0,@rq),21) union all select [Month]=convert(char(7),dateadd(month,1,@rq),21) union all
select [Month]=convert(char(7),datadd(month,2,@rq),21) union all select [Month]=convert(char(7),dateadd(month,3,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,4,@rq),21) union all select [Month]=convert(char(7),dateadd(month,5,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,6,@rq),21) union all select [Month]=convert(char(7),dateadd(month,7,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,8,@rq),21) union all select [Month]=convert(char(7),dateadd(month,9,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,10,@rq),21) union all select [Month]=convert(char(7),dateadd(month,11,@rq),21)
)d
order by a.splb,b.bm,d.month --select * from #info drop table #info--取得某年的营业数据 --drop table #xs
select a.bm,left(a.rq,7) as rq,d.splb,sum(b.hsje) as xsje,sum(b.ml) as ml,
mll=case
when sum(b.hsje)=0 then 0
else sum(b.ml)/sum(b.hsje)
end
into #xs
from pf_ckhz a(nolock),pf_ckmx b(nolock),spkfk c(nolock),#splb d(nolock)
where a.djbh=b.djbh and b.spid=c.spid and left(c.spbh,2)=d.splb
group by d.splb,a.bm,left(a.rq,7)
order by d.splb,a.bm,left(a.rq,7)
--分段更新
--更新基本表的营业额,毛利,毛利率 --分批更新
----更新类型(销售金额)select * from #info
update a set a.xsje=b.xsje,a.ml=b.ml,a.mll=b.mll
from #info a(nolock),#xs b(nolock)
where a.splb=b.splb and a.bm=b.bm and a.yf=b.rq --基本数据更新完成,开始组织显示数据
declare @s nvarchar(4000)
set @s=N'
select bm=case when grouping(bm)=1 and grouping(yf)=1 then ''总计''
when yf=convert(varchar(4),getdate(),23)+''-01'' then bm
else ''''
end,
yf=case when grouping(bm)=0 and grouping(yf)=1 then ''合计''
else yf
end'
select @s=@s+','+char(13)+quotename(cast(splb as varchar)+N'_xsje')
+N'=sum(case splb when '+cast(char(39)+splb+char(39) as varchar)+N' then xsje end)'
+N','+char(13)+quotename(cast(splb as varchar)+N'_ml')
+N'=sum(case splb when '+cast(char(39)+splb+char(39) as varchar)+N' then ml end)'
+N','+char(13)+quotename(cast(splb as varchar)+N'_mll')
+N'=sum(case splb when '+cast(char(39)+splb+char(39) as varchar)+N' then mll end)'
from #info
group by splb
set @s=@s+N',sum(isnull(xsje,0)) as xsje,sum(isnull(ml,0)) as ml,sum(isnull(ml,0))/sum(isnull(xsje,0)) as mll '
exec(@s+N'from #info group by bm,yf with rollup')
set @rq=convert(char(7),dateadd(month,-datepart(month,getdate())+1,getdate()),21)+'-01'declare @bl decimal(16,4)
set @bl=0
select a.splb,a.hzflchina,b.bm,d.month as yf,@bl as xsje,@bl as ml,@bl as mll into #info
from #splb a
cross join
(select bm from #bm)b
cross join
(
select [Month]=convert(char(7),dateadd(month,0,@rq),21) union all select [Month]=convert(char(7),dateadd(month,1,@rq),21) union all
select [Month]=convert(char(7),datadd(month,2,@rq),21) union all select [Month]=convert(char(7),dateadd(month,3,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,4,@rq),21) union all select [Month]=convert(char(7),dateadd(month,5,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,6,@rq),21) union all select [Month]=convert(char(7),dateadd(month,7,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,8,@rq),21) union all select [Month]=convert(char(7),dateadd(month,9,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,10,@rq),21) union all select [Month]=convert(char(7),dateadd(month,11,@rq),21)
)d
order by a.splb,b.bm,d.month --select * from #info drop table #info--取得某年的营业数据 --drop table #xs
select a.bm,left(a.rq,7) as rq,d.splb,sum(b.hsje) as xsje,sum(b.ml) as ml,
mll=case
when sum(b.hsje)=0 then 0
else sum(b.ml)/sum(b.hsje)
end
into #xs
from pf_ckhz a(nolock),pf_ckmx b(nolock),spkfk c(nolock),#splb d(nolock)
where a.djbh=b.djbh and b.spid=c.spid and left(c.spbh,2)=d.splb
group by d.splb,a.bm,left(a.rq,7)
order by d.splb,a.bm,left(a.rq,7)
--分段更新
--更新基本表的营业额,毛利,毛利率 --分批更新
----更新类型(销售金额)select * from #info
update a set a.xsje=b.xsje,a.ml=b.ml,a.mll=b.mll
from #info a(nolock),#xs b(nolock)
where a.splb=b.splb and a.bm=b.bm and a.yf=b.rq --基本数据更新完成,开始组织显示数据
declare @s nvarchar(4000)
set @s=N'
select bm=case when grouping(bm)=1 and grouping(yf)=1 then ''总计''
when yf=convert(varchar(4),getdate(),23)+''-01'' then bm
else ''''
end,
yf=case when grouping(bm)=0 and grouping(yf)=1 then ''合计''
else yf
end'
select @s=@s+','+char(13)+quotename(cast(splb as varchar)+N'_xsje')
+N'=sum(case splb when '+cast(char(39)+splb+char(39) as varchar)+N' then xsje end)'
+N','+char(13)+quotename(cast(splb as varchar)+N'_ml')
+N'=sum(case splb when '+cast(char(39)+splb+char(39) as varchar)+N' then ml end)'
+N','+char(13)+quotename(cast(splb as varchar)+N'_mll')
+N'=sum(case splb when '+cast(char(39)+splb+char(39) as varchar)+N' then mll end)'
from #info
group by splb
set @s=@s+N',sum(isnull(xsje,0)) as xsje,sum(isnull(ml,0)) as ml,sum(isnull(ml,0))/sum(isnull(xsje,0)) as mll '
exec(@s+N'from #info group by bm,yf with rollup')
看一下颜色就知道了 dateadd
select [Month]=convert(char(7),dateadd(month,0,@rq),21) union all select [Month]=convert(char(7),dateadd(month,1,@rq),21) union all
select [Month]=convert(char(7),datadd(month,2,@rq),21) union all select [Month]=convert(char(7),dateadd(month,3,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,4,@rq),21) union all select [Month]=convert(char(7),dateadd(month,5,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,6,@rq),21) union all select [Month]=convert(char(7),dateadd(month,7,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,8,@rq),21) union all select [Month]=convert(char(7),dateadd(month,9,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,10,@rq),21) union all select [Month]=convert(char(7),dateadd(month,11,@rq),21)
看一下颜色就知道了 dateadd
select [Month]=convert(char(7),dateadd(month,0,@rq),21) union all select [Month]=convert(char(7),dateadd(month,1,@rq),21) union all
select [Month]=convert(char(7),datadd(month,2,@rq),21) union all select [Month]=convert(char(7),dateadd(month,3,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,4,@rq),21) union all select [Month]=convert(char(7),dateadd(month,5,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,6,@rq),21) union all select [Month]=convert(char(7),dateadd(month,7,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,8,@rq),21) union all select [Month]=convert(char(7),dateadd(month,9,@rq),21) union all
select [Month]=convert(char(7),dateadd(month,10,@rq),21) union all select [Month]=convert(char(7),dateadd(month,11,@rq),21)