DateDiff between dynamic number of dates

Posted on 2004-10-25
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
    LVL 5

    Accepted Solution

    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

    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.
    LVL 8

    Author Comment


    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>
    LVL 5

    Expert Comment

    im glad i could help in some sort of way...

    good luck Rock

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
    Article by: kevp75
    Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    884 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now