Loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Loop

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-05-15 : 07:22:25

mcenda writes "Any Suggestions why the below code doesn't work ???
Its meant to loop through the dates, 7 days apart ie a week and put the info for each week in a temptable
then output the contents of the temp table

Set @week = 1
Set @date1 = '05/10/02'
set @target = '05/09/03'
set @date2 = dateadd(day,7,@date1)
set @week = 1

while @date1 < @target

begin
Insert Into #TempTable(amt, weeknum, qty)
Select sum(amounta), @week,sum(docqty)
From Transactions
Where comp = @comp And mod = @mod And code = @code And docdate between @date1 and @date2

set @temp = @date2
set @date2 = dateadd(day,7,@date2)
set @date1 = @temp
set @week = @week + 1
end

Select amt, weeknum, qty from #TempTable"

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-15 : 07:39:38

This query can be done without a loop (I think), but I have to run, back in about 7 hours.

If noone has it solved by then, I'll give it a try.

Sam

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-15 : 08:18:05

There may be a better solution out there, but here is my shot in the dark (untested since no ddl or sample data was provided...)


select
    weeks.weeknum as [week],
    sum(t.amounta) as amt,
    sum(t.docqty) as qty
from
   (select
        dateadd(dd,n,@date1) as weekstart,
        n/7 + 1 as weeknum
    from
        tally
    where
        n%7 = 0) as weeks
    inner join Transactions t
        on t.docdate>=weeks.weekstart and
           t.docdate<weeks.weeksdtart + 7
where
    comp = @comp and
    mod = @mod and
    code = @code
    docdate < @target + 1
group by
    weeks.weeknum

For this solution you will need a 'Tally' table (create table tally (n int primary key)) populated with (at least) positive ints from 0 to datediff(dd,@date1,@target).

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources