DateDiff between dynamic number of dates

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#">
SELECT *
FROM   OELINHST_SQL
WHERE (Cus_No = '000000005001') AND (Item_No = '600-024')
ORDER BY request_dt ASC
</cfquery>

<!--- 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))>
</cfoutput>

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

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

<!--- 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)>
LVL 8
JRockFLAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kkhippleConnect With a Mentor Commented:
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#)>

</CFLOOP>


0
 
mrichmonCommented:
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.
0
 
JRockFLAuthor Commented:
kkhipple,

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>
</cfloop>
0
 
kkhippleCommented:
im glad i could help in some sort of way...


good luck Rock
0
All Courses

From novice to tech pro — start learning today.