datediff between multiple dates

var1 = 18 #datediff("w",createdate(2005,1,1),createdate(2005,5,11))#
var2 = 19 #datediff("w",createdate(2005,5,11),createdate(2005,9,27))#

one too many w's

try this <cfset var1 = datediff("w",createdate(2005,1,1),createdate(2005,5,11))>

<cfoutput>
#var1#
</cfoutput>

I don't think you're understanding what I'm asking.  

First of all, "ww" is correct for 'week'. "w" is for weekday(day of week).

Second of all, the example you gave does not answer my question in the least.  All you did was assign var1 to equal the datediff expression.  That doesn't get my from the query to the final result.  

If you had a table full of dates as I've described (could be 3 dates, could be 50 dates), how would you find the difference between between each of the dates and assign them to a series of variables in an automated fashion?  Do you understand what I'm asking?

try this for values from a query

<cfoutput query="qry1">
<cfif currentrow LT recordcount>
      <cfset secondValue = currentrow+1>
</cfif>
<cfset var[currentrow] = datediff("ww",ValidAsOf[currentrow],ValidAsOf[secondValue])>
  #var[currentrow]#
</cfoutput>

yup ;o)

query would look like this..

<cfquery name="qry1" datasource="testDB">
SELECT * FROM testtable
WHERE ID =1
</cfquery>

ok final answer ;o)

I'm guessing you didn't want the final '0' on there

<cfquery name="qry1" datasource="testDB">
SELECT * FROM testtable
WHERE ID =1
</cfquery>

<cfoutput query="qry1">
<cfif currentrow LT recordcount>
      <cfset secondValue = currentrow+1>
</cfif>
<cfset var[currentrow] = datediff("ww",ValidAsOf[currentrow],ValidAsOf[secondValue])>
<cfif currentrow LT recordcount>
  #var[currentrow]#
</cfif>
</cfoutput>

wow, this is the second thread I've seen you respond to with an unusual number of posts...

sorry I misunderstood you original question,
don't know what I was thinking,
but my last post returns the 18, 19 from the values you have listed,
I created a test db to make sure, so that should do it, it didn't return any issues for me...

would it work with more than 3 db entries (I haven't had a chance to try it yet, you're so fast)?

I like what I see so far, thank you.  

There's some syntax in there I am not familiar with, the [] parts in particular.  I didn't know you could use it like <cfset var[currentrow] = ... or ValidAsOf[currentrow]...  Can you only use it with query rows, or can it be used in other situations, like cfloops?

you can use them within loops as well or in cfoutput tags too...

you use [] when you have a count within a dynamic variable like #varr[somecount]#

noamlly used when looping or outputing queries...

for query values if you're looking for a specific row number you can use #query.variable[currentrow]#

but when using cfset you can assign the naming value the[] for a dynamic variable count such as varr1,varr2...

Like this.. <cfset varr[i] = some value>
But you can't do this.. <cfset varr#i# = some value>

and the likewise

you can do this <cfset varr = somevalue#i#>
but you cant <cfset varr = somevalue[i]> unless somevalues is dynamic in which you'd do <cfset varr = #somevalue[i]#>

there's lots of what you can use it for and what you can't, just takes some playing with ;o)

by the way I'm just adding some values to the test DB to test the script further I'm 99.9% sure though it'll work for any number of values :o)

also just I set all the values like you had in your original question with and ID = 1 when you query this determines what ID to do the datediff one, I'm sure you knew that though..

hope that helps
~trail

yup it works for as many records as you want to put in there ;o)

hope that help, I tested it going up to the end of 2008 counting like you were doing 18,19,20,21.... work good!

>>wow, this is the second thread I've seen you respond to with an unusual number of posts...

I'm bored ;o)

~trail

<!--- ID --->
<cfset id=1>

<cfquery name="myqry">
   select ValidAsOf from sometable where id=#id# order by ValidAsOf
</cfquery>

<!--- Differences here --->
<cfset diff=ArrayNew(1)>
<!--- Dates here --->
<cfset dates=ArrayNew(1)>

<!--- Check for no records, if needed --->
. . . .

<!--- First value --->
<cfset dates[1]=myqry.ValidAsOf>
<cfset diff[1]=0>

<!--- Others --->
<cfloop query="myqry">
   <cfif currentRow GT 1>
      <cfset dates[currentRow]=ValidAsOf>
      <cfset diff[currentRow]=DateDiff("ww", dates[currentRow-1], dates[currentRow])>
   </cfif>
</cfloop>

INSDivision6,
it's another way of doing it I guess... although I tried your way and get different results from a test DB???

0 0 18 0 19 0... , the way I did it showed 18 0 19...

account for nulls

0 18 19, my way 18 19

yes you left a spot to handle nulls, reason for both examples i tested yours accounting for that as well
Also why create arrays? just define the variables inside the cfloop, easier I think??? And less scripting...
And while cfloop query may be a millisecond faster, using cfoutput query eliminates the need for additional tags

theamzngq,

To account for nulls or blank values just edit your query like this... no extra scripting needed...

<cfset ID = 1>
<cfquery name="qry1" datasource="testDB">
SELECT ValidAsOf FROM testtable WHERE ID = #ID# AND ValidAsOf <> 0                    <!--- 0, "", or 'NULL' depending on what blank values are if any --->
ORDER BY ValidAsOf
</cfquery>

then...

<cfoutput query="qry1">
     <cfif currentrow LT recordcount><cfset secondValue = currentrow+1></cfif>
          <cfset var[currentrow] = datediff("ww",ValidAsOf[currentrow],ValidAsOf[secondValue])>
     <cfif currentrow LT recordcount>#var[currentrow]#</cfif>
</cfoutput>


that's all there is too it and it handles as many records as you can feed in there as well as accounts for null values ;o)

~trail

theamzngq,

Thanks for the points, I glad I was able to help ;o)
~trail

I started another post based on parts of this post, would you mind having a look, trail?

https://www.experts-exchange.com/questions/21591449/which-iteration-of-cfloop-am-I-on.html