Solved

# datediff between multiple dates

Posted on 2005-05-11
238 Views
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
Question by:theamzngq

LVL 20

Expert Comment

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

LVL 20

Expert Comment

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

<cfoutput>
#var1#
</cfoutput>
0

LVL 2

Author Comment

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

LVL 20

Expert Comment

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

LVL 20

Expert Comment

yup ;o)
0

LVL 20

Expert Comment

query would look like this..

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

LVL 20

Expert Comment

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

LVL 2

Author Comment

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

LVL 20

Expert Comment

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

LVL 2

Author Comment

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

LVL 2

Author Comment

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

LVL 20

Expert Comment

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

LVL 20

Expert Comment

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

LVL 7

Expert Comment

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

LVL 20

Expert Comment

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

LVL 20

Accepted Solution

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

LVL 20

Expert Comment

theamzngq,

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

LVL 2

Author Comment

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

### Suggested Solutions

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…