日期:2014-05-17 浏览次数:20460 次
create table #temp(cSOCode varchar(100),iQuantity int)
...............
while @i<7
begin
insert into #temp
select a.cSOCode,sum(b.iQuantity)
from SO_SOMain a
join SO_SODetails b on a.cSOCode = b.cSOCode
where dDate between @a and @b
group by a.cSOCode
set @a=dateadd(day,1,@b)
set @b=dateadd(day,7,@b)
set @i=@i+1
end
DECLARE @date DATETIME
SET @date = '2013-08-28'
;WITH num AS
(
SELECT id = 1 UNION ALL
SELECT id = 2 UNION ALL
SELECT id = 3 UNION ALL
SELECT id = 4 UNION ALL
SELECT id = 5 UNION ALL
SELECT id = 6
),
cte_week AS
(
select
id = '第'+LTRIM(b.id)+'周',
begindate = CASE b.id WHEN 1 THEN @date ELSE DATEADD(DAY, -(DATEPART(WEEKDAY, @date)-2), DATEADD(week, b.id-1, a.begindate)) END,
enddate = DATEADD(DAY, -(DATEPART(WEEKDAY, @date)-1)+1, DATEADD(week, b.id, a.begindate))
FROM (SELECT begindate = @date) a