How to propagate date table automatically?

[dbrewerton]

I have a table with a series of dates. What I want to do is take the very last date in the table and propagate the next 52 weeks. I don't have any clues as to how to do this. I know I'll need a loop structure to count 52 loops but where I'm lost is how to do the loop function.

My table is very simple:

ID (Autonum) | Datevalue (smalldatetime)

is loop count = 52?

If yes quit

if no then begin loop

// Grab last Datevalue ID and then add 7 days to that date
 
SELECT MAX 1 id, Datevalue AS newdateval from Table ORDER BY id desc
 
newdateval = DateAdd(day,7,newdateval)
 
// Insert the new date
 
INSERT INTO Table ([Datevalue]) values ([newdateval])

repeat loop

repeat grabbing last entry until we cycle through 52 iterations (this is where I'm confused) so I need help with the loop, any ideas?

[ck9663]

I got this snippet of code to generate a sequential series of number. You can use this technique and modify it into an INSERT statement.

Happy Coding!!!

~~ CK

[dbrewerton]

Very cool, that helped. Now, I have some further work to do that perhaps maybe someone here can help me do. What I want to do now is add two more fields to this query: the month number which is always sequential and a person id from 1 to 6. Here's what i have so far:

DECLARE @count int;
SET @count = 0;
WHILE (@count < 52)
BEGIN
SELECT TOP 1 id, DateAdd(Day, 7, datevalue) AS dateval, monthid, personid from dates ORDER BY id desc
INSERT INTO dates (datevalue) values (dateval)
SET @count= @count + 1
END
GO
 

[dbrewerton]

I have decided the month number field is unnecessary. So now my situation is just adding the person id to the schedule. I know its going to be another loop but the big question I have is where should this loop sit?

[dbrewerton]

Ok, here's a strange thing I encountered in my quest. I try using an alias to update the value in the DB but it will not accept it for my insert clause. Here's my query:

DECLARE @count int;
SET @count = 0;
WHILE @count < 52
BEGIN
    @personid = 1
    SELECT TOP 1 id, DateAdd(Day, 7, weekend_val) AS wkendval from Weekends ORDER BY id desc
    INSERT INTO Weekends (weekend_val,employee_id) values (wkendval,personid)
    SET @count= @count + 1
END
GO
 

Each time I try my insert, I get this:

The name "wkendval" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

I thought you could use aliased names in insert clauses, am I wrong? What do I do to fix this?

[Rabbit]

Can you provide a subset of sample data and expected results?

I suspect you can just do something along the lines of

INSERT INTO SomeTable (DateIDField, EmployeeIDField)
SELECT DateIDField, EmployeeIDField
FROM DateTable, EmployeeTable
WHERE DATEPART('yyyy', DateField) = 2011

No need to create some complicated loop code.

[dbrewerton]

I could see that working if I didn't have to populate the Employee field automatically with numbers 1 to 6. That is why I need the loop structure. Its more than just populating the dates. What I am trying to do is populate a date every 7 days in the table and thereby assign an employee number on a rotating schedule.

[Rabbit]

If that's the case, you can use the week number mod 6 + 1 and that will give you the rotating employee ID without using a loop.

INSERT INTO SomeTable (DateIDField, EmployeeIDField)
SELECT DateIDField,
  ((DATEPART('ww', DateField) % 6) + 1) AS EmployeeID
FROM DateTable
WHERE DATEPART('yyyy', DateField) = 2011

[dbrewerton]

It's complianing about DATEPART saying its an invalid parameter 1 specified for datepart. Here is what I have now:

INSERT INTO weekends (weekend_val, coordinator_id) 
SELECT weekends, 
  ((DATEPART('ww', weekends) % 6) + 1) AS coordinator_id 
FROM weekends 
WHERE DATEPART('yyyy', weekend_val) = 2011 
 

and the error:

Msg 1023, Level 15, State 1, Line 3
Invalid parameter 1 specified for datepart.

[dbrewerton]

I figured it out. Since I need a custom order for two of the employees, I ended up using a loop structure. Now of course if there is a way to simplify this code I'm all ears. :)

DECLARE @count int;
DECLARE @wkendval smalldatetime;
DECLARE @personid int;
SET @count = 0;
set @personid = 0;
WHILE @count < 38
BEGIN
    set @personid = 1    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
    set @personid = 2    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
    set @personid = 3    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
    set @personid = 4    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
    set @personid = 5    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
    set @personid = 1    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
    set @personid = 2    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
    set @personid = 6    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
    set @personid = 4    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
    set @personid = 5    
    INSERT INTO weekends (weekend_val,employee_id)
    SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
    SET @count= @count + 1
 
END
GO
 

[Rabbit]

It should be

INSERT INTO weekends (weekend_val, coordinator_id)  
SELECT datevalue,  
  ((DATEPART('ww', datevalue) % 6) + 1) AS coordinator_id  
FROM dates
WHERE DATEPART('yyyy', datevalue) = 2011