我要求一个算法,是每月销量的对比,比如:输入6月9日,输出的是6月1号到9号的、5月1号到9号、4月1号到9号...销量比对,但数据库中的销量是周销量,也就是说非整周的销量,要除以5(表示去掉周六周日),乘以天数。。。类似输入6月30日等等
表的结构:salesid quantity salesdate
... ... ....
22 50 2005-6-5
23 15 2005-6-12
24 22 2005-6-19
25 555 2005-6-26
26 321 2005-7-3
... ... ....
salesdate为周日,要sum(quanity) 就是要求月初到输入日期那天的销量,即每个月同段时间销量比对,这里只举了一个月
另外如果是输入6月30号,则要把6-5日的quantity/5*3 把7月3日的quantitiy/5*4 以及12、19,26号的quantity 相加,则为6月的销量 注:6月1日2日3日为周三周四周五,6月27,28,29,30为周一到周四
---------------------------------------------------------
select month+'01至'+month+cast(day(参数) as varchar),sum(quantity) as quantity from
(select left(convert(varchar(10),salesdate,120),8) as month,
right(convert(varchar(10),salesdate,120),2) as day, from
(
select dateadd(day,-6,salesdate) as salesdate,quantity/5 as quantity from 表
union all
select dateadd(day,-5,salesdate) as salesdate,quantity/5 as quantity from 表
union all
select dateadd(day,-4,salesdate) as salesdate,quantity/5 as quantity from 表
union all
select dateadd(day,-3,salesdate) as salesdate,quantity/5 as quantity from 表
union all
select dateadd(day,-2,salesdate) as salesdate,quantity/5 as quantity from 表
)t1
)t2
where cast(day as int)<=day(参数)
group by month
----------------------------------------------------------