473,395 Members | 1,639 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

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 1760
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

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

Similar topics

4
by: Gerry | last post by:
As I'm not a PHP-prgrammer at all, I just need Help with this: I have had a guestbook-page in Europe and will now have to move it to a US based-server. This makes the time-function showing time...
1
by: Rowlie | last post by:
Hi I am very new to access. I am starting a new job and have been asked to design a data base. After much messing around I have got something that is just o.k. The problem is that every time I...
2
by: Mark | last post by:
Hello, We currently have a table (access 2000) that has the following values: 706 6789 1 5.11 N N 20040923 792 6592 1 10.05 N N 20040923 795 1605 1 6.30 N N 20040923 807 5817 1 6.33 N N...
1
by: tomerslider | last post by:
i need some code that show me all the expired product that i have in every month example: the date today is 4/11/05 i need that it will show me all the proudct that there dates betwenn 1/11/05...
7
by: erekose666 | last post by:
I need a java prog to do the following: Create class Date with the following capabilities: a) Output the date in multiple formats, such as: MM/DD/YYYY June 14, 2005 DDD YYYY b) Use...
0
by: xadrian | last post by:
Hi, I'm trying to put together a query that will order entries in a table by the rating values which decay over time. I'm not too well versed in the intricacies of Postgres, or complex queries,...
19
by: eskelies | last post by:
I have two queries. My goal is to pull a range of numbers that fall on or in a certain date range. For example, $100 on 9/1/2007 and $200 on 9/24/2007. I want the date range to pick up both the...
1
crystal2005
by: crystal2005 | last post by:
Hi i encounterd some problem when i implement CSS for rounded Corner table. Need help please... :D <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />...
15
by: jayjayplane | last post by:
Currently I use Access form/subform to design a database interface, one field is date type field, but I need if the user inputs the future date like later than system currently date, I want it pop up...
5
by: Stepheno | last post by:
Hi, I am a recently converted Iseries (AS/400) RPG programmer trying to learn HTML/CSS/JavsScript all at the same time (not fun). My problem deals mostly with CSS. I will be reveiving a table,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.