哈,甜老师这么早呢!!
甜老师,我用sql语句写同比、环比统计,分别写会了,但是要放在一起却不知道怎么放:
环比:
select c.消费门店 as 消费门店, convert(varchar(10), c.消费时间, 23) as 消费时间, c.销售金额 as 销售金额, case when d.销售金额 is null or d.销售金额=0 then '无穷大' else cast(cast((isnull(c.销售金额, 0)-isnull(d.销售金额,0))*100/isnull(d.销售金额, 0) as decimal(10,2)) as varchar(50))+'%' end as 环比增长 from 门店日销售 c left join
(select a.消费门店 as 消费门店, a.销售金额 as 销售金额, a.消费时间 as ls消费时间, b.消费时间 as 消费时间 from 门店日销售 a join 门店日销售 b on a.消费时间=DateAdd(month, -1, b.消费时间) and a.消费门店=b.消费门店) d
on c.消费时间=d.消费时间 and c.消费门店=d.消费门店
同比:
select c.消费门店 as 消费门店, convert(varchar(10), c.消费时间, 23) as 消费时间, c.销售金额 as 销售金额, case when d.销售金额 is null or d.销售金额=0 then '无穷大' else cast(cast((isnull(c.销售金额, 0)-isnull(d.销售金额,0))*100/isnull(d.销售金额, 0) as decimal(10,2)) as varchar(50))+'%' end as 同比增长 from 门店日销售 c left join
(select a.消费门店 as 消费门店, a.销售金额 as 销售金额, a.消费时间 as ls消费时间, b.消费时间 as 消费时间 from 门店日销售 a join 门店日销售 b on a.消费时间=DateAdd(year, -1, b.消费时间) and a.消费门店=b.消费门店) d
on c.消费时间=d.消费时间 and c.消费门店=d.消费门店
现在是分别两张视图,想像狐表的统计放在一张里:
如字段:
消费时间 消费门店 销售金额 环比 同比
搞半天没搞出来。。。
[此贴子已经被作者于2014-8-17 10:20:15编辑过]