Update a table with dates split evenly over 6months period

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

Transact-SQL (2000)

Update a table with dates split evenly over 6months period

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-14 : 08:03:23

smita writes "Hi There,

I need help/suggestion regarding the business requirement we have:

We need to do a portfolio review of our customers based on their report date and we have about 900000 customers records who do not have any report date so we need to do a one time update of the table for all 900000 customers and populate the report date with the current date evenly split over next 6 months(180 days).

e.g: Total Customers records/180 days = customer per day
900000/180 = 5000/per day so the report date for 5000 records will be GETDATE()
this should be in a loop and we need to update every 5000 customer records with dates (GETDATE()+1) till we reach the 900k limit.
5000 records report date is Current Date
5000 records report date is tommorrow's date
5000 records report date will be day after tommorrows date and so on till 900k records over 6 months period.

I need to do this through a stored proc and then execute this as a one time batch job. I tried to do it using while loop but was not successful so far. Any help is greatly appreciated."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-14 : 08:13:29

This should work:

DECLARE @a int, @d datetime
SELECT @a=0, @d=getdate()
UPDATE myTable
SET ReportDate=DateAdd(day, @a%180, @d), @a=@a+1
WHERE ReportDate IS Null

The @a variable is a counter, it increments by 1 for each row updated. The DateAdd function will perform a modulo on %a to get a remainder between 0 and 179, and use it to set the ReportDate value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources