Nice example Eric!

Nice example Eric!  ;-))

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net



Yes there are Loops in SQL Server, but Celko doesn't like them.
Here's a quick example...

WHILE @CelkoIsBeingDifficult
BEGIN --LOOP
TRY
SET @Him = @Straight
CATCH
@Him = @DoingItAgainLater
END TRY
END --Less LOOP


Here's one way to do it...

SET @EndMonth = MONTH(@EndDate)
SET @Month = MONTH(@StartDate)

WHILE @Month <= @EndMonth
BEGIN
--Process data for the month

SET @Month = @Month + 1
END

-Eric Isaacs

The example I was referring to had nothing to do with cursors.

The example I was referring to had nothing to do with cursors.  It dealt
with your recurrent cantankerousness in a rather funny way.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

How to loop a month at a time between two dates

In a SQL 2005 SP that has two parameters a start and end date.

How can you loop through these dates a month at a time to process data
for that month period?

We don't have loops in SQL; this is a declarative language, so we doit all in

We don't have loops in SQL; this is a declarative language, so we do
it all in one statement on the entire set.  If you can explain what

SELECT ..
FROM ..
WHERE something_date BETWEEN @vague_start_date AND@vague_end_date
AND..;

Yes there are Loops in SQL Server, but Celko doesn't like them.

Yes there are Loops in SQL Server, but Celko doesn't like them.
Here's a quick example...

WHILE @CelkoIsBeingDifficult
BEGIN --LOOP
TRY
SET @Him =3D @Straight
CATCH
@Him =3D @DoingItAgainLater
END TRY
END --Less LOOP


Here's one way to do it...

SET @EndMonth =3D MONTH(@EndDate)
SET @Month =3D MONTH(@StartDate)

WHILE @Month <=3D @EndMonth
BEGIN
--Process data for the month

SET @Month =3D @Month + 1
END

-Eric Isaacs

Seriously, Celko is right from the standpoint that you should beavoiding loops

Seriously, Celko is right from the standpoint that you should be
avoiding loops in your SQL.  Just because they can be done, doesn't
mean they should be done.  You should always try to process the data
in a single bulk SQL statement like he suggested.  If that isn't
possible, a loop is possible.

-Eric Isaacs

After 30 years with SQL, I think it is always possible to avoidloops.

After 30 years with SQL, I think it is always possible to avoid
loops.

Actually, I knew it was always possible because of some theorem that
showed a push-down stack automata (procedural language) was
computationally equivalent to primitive recursive functions
(declarative language).  But it was not worth it.  Hey, my first
Masters degree was Math and my second was Comp Sci!

After we got CASE expressions, the OLAP functions and windows clause
on aggregates, the only problem I can think of for doing in loops is a
return the first answer you find that is acceptable (i.e. cost < k)
without looking further.

Do you really think that will run faster and port easier than a singelSELECT

Do you really think that will run faster and port easier than a singel
SELECT or UPDATE statement?  Of course not!

Let's see if Robin9876 will actually post his problem so we can code
it...

How to loop a month at a time between two dates

t

Ok, I have changed the scenario to a sales ordering system the data
requested may seem strange but is the type of information required.

For each Sales Person, in user defined date range, by month I need the
number of orders placed, number of orders invoiced and at the end of
each month period how many customers last order was yet to be
despatched.

Robin,Can you include more details about the tables involved?

Robin,

Can you include more details about the tables involved?  Maybe some
CREATE scripts for those tables and definitions of the relationships
between the tables?  Otherwise we'll just be guessing what your schema
looks like.

Thanks,

-Eric Isaacs

Please post DDL, so that people do not have to guess what the

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules.  Sample data is also a good idea,
along with clear specifications.  It is very hard to debug code when
you do not let us see it.  If you want to learn how to ask a question
on a Newsgroup, look at:  http://www.catb.org/~esr/faqs/smart-questions.html