假设有这样一个表a
bizdate salenumber
20090122 232
20090123 33
......
20100308 100
怎么统计每天到上一年该天的销售总和?
比如20100122的结果是sum(salenumber) where bizdate <= 20100122 and bizdate > 20090122;
20100123的结果是sum(salenumber) where bizdate <= 20100123 and bizdate > 20090123
谢谢
依次类推
bizdate salenumber
20090122 232
20090123 33
......
20100308 100
怎么统计每天到上一年该天的销售总和?
比如20100122的结果是sum(salenumber) where bizdate <= 20100122 and bizdate > 20090122;
20100123的结果是sum(salenumber) where bizdate <= 20100123 and bizdate > 20090123
谢谢
依次类推
sum(salenumber) where bizdate between dateadd(year,-1,getdate()) and getdate()
bizdate,
salenumber,
最近一年销售总和=(select sum(salenumber) from tb where bizdate between dateadd(yy,-1,t.bizdate) and t.bizdate)
from tb t
from a k cross apply
(
select SUM(salenumber) as sum_salenumber
from a
where bizdate between DATEADD(YEAR,-1,k.bizdate) and bizdate
) p