repeat a statement in a loop

hello,
i need to repeat the update statement for all days in the actual month
example:if i run today (19.09.2005) the job - it must run 19 times for
the value of
GETDATE() to GetDate() -19. How can be done this in a loop.
In the update statement is GetDate() used in the where condition and it must
be replaced with the values
in the first loop with GetDate() -1
in the second loop with GetDate() - 2
...
in the last GetDate()-19
The update statement looks like :
UPDATE [table1]
SET [OrderDate]=(
SELECT DISTINCT Top 1 OrderCreationDate
FROM table2
WHERE (SalesDocNr = table1.OrderNr)
)
WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
FROM table1 INNER JOIN
table2 ON table1.OrderNr = table2.SalesDocNr
WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
CONVERT(VarChar(10), GETDATE() , 104))
)
thanks
XavierWhile can use a WHILE loop or a cursor to get this done as you want, a
better approach is to use a single UPDATE statement which can update all the
rows.
Based on the sample code you posted, it is hard to work out such a solution,
so please refer to www.aspfaq.com/5006 and post relevant information for
others to reproduce your problem.
Anith|||For a looped approach try this for your basic loop. @.date should be used in
place of GETDATE() in your update query. Replace the PRINT statement with
your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
ONLY. You may want a better method than what I did.
Mike
DECLARE @.day int
DECLARE @.date datetime
SET @.day = DAY(GETDATE())
SET @.date = GETDATE()
SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
WHILE @.day > 0
BEGIN
PRINT @.date
SET @.day = @.day - 1
SET @.date = DATEADD(d, -1, @.date)
END
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it
> must
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||Please post DDL (CREATE TABLE), sample data (INSERTs) and show your required
results.
I'm certain it's possible to do what you want in a single UPDATE statement
without a loop. However, the UPDATE you have posted may not give reliable
results because you've used TOP without ORDER BY. For that reason it's trick
y
to guess what you intended by it (even though it may not always work as you
wanted).
I expect the solution will look like
:
UPDATE Table1
SET orderdate =
(
..
)
WHERE insertdate >= DATEADD(DAY,-19,CURRENT_TIMESTAMP)
AND insertdate <= CURRENT_TIMESTAMP ;
David Portas
SQL Server MVP
--
"Xavier" wrote:

> hello,
> i need to repeat the update statement for all days in the actual month
> example:if i run today (19.09.2005) the job - it must run 19 times for
> the value of
> GETDATE() to GetDate() -19. How can be done this in a loop.
> In the update statement is GetDate() used in the where condition and it mu
st
> be replaced with the values
> in the first loop with GetDate() -1
> in the second loop with GetDate() - 2
> ...
> in the last GetDate()-19
> The update statement looks like :
> UPDATE [table1]
> SET [OrderDate]=(
> SELECT DISTINCT Top 1 OrderCreationDate
> FROM table2
> WHERE (SalesDocNr = table1.OrderNr)
> )
> WHERE CONVERT(VARChar(10), table1.InsertDate, 104) = (
>
> SELECT TOP 1 CONVERT(VARChar(10), table1.InsertDate, 104) AS d
> FROM table1 INNER JOIN
> table2 ON table1.OrderNr = table2.SalesDocNr
> WHERE (CONVERT(VARChar(10), table1.InsertDate, 104) =
> CONVERT(VarChar(10), GETDATE() , 104))
> )
> thanks
> Xavier|||it works,
thanks Mike
"Mike Jansen" wrote:

> For a looped approach try this for your basic loop. @.date should be used
in
> place of GETDATE() in your update query. Replace the PRINT statement with
> your UPDATE query. I did a quick conversion of GETDATE() to get the DATE
> ONLY. You may want a better method than what I did.
> Mike
>
> DECLARE @.day int
> DECLARE @.date datetime
>
> SET @.day = DAY(GETDATE())
> SET @.date = GETDATE()
> SET @.date = CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime)
> WHILE @.day > 0
> BEGIN
> PRINT @.date
> SET @.day = @.day - 1
> SET @.date = DATEADD(d, -1, @.date)
> END
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:557E3B53-C67C-40BF-A62A-7674E0659CD3@.microsoft.com...
>
>|||> While can use a WHILE loop or a cursor to get this done as you want, a
> better approach is to use a single UPDATE statement which can update all
> the rows.
I don't think that's really possible without SQL Server 2005 (or perhaps a
_really_ messed up query in SQL 2000), in which case it would be something
like this as a base:
WITH DAYS(DayValue, Remaining) AS
(
SELECT
CAST(CONVERT(varchar(32), GETDATE(), 101) AS datetime) AS DayValue,
DAY(GETDATE()) - 1
UNION ALL
SELECT
DATEADD(d, -1, r.DayValue) AS DayValue,
r.Remaining - 1
FROM
DAYS r
WHERE
r.Remaining > 0
)
SELECT * FROM DAYS;
Instead of SELECT * FROM DAYS you'd do an UPDATE and JOIN to DAYS.
Mike