Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


DateDiff between dynamic number of dates

Posted on 2004-10-25
Medium Priority
Last Modified: 2013-12-24
I'm working on a product predictor to determine the number of days between order dates.
It's possible for a product to have been ordered numerous times.

<cfquery name="qPP" datasource="#request.datasource#" username="#request.username#" password="#request.password#">
WHERE (Cus_No = '000000005001') AND (Item_No = '600-024')
ORDER BY request_dt ASC

<!--- Date is not in a date/time format, so make it happen --->
<cfoutput query="qPP">
<cfset "mydate#currentrow#" = CreateDate(mid(request_dt,1,4),mid(request_dt,5,2),mid(request_dt,7,2))>

<!--- Create Array to hold data --->
<cfset products = ArrayNew(1)>
<cfloop from="1" to="#qPP.RecordCount#" index="i">
<cfset products[i] = "#Evaluate("mydate#i#")#">

<!--- Output dates for testing --->
<cfloop index="i" from="1" to="#qPP.RecordCount#">
<cfoutput> #products[i]#</cfoutput>

<!--- Here is where it gets tricky --->
It's possible to have more then 2 dates, there could be 7 or more. Is it possible to make this dynamic, so I dont have a bunch of cfif statements? I will hardcode the mydate variables

<!--- 2 dates --->
<cfset daydiff1 = DateDiff("D", mydate1, mydate2)>

<!--- 3 dates --->
<cfset daydiff1 = DateDiff("D", mydate1, mydate2)>
<cfset daydiff2 = DateDiff("D", mydate2, mydate3)>

<!--- 4 dates an so on.... --->
<cfset daydiff1 = DateDiff("D", mydate1, mydate2)>
<cfset daydiff2 = DateDiff("D", mydate2, mydate3)>
<cfset daydiff3 = DateDiff("D", mydate3, mydate4)>
Question by:JRockFL
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Accepted Solution

kkhipple earned 500 total points
ID: 12401841
something like this should work

<CFLOOP index="i" FROM="1" TO="#size#">

   <CFSET nextDay = i + 1>
   <CFSET dayDiff#i# = DateDiff("D", mydate#1#, mydate#nextDay#)>


LVL 35

Expert Comment

ID: 12402130
I am confused on what exactly you want.

It sounds like you are factorially comparing dates.

The best method would probably be to have SQL server do the datediff using a cross join so you get all posibilities without haveing to code it manually.

Author Comment

ID: 12403608

That's what I needed. That pushed me in the right direction. I'm creating a "product predictor" to calculate average number of days between orders and then add that average to their last purchase date.

I have it working now.

<cfset tonumber = qPP.RecordCount - 1>
<cfset totaldays = 0>
<cfloop from="1" to="#tonumber#" index="i">
<cfset nextDay = i + 1>
<cfset daydiff[i] = DateDiff("D", Evaluate("mydate#i#"), Evaluate("mydate#nextday#"))>
<cfset totaldays = totaldays + daydiff[i]>
<cfoutput>#daydiff[i]# days difference</cfoutput><br>

Expert Comment

ID: 12403668
im glad i could help in some sort of way...

good luck Rock

Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question