469,903 Members | 1,520 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

How to propagate date table automatically?

dbrewerton
115 100+
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
Expand|Select|Wrap|Line Numbers
  1. // Grab last Datevalue ID and then add 7 days to that date
  2.  
  3. SELECT MAX 1 id, Datevalue AS newdateval from Table ORDER BY id desc
  4.  
  5. newdateval = DateAdd(day,7,newdateval)
  6.  
  7. // Insert the new date
  8.  
  9. 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?
Jan 28 '11 #1
10 1598
ck9663
2,878 Expert 2GB
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
Jan 28 '11 #2
dbrewerton
115 100+
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:

Expand|Select|Wrap|Line Numbers
  1. DECLARE @count int;
  2. SET @count = 0;
  3. WHILE (@count < 52)
  4. BEGIN
  5. SELECT TOP 1 id, DateAdd(Day, 7, datevalue) AS dateval, monthid, personid from dates ORDER BY id desc
  6. INSERT INTO dates (datevalue) values (dateval)
  7. SET @count= @count + 1
  8. END
  9. GO
  10.  
Jan 28 '11 #3
dbrewerton
115 100+
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?
Jan 28 '11 #4
dbrewerton
115 100+
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:

Expand|Select|Wrap|Line Numbers
  1. DECLARE @count int;
  2. SET @count = 0;
  3. WHILE @count < 52
  4. BEGIN
  5.     @personid = 1
  6.     SELECT TOP 1 id, DateAdd(Day, 7, weekend_val) AS wkendval from Weekends ORDER BY id desc
  7.     INSERT INTO Weekends (weekend_val,employee_id) values (wkendval,personid)
  8.     SET @count= @count + 1
  9. END
  10. GO
  11.  
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?
Jan 28 '11 #5
Rabbit
12,516 Expert Mod 8TB
Can you provide a subset of sample data and expected results?

I suspect you can just do something along the lines of
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO SomeTable (DateIDField, EmployeeIDField)
  2. SELECT DateIDField, EmployeeIDField
  3. FROM DateTable, EmployeeTable
  4. WHERE DATEPART('yyyy', DateField) = 2011
No need to create some complicated loop code.
Jan 28 '11 #6
dbrewerton
115 100+
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.
Jan 29 '11 #7
Rabbit
12,516 Expert Mod 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO SomeTable (DateIDField, EmployeeIDField)
  2. SELECT DateIDField,
  3.   ((DATEPART('ww', DateField) % 6) + 1) AS EmployeeID
  4. FROM DateTable
  5. WHERE DATEPART('yyyy', DateField) = 2011
Jan 29 '11 #8
dbrewerton
115 100+
It's complianing about DATEPART saying its an invalid parameter 1 specified for datepart. Here is what I have now:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO weekends (weekend_val, coordinator_id) 
  2. SELECT weekends, 
  3.   ((DATEPART('ww', weekends) % 6) + 1) AS coordinator_id 
  4. FROM weekends 
  5. WHERE DATEPART('yyyy', weekend_val) = 2011 
  6.  
and the error:
Expand|Select|Wrap|Line Numbers
  1. Msg 1023, Level 15, State 1, Line 3
  2. Invalid parameter 1 specified for datepart.
Jan 31 '11 #9
dbrewerton
115 100+
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. :)

Expand|Select|Wrap|Line Numbers
  1. DECLARE @count int;
  2. DECLARE @wkendval smalldatetime;
  3. DECLARE @personid int;
  4. SET @count = 0;
  5. set @personid = 0;
  6. WHILE @count < 38
  7. BEGIN
  8.     set @personid = 1    
  9.     INSERT INTO weekends (weekend_val,employee_id)
  10.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  11.     SET @count= @count + 1
  12.  
  13.     set @personid = 2    
  14.     INSERT INTO weekends (weekend_val,employee_id)
  15.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  16.     SET @count= @count + 1
  17.  
  18.     set @personid = 3    
  19.     INSERT INTO weekends (weekend_val,employee_id)
  20.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  21.     SET @count= @count + 1
  22.  
  23.     set @personid = 4    
  24.     INSERT INTO weekends (weekend_val,employee_id)
  25.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  26.     SET @count= @count + 1
  27.  
  28.     set @personid = 5    
  29.     INSERT INTO weekends (weekend_val,employee_id)
  30.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  31.     SET @count= @count + 1
  32.  
  33.     set @personid = 1    
  34.     INSERT INTO weekends (weekend_val,employee_id)
  35.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  36.     SET @count= @count + 1
  37.  
  38.     set @personid = 2    
  39.     INSERT INTO weekends (weekend_val,employee_id)
  40.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  41.     SET @count= @count + 1
  42.  
  43.     set @personid = 6    
  44.     INSERT INTO weekends (weekend_val,employee_id)
  45.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  46.     SET @count= @count + 1
  47.  
  48.     set @personid = 4    
  49.     INSERT INTO weekends (weekend_val,employee_id)
  50.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  51.     SET @count= @count + 1
  52.  
  53.     set @personid = 5    
  54.     INSERT INTO weekends (weekend_val,employee_id)
  55.     SELECT TOP 1 DateAdd(Day, 7, weekend_val), @personid from weekends ORDER BY id desc
  56.     SET @count= @count + 1
  57.  
  58. END
  59. GO
  60.  
Jan 31 '11 #10
Rabbit
12,516 Expert Mod 8TB
It should be
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO weekends (weekend_val, coordinator_id)  
  2. SELECT datevalue,  
  3.   ((DATEPART('ww', datevalue) % 6) + 1) AS coordinator_id  
  4. FROM dates
  5. WHERE DATEPART('yyyy', datevalue) = 2011
Jan 31 '11 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Gerry | last post: by
1 post views Thread by Rowlie | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.