Insert database query looping through claendar dates 1

[rockyroad]

Hi,

I have a basic CF calendar application which uses an Access DB backend. What I need to do is write a CFQUERY Insert query which will loop through all the actual calendar dates starting April 1 of this year and ending Dec 31st. Is there a server variable or some other variable that is aware of this that I can loop through, so psuedocode like:

<cfloop from "foo" to "foo" where the server date GT March 31st 2006 AND LT January 1st 2007>
<cfquery datasource="" name="insert">
<!--- Insert stuff here, including current row in loop with variable for appropriate date --->
</cfquery>
</cfloop>

Any help would be much appreciated!

Thanks! RR

 

[r937]

looping is one way to do it, but that calls the database hunderds of times

another way to generate the dates from an integers table

in access, define a table called integers with a single column, called i, and make it the primary key

insert 10 values into i, from 0 through 9

then run this query --

select 100*h.i + 10*t.i + u.i as nnn
  from integers as h
     , integers as t
     , integers as u
 where 100*h.i + 10*t.i + u.i between 0 and 274
order 
    by 100*h.i + 10*t.i + u.i

with me so far?

okay, now change the query slightly --

select dateadd("d", 100*h.i + 10*t.i + u.i
         , #2006-04-01# ) as ddate
  from integers as h
     , integers as t
     , integers as u
 where 100*h.i + 10*t.i + u.i between 0 and 274
order 
    by 100*h.i + 10*t.i + u.i

see what this does? with this SELECT statement, we generate the desired dates "on the fly"

now all you gotta do write a single INSERT statement and feed it the results of this SELECT, and bob's your uncle!!

INSERT INTO daTable ( daDate ) SELECT ...

r937.com | rudy.ca

 

[rockyroad]

Well, r937, I wouldn't be needing to hit the database multiple times really, because this is to be a one-time data entry of records from one starting date to an end date... but your idea is intriguing and I am going to give it a whirl Thanks!

Still, I'd like to understand how to say start with a CF variable derived from #Now()#, and establish a future ending date, and loop through in CF. Any thoughts?

 

[r937]

i guess you could use a CFLOOP and use coldfusion's DateAdd function

but then you'd have an INSERT statement inside the loop, so you'd be executing 275 calls to the database, right?

i like my way better, it's only 1 call to the database



r937.com | rudy.ca

 

[rockyroad]

Thank you, your idea worked wonderfully.

However, I am a little unclear about the 'h' and 't' variables in your script - i.e., 100*h.i...

Can you eluciate a bit on your script? I was able to utilize it in my script, but I'd like to gain the larger understanding of your script. THANKS!!!!

 

[r937]

t, h, and u are table aliases

the FROM clause specifies the integers table more than once, thus each reference to one of its columns must be qualified using a table alias

i chose the alias names t, h, and u to represent thousands, hundreds, and units (i've also called them curly, larry, and moe, but that might be too confusing, eh)

each qualified column reference is to the column i, which is an integer from 0 through 9

so think of it as all possible combinations of three digits, and use them to generate a number which will be between 0 and 999

r937.com | rudy.ca

 

[rockyroad]

Thanks, hey here's a few more questions that will help my understanding... - what is the significance of 274 in "between 0 and 274" in the formula. Also, how would you alter the formula to only create a table of records for, say, every Wednesday, rather than every day?

Thanks!!!!

 

[r937]

i am an idiot

did i say thousands, hundreds, and units?

i meant to say hundreds, tens and units

i used 274 because there are 275 days (add 0 through 274) from apr 1 to dec 31

to do every wednesday, you have to start with april 5, and then add 7 for 38 weeks --

select dateadd("d", 7*( 10*t.i + u.i )
         , #2006-04-05# ) as ddate
  from integers as t
     , integers as u
 where 10*t.i + u.i between 0 and 38
order 
    by 10*t.i + u.i

r937.com | rudy.ca

 

[rockyroad]

Thanks! You're no idiot. I have been thick as a brick trying to wrap my head around your logic I think I get it now. THANKS AGAIN!