[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

datediff between multiple dates

I have a table that contains various dates, for example:

ID | ValidAsOf
----------------
1   | 2005-01-01
1   | 2005-05-11
1   | 2005-09-27

I need to find the difference between each of these dates in weeks, figured starting with the earliest to the latest.  In the case of the above example, I would like to end up with two variables:

var1 = 18 (the eqivalent of #datediff("ww",createdate(2005,1,1),createdate(2005,5,11))#)
var2 = 19 (the eqivalent of #datediff("ww",createdate(2005,5,11),createdate(2005,9,27))#)

I've tried working through several possibilities in my head, but to no avail.  Anyone?
0
David Williamson
Asked:
David Williamson
  • 12
  • 5
1 Solution
 
trailblazzyr55Commented:


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
0
 
trailblazzyr55Commented:


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

<cfoutput>
#var1#
</cfoutput>
0
 
David WilliamsonIT DirectorAuthor Commented:
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?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
trailblazzyr55Commented:
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>
0
 
trailblazzyr55Commented:
yup ;o)
0
 
trailblazzyr55Commented:
query would look like this..

<cfquery name="qry1" datasource="testDB">
SELECT * FROM testtable
WHERE ID =1
</cfquery>
0
 
trailblazzyr55Commented:
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>
0
 
David WilliamsonIT DirectorAuthor Commented:
wow, this is the second thread I've seen you respond to with an unusual number of posts...
0
 
trailblazzyr55Commented:
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...

0
 
David WilliamsonIT DirectorAuthor Commented:
would it work with more than 3 db entries (I haven't had a chance to try it yet, you're so fast)?
0
 
David WilliamsonIT DirectorAuthor Commented:
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?
0
 
trailblazzyr55Commented:
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
0
 
trailblazzyr55Commented:
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
0
 
INSDivision6Commented:
<!--- 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>
0
 
trailblazzyr55Commented:
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
0
 
trailblazzyr55Commented:
Actually wasn't even paying attention but you can shorten mine more... here it is again shorter..

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

Thanks for the points, I glad I was able to help ;o)
~trail
0
 
David WilliamsonIT DirectorAuthor Commented:
I started another post based on parts of this post, would you mind having a look, trail?

http://www.experts-exchange.com/ColdFusion/Q_21591449.html
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now