虽然麻烦点,但是可以实现,而且数据是按周来记的,数据量应该不大
---------------------------------------------------------
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
----------------------------------------------------------
这里的参数必须是日期格式的
不知道我理解的有问题没?
CREATE TABLE SALEQUAN(ID INT,QUANTITY NUMERIC(8,2),SALESDATE DATETIME)
CREATE TABLE SALEQUAN1(ID INT,QUANTITY NUMERIC(8,2),SALESDATE DATETIME)
truncate table salequan
truncate table salequan1
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(1,150,'2005-4-3')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(2,105,'2005-4-10')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(3,220,'2005-4-17')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(4,55,'2005-4-24')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(10,50,'2005-5-1')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(12,50,'2005-5-8')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(13,15,'2005-5-15')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(14,22,'2005-5-22')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(15,555,'2005-5-29')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(21,50,'2005-6-5')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(22,15,'2005-6-12')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(23,22,'2005-6-19')
INSERT INTO SALEQUAN(ID,QUANTITY,SALESDATE)VALUES(24,555,'2005-6-26')
DECLARE sq_cursor CURSOR FOR
SELECT id, quantity, salesdate
FROM salequan
OPEN sq_cursor
FETCH NEXT FROM sq_cursor
INTO @sq_id, @sq_quan, @sq_date
WHILE @@FETCH_STATUS = 0
BEGIN
set @sq_date=dateadd(day,-2,@sq_date)
while datepart(dw,@sq_date)>1
begin
insert into salequan1(id,quantity,salesdate)values(@sq_id,@sq_quan/5,@sq_date)
set @sq_date=dateadd(day,-1,@sq_date)
end
FETCH NEXT FROM sq_cursor
INTO @sq_id, @sq_quan, @sq_date
end
CLOSE sq_cursor
DEALLOCATE sq_cursor
--获取数据
select sum(quantity) from salequan1 where datepart(day,salesdate)!>22 and salesdate!>'2005-06-22' group by datepart(m,salesdate)
--明细数据
select * from salequan1 where datepart(day,salesdate)!>22 and salesdate!>'2005-06-22' order by salesdate